sing1006 -5 Light Poster

I have 3 table which are
zz.PNG
I want to duplicate a record from the master table in the same time it will duplicate all children table. I will assign new primary key to the new duplicate record. For the tbl_System and tbl_MainComp work fine. But when i have 2 record in tbl_MainComp and each of them contain 1 record in tbl_SubComp, it just copy all the record in tbl_SubComp to each tbl_MainComp record.

    cmd.CommandText = "INSERT INTO tbl_System (EquipTag, S_Name, S_Function, S_Failure) " &
                " SELECT '" & TextBox1.Text & "', S_Name, S_Function, S_Failure FROM tbl_System WHERE EquipTag ='" & Tbl_SystemDataGridView.CurrentRow.Cells(0).Value & "'"
                cmd.ExecuteNonQuery()
                cmd.CommandText = "INSERT INTO tbl_MainComp (EquipTag, MC_Name, MC_Function) " &
                " SELECT '" & TextBox1.Text & "', MC_Name, MC_Function FROM tbl_MainComp WHERE EquipTag ='" & Tbl_SystemDataGridView.CurrentRow.Cells(0).Value & "'"
                cmd.ExecuteNonQuery()


                For i As Integer = 0 To Tbl_MainCompDataGridView.RowCount - 1
                    Dim newcmd As New OleDb.OleDbCommand("SELECT @@IDENTITY", cnn)
                    Dim uid As Integer
                    uid = newcmd.ExecuteScalar

                    MessageBox.Show(uid)

                    Dim a As Integer
                    cmd.CommandText = "INSERT INTO tbl_SubComp (Equiptag, MC_No, SC_Name) " &
                        " SELECT '" & TextBox1.Text & "','" & uid & "', SC_Name FROM tbl_SubComp WHERE EquipTag ='" & Tbl_SystemDataGridView.CurrentRow.Cells(0).Value & "' "
                    cmd.ExecuteNonQuery()


                Next
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.