我如何将数据插入VB.net中的2个不同表中，我正在使用MS Access作为数据库 [英] How can I insert data into 2 different table in VB.net, I'm using MS Access as my db

问题描述

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

I'm having a trouble here using VS2010. I'm using a MS Access database.

我有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表被完全填充.

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 tblCustomers table are completely filled in.

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

Can anyone suggest something so that I can also fill in the tblBooking table.

我也曾尝试加入他们，但这是行不通的.预先感谢您的答复.

I also did try joining them, but it doesn't work. Thanks in advance for the reply.

这是我编写的代码.问题是，它是在tblCustomers表中两次插入信息.

This is the code that I've made. The problem is, it is double inserting the information in tblCustomers table.

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

推荐答案

您query2没有指定值:

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

与在query1中所做的一样.您正在编写where子句，但未编写值.

as you do in query1. You are writing the where clause but you are not writing values.

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 & "'"

这篇关于我如何将数据插入VB.net中的2个不同表中，我正在使用MS Access作为数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！