sql - How can I insert data into 2 different table in VB.net, I'm using MS Access as my db

Question

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

I have 3 tables: tblCustomers, tblBooking, tblRoom

tblCustomers has these columns:

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

tblBooking has these columns:

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

tblRoom has these columns:

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

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.

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

Answer 1

You query2 is not specyfing values:

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.

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