如何在 2 个不同的表中插入数据 VB.net，我正在使用 MS Access 作为我的数据库

Question

我在使用 VS2010 时遇到了麻烦。我正在使用 MS Access 数据库。

我有3张桌子：tblCustomers，tblBooking，tblRoom

tblCustomers具有以下列：

(1) Customer_ID
(2) Last_Name
(3) First_Name
(4) Age
(5) Address
(6) Contact

tblBooking具有以下列：

(1) Book_No
(2) Customer_ID
(3) Day_In
(4) Day_Out
(5) Room_ID

tblRoom具有以下列：

(1) Room_ID
(2) Room_Type
(3) Price
(4) Capacity
(5) Remarks

现在，每当我输入lname，fname，年龄，地址，联系人时，选择房间类型，容量，输入首选day_in和day_out，然后单击立即预订！它说它已经成功完成了。但是当我查看我的数据库时，只有tblCustomers表被完全填满。

任何人都可以提出一些建议，以便我也可以填写tblBooking表。

我也尝试加入他们，但它不起作用。提前感谢您的回复。

这是我编写的代码。问题是，它是在tblCustomers表中双重插入信息。

Private Sub bookBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bookBtn.Click
    Try
        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = HotelRsvp.accdb"
        query = "INSERT INTO tblCustomers (Last_Name, First_Name, Age, Address, Contact) VALUES ('" & lnametxtbx.Text.ToLower() & "', '" & fnametxtbx.Text.ToLower() & "', '" & agetxtbx.Text & "', '" & addtxtbx.Text.ToLower() & "', '" & contacttxtbx.Text & "')"
        query2 = "INSERT INTO tblBooking WHERE Customer_ID = tblCustomer.Customer_ID, Day_In = '" & dayIn.Value & "', Day_Out = '" & dayOut.Value & "', Room_ID = '" & rmIDlbl.Text & "'"

        dbUp = New OleDbCommand(query, con)
        dbUp2 = New OleDbCommand(query2, con)
        con.Open()
        dbUp.ExecuteNonQuery()
        dbUp2.ExecuteNonQuery()
        MessageBox.Show("Successfully Booked.")
        lnametxtbx.Text = ""
        fnametxtbx.Text = ""
        agetxtbx.Text = ""
        addtxtbx.Text = ""
        contacttxtbx.Text = ""
        RmtypeCbx.ResetText()
        cpctyCbx.ResetText()
        rmIDlbl.Text = ""
        Pricelbl.Text = ""
        con.Close()
    Catch ex As Exception
        If Not IsNumeric(contacttxtbx.Text) Or Not IsNumeric(agetxtbx.Text) Then
            MessageBox.Show("Invalid Age, Contact Number or there's a blank.")
        Else
            'con.Open()
            dbUp = New OleDbCommand(query, con)
            dbUp.ExecuteNonQuery()
            MessageBox.Show("Successfully Booked.")
            con.Close()
        End If
    End Try
    con.Close()
End Sub

Answer 1

你query2不是指定值：

query2 = "INSERT INTO tblBooking WHERE Customer_ID = tblCustomer.Customer_ID, Day_In = '" & dayIn.Value & "', Day_Out = '" & dayOut.Value & "', Room_ID = '" & rmIDlbl.Text & "'"

就像您在查询 1 中所做的那样。您正在编写 where 子句，但不是在编写值。

query = "INSERT INTO tblCustomers (Last_Name, First_Name, Age, Address, Contact) VALUES ('" & lnametxtbx.Text.ToLower() & "', '" & fnametxtbx.Text.ToLower() & "', '" & agetxtbx.Text & "', '" & addtxtbx.Text.ToLower() & "', '" & contacttxtbx.Text & "')"

它应该是：

query2 =
"INSERT INTO tblBooking (COL1,COL2,COL3) VALUES (VAL1,VAL2,VAL3) WHERE Customer_ID = tblCustomer.Customer_ID, Day_In = '" & dayIn.Value & "', Day_Out = '" & dayOut.Value & "', Room_ID = '" & rmIDlbl.Text & "'"