Question How to append records in master detail tables.

Mazhar

Member
Joined
Aug 27, 2016
Messages
15
Programming Experience
Beginner
I create a program in vb.net 2010 with sql Server 2014, There are two tables InvoiceMaster and InvoiceDetail

A invoice contain 100 item. I inserted 60 items in the tables through follwoing code:

VB.NET:
Private Sub BtnSave_Click(sender As System.Object, e As System.EventArgs) Handles BtnSave.Click

        If PurGridValidity() Then

            Dim MyTxtBillNo As String
            MyTxtBillNo = TxtVendorID.Text + "/" + TxtBillNo.Text
            PurchaseGrid.AllowUserToAddRows = False

            GetConnect()
            GetNewPurchaseinvoiceNo()
            GetNewVoucherNo()

            Conn.Open()
            Using Mytransaction = Conn.BeginTransaction()

                Try

Using cmd As New SqlCommand("INSERT INTO PurchaseMaster (PurchaseInvoiceNo,VendorBillNo,VendorBillDate,VendorID,PurAmount,Remarks) 
Values (@PurchaseInvoiceNo,@VendorBillNo,@VendorBillDate,@VendorID, @Puramount,@Remarks)", Conn, Mytransaction)
                        cmd.Parameters.AddWithValue("@PurchaseInvoiceNo",LblPurchaseInvoice.Text)
                        cmd.Parameters.AddWithValue("@VendorBillNo", MyTxtBillNo)
                        cmd.Parameters.AddWithValue("@VendorBillDate", CDate(TxtBillDate.Text))
                        cmd.Parameters.AddWithValue("@VendorID", TxtVendorID.Text)
                        cmd.Parameters.AddWithValue("@PurAmount", TxtNetTotal.Text)
                        cmd.Parameters.AddWithValue("@Remarks", TxtRemarks.Text)

                        cmd.ExecuteNonQuery()

                    End Using

                    For Each rw As DataGridViewRow In PurchaseGrid.Rows
                        If rw.Cells(0).Value <> "" And rw.Cells(2).Value <> "" Then
                            Using cmd = New SqlCommand("INSERT INTO PurchaseDetail (PurchaseInvoiceNo,VendorBillNo,ProductID,PurDescription,PurQty,PurRate) values ('" & LblPurchaseInvoice.Text & "','" & MyTxtBillNo & "','" & rw.Cells(0).Value & "','" & rw.Cells(1).Value & "','" & rw.Cells(2).Value & "','" & rw.Cells(3).Value & "')", Conn, Mytransaction)
                                cmd.ExecuteNonQuery()
                            End Using

                        End If
                    Next

                    Mytransaction.Commit()

                    MessageBox.Show("New Record has Added Successfuly", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Catch ex As Exception
                    Mytransaction.Rollback()
                    MessageBox.Show("Error :" & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End Try
                Conn.Close()
            End Using
        End If
    End Sub

Now problem is that after Inserting the 60 items, how can I append the remaining 40 items ?
Where i should change my code or what should be the code for?
 
Last edited:
Back
Top