How do I insert certain values, including the customer ID pulled from another table, into a seperate table

Question

Okay, so i'm working on my final project for VB.net II. I'm created a project for a hotel stay situation and I have everything working perfectly. I need help however on adding something to it.

I have it to where either your a new guest/customer or a returning. If your new, when you submit the form, it adds the customers information to the database. My Access database has 2 tables: Customer and CustomerStays.

Now, the primary key in both of them is a field called: CustomerID and they have a relationship tied together in the database itself.

Fields of the Customer table include: CustomerID, CustomerName, CustomerAddress, CustomerZipcode, and CustomerTagNumber

Fields of the CustomerStays table include: CustomerID, CustomerDateIn, CustomerDateOut, and TotalDays

Here is an image of my main form:

When they click total for stay, here is the code that goes through with it

 Private Sub TotalForStayBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TotalForStayBtn.Click

    ErrorChecking() 'Sub routine that checks for errors

    If YourGood = True Then 'Only good if you pass the error check

        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        ''''''''''''''''ADDING CUSTOMER INFORMATION TO THE DATABASE''''''''''''''''
        '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

        If ExistingCustomer = False Then

            Try

                myconnection.Open()

                Dim str As String

                str = "SELECT * FROM Customer WHERE CustomerTagNumber='" & CustomerTagNumbertxt.Text & "'"

                Dim cmd As OleDbCommand = New OleDbCommand(str, myconnection)

                dr = cmd.ExecuteReader

                If dr.Read Then

                    If MsgBox("That license plate tag number already exists. Are you an existing customer?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then

                        ReturningCustomerLookup.Show()
                        Me.Close()

                    ElseIf MsgBoxResult.No Then

                        MessageBox.Show("Please re-enter your own license plate tag number and verify that it is entered correctly.")

                    End If

                Else

                    str = "INSERT INTO Customer (CustomerName, CustomerAddress, CustomerZip, CustomerTagNumber)" _
                        & " VALUES ('" & CustomerNametxt.Text & "','" _
                        & CustomerAddresstxt.Text & "','" _
                        & CustomerZipCodetxt.Text & "','" _
                        & CustomerTagNumbertxt.Text & "')"

                    cmd = New OleDbCommand(str, myconnection)
                    cmd.ExecuteNonQuery()
                    MessageBox.Show("Your information has been successfully added to the database", "Successful")

                    '''''''CALCULATING THE TOTAL'''''''
                    CalculateTotal()
                    '''''END CALCULATING THE TOTAL'''''

                    '''''''ADDING DATES INFO TO DATABASE'''''''
                    **CustomerStaysInsertion()**
                    '''''END ADDING DATES INFO TO DATABASE'''''

                End If

            Catch ex As Exception
                MessageBox.Show("There was an error inserting your information into the database" _
                                & vbCrLf & vbCrLf & "Original Error: " & ex.ToString, _
                                "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Finally
                myconnection.Close()
            End Try

            '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            ''''''''''''''END ADDING CUSTOMER INFORMATION TO THE DATABASE''''''''''''''
            '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

        Else

            '''''''CALCULATING THE TOTAL'''''''
            CalculateTotal()
            '''''END CALCULATING THE TOTAL'''''

        End If

    End If

End Sub

Now my problem is the CustomerStays information. It runs the subroutine and what I want it to do is use the information of the customer to grab the CustomerID in the Customer table and place that AS CustomerID in the CustomerStays table along with the date that they check in and check out and the total number of days staying.

Basically, I need it to insert all of that information with the CustomerID that belongs to the customer that is currently staying at the hotel.

Additional info:

Names for some controls:

Date in - DateInPicker

Date out - DateOutPicker

Total Days - TotalDayslbl

Answer 1

First, if it is not already set, you need to set the CustomerID on the Customer table to be an Autonumber, so its value is generated automatically for you.

Then, assuming that you don't have concurrent insert in your Customer table, you can grab the last inserted Autonumber executing the following OleDbCommand, just after the ExecuteNonQuery that inserts a new customer,

    cmd.ExecuteNonQuery()
    cmd.CommandText = "SELECT @@IDENTITY"
    Dim result = Convert.ToInt32(cmd.ExecuteScalar())

then the variable result should have the last inserted value in the column CustomerID of the Customer table.

Said that, please take advice to change the way you issue your sql commands to the database. String concatenation is really a bad practice and should be avoided at all costs.
Just for example, what happen to your code if a Customer is called John O'Hara? Notice the single quote? When you concatenate the string in the textbox your query text will result in an incorrect sql statement. But this is just a syntax error, worst is the Sql Injection scenario described here

This is an example how to write the insert query (but it applies to every query where you need to use text typed by your malicious users)

str = "INSERT INTO Customer (CustomerName, CustomerAddress, CustomerZip, CustomerTagNumber)" _
                    & " VALUES (?,?,?,?)"
cmd = New OleDbCommand(str, myconnection)
cmd.Parameters.AddWithValue("@p1", CustomerNametxt.Text)
cmd.Parameters.AddWithValue("@p2", CustomerAddresstxt.Text)
cmd.Parameters.AddWithValue("@p3", CustomerZipCodetxt.Text)
cmd.Parameters.AddWithValue("@p4", CustomerTagNumbertxt.Text)
cmd.ExecuteNonQuery()

In this way the problem of single quotes is resolved by the framework code and this avoid also the Sql Injection mess.

EDIT
For the CustomerStays fields (datetime) use instead this

       Dim str As String
        str = "INSERT INTO CustomerStays (CustomerID, CustomerDateIn, CustomerDateOut, TotalDays)" _
                        & " VALUES (?,?,?,?)"

        Dim cmd As OleDbCommand = New OleDbCommand(str, myconnection)
        cmd.Parameters.AddWithValue("@p1", result)
        cmd.Parameters.AddWithValue("@p2", Convert.ToDateTime(DateInPicker.Value)
        cmd.Parameters.AddWithValue("@p3", Convert.ToDateTime(DateOutPicker.Value)
        cmd.Parameters.AddWithValue("@p4", TotalDayslbl.Text)
        cmd.ExecuteNonQuery()

And if TotalDays is numeric Convert.ToInt32(TotalDayslbl.Text).....