如何将数据插入到 VB.net 中的 2 个不同表中，我使用 MS Access 作为我的数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19424769/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I insert data into 2 different table in VB.net, I'm using MS Access as my db
提问by Elixes Villareal Felicierta Be
I'm having a trouble here using VS2010. I'm using a MS Access database.
我在使用 VS2010 时遇到了麻烦。我正在使用 MS Access 数据库。
I have 3 tables: tblCustomers, tblBooking, tblRoom
我有 3 个表：tblCustomers, tblBooking,tblRoom
tblCustomershas these columns:
tblCustomers有这些列：
(1) Customer_ID
(2) Last_Name
(3) First_Name
(4) Age
(5) Address
(6) Contact
tblBookinghas these columns:
tblBooking有这些列：
(1) Book_No
(2) Customer_ID
(3) Day_In
(4) Day_Out
(5) Room_ID
tblRoomhas these columns:
tblRoom有这些列：
(1) Room_ID
(2) Room_Type
(3) Price
(4) Capacity
(5) Remarks
Now whenever I entered lname, fname, age, address, contact, choose a room type, capacity, put the preferred day_in and day_out and then click Book Now! It says it is successfully done. But when I look at my database, only the tblCustomerstable are completely filled in.
现在，每当我输入 lname、fname、年龄、地址、联系人时，选择房间类型、容量，输入首选的 day_in 和 day_out，然后单击立即预订！它说它已成功完成。但是当我查看我的数据库时，只有tblCustomers表格被完全填满。
Can anyone suggest something so that I can also fill in the tblBookingtable.
任何人都可以提出一些建议，以便我也可以填写tblBooking表格。
I also did try joining them, but it doesn't work. Thanks in advance for the reply.
我也确实尝试加入他们，但它不起作用。预先感谢您的回复。
This is the code that I've made. The problem is, it is double inserting the information in tblCustomers table.
这是我制作的代码。问题是，它在 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
回答by Carlos Landeras
You query2is not specyfing values:
您query2没有指定值：
query2 = "INSERT INTO tblBooking WHERE Customer_ID = tblCustomer.Customer_ID, Day_In = '" & dayIn.Value & "', Day_Out = '" & dayOut.Value & "', Room_ID = '" & rmIDlbl.Text & "'"
as you do in query1. You are writing the where clause but you are not writing values.
正如您在 query1 中所做的那样。您正在编写 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 & "')"
It should be:
它应该是：
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 & "'"
