Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim Query As String

        Query = "INSERT INTO visitor(vid_pass,vname,address,contact_num,status,purpose)VALUES(" + vid_pass.Text + ",'" + vname.Text + "','" + address.Text + "'," + contact.Text + ",'" + TextBox5.Text + "','" + purpose.Text + "');"

        Query = "INSERT INTO building_transaction(bname)VALUES('" + building.SelectedValue + "');"

        Dim cmd As MySqlCommand = New MySqlCommand(Query, con)


        Dim i As Integer = cmd.ExecuteNonQuery() '
        If (i > 0) Then
            MessageBox.Show("Record is Successfully Inserted")
            vid_pass.Clear()
            vname.Clear()
            address.Clear()
            contact.Clear()
            purpose.Clear()
        Else
            MessageBox.Show("Record is not Inserted")

        End If

    End Sub

I have 2 queries, the 1st one will insert to the "visitors" table and the 2nd query will insert to "building_transaction" table and its fully working. Now how can I get a copy "ID" of the visitor which is auto generated and insert it to the "building_transaction" table? Note: the vid_pass is different to the ID.

use scope identification in MSSql server. Google it

Can you try this one:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click


            Dim Query1 As String
            Dim Query2 As String

            Query1 = "INSERT INTO visitor(vid_pass,vname,address,contact_num,status,purpose)VALUES(" + vid_pass.Text + ",'" + vname.Text + "','" + Address.Text + "'," + CONTACT.Text + ",'" + TextBox5.Text + "','" + purpose.Text + "');"
            Dim cmd1 As MySqlCommand = New MySqlCommand(Query1, con)

            Query2 = "INSERT INTO building_transaction(bname, ID )VALUES('" + building.SelectedValue + "', SELECT ID From Visitor WHERE vid_pass = " + vid_pass.Text + ");"

            Dim i As Integer = cmd1.ExecuteNonQuery()
            If (i > 0) Then
                Dim cmd2 As MySqlCommand = New MySqlCommand(Query1, con)

                Dim i As Integer = cmd2.ExecuteNonQuery()
                If (j > 0) Then
                    MessageBox.Show("Record is Successfully Inserted")
                    vid_pass.Clear()
                    vname.Clear()
                    Address.Clear()
                    CONTACT.Clear()
                    purpose.Clear()
                Else
                    MessageBox.Show("Record is not Inserted")
                End If
            End If

  End Sub
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.