how to transfer data to two different tables in one single query

rajdh75

Member
Joined
Mar 30, 2020
Messages
23
Programming Experience
Beginner
Hello,
I have a table named TblInvoice which have following columns.
InvoiceID(Primary key), DateofSale, CustomerID, SaleType, TypeofSale.
For transferring data to this table the code is
VB.NET:
 con.Close()
        Try
            con.Open()
            If MsgBox("Save Data to database ?", vbYesNo + vbQuestion) = vbYes Then
                cmd = New OleDbCommand("Insert into TblInvoice (DateofSale,CustomerID,SaleType,TypeofSale)values('" + TxtDate.Text + "','" + TxtID.Text + "','" + CmbSaleType.Text + "','" + CmbTypeofSale.Text + "') ", con)
            Dim a As Integer = cmd.ExecuteNonQuery
                If (a > 0) Then
                    con.Close()
                    MsgBox("Record Saved Successfully", MsgBoxStyle.Information, MsgBoxStyle.OkOnly)
                End If
            Else
                MsgBox("Record not saved.", MsgBoxStyle.Critical, MsgBoxStyle.OkOnly)

            End If
        Catch ex As Exception
            MsgBox("Error", ex.Message)
        Finally
            con.Close()
        End Try
        con.Close()
It code is working well with Button called BtnSave.
I have another table named as TblTest with following columns
InvoiceID(as foreign key), ProductID, ProductName, Qty, Rate, Amount.
Both table are related with InvoiceID.
The datagridview is used to add Products and other columns.
For transferring data to this table the code is
VB.NET:
 For i As Integer = 0 To DataGridView1.Rows.Count - 2 Step +1

            cmd = New OleDbCommand("INSERT INTO TblTest(InvoiceID,ProductID,ProductName,Qty,Rate,Amount) VALUES (@InvoiceID,@ProductID,@ProductName,@Qty,@Rate,@Amount)", con)

            cmd.Parameters.Add("@InvoiceID", OleDbType.Integer).Value = DataGridView1.Rows(i).Cells(0).Value.ToString()
            cmd.Parameters.Add("@ProductID", OleDbType.Integer).Value = DataGridView1.Rows(i).Cells(1).Value.ToString()
            cmd.Parameters.Add("@ProductName", OleDbType.Char).Value = DataGridView1.Rows(i).Cells(2).Value.ToString()
            cmd.Parameters.Add("@Qty", OleDbType.Char).Value = DataGridView1.Rows(i).Cells(3).Value.ToString()
            cmd.Parameters.Add("@Rate", OleDbType.Char).Value = DataGridView1.Rows(i).Cells(4).Value.ToString()
            cmd.Parameters.Add("@Amount", OleDbType.Char).Value = DataGridView1.Rows(i).Cells(5).Value.ToString()
            
            cmd.ExecuteNonQuery()

        Next
        con.Close()
This code is also working with Button called BtnData.
Is there any code for transferring data to these two tables in single query ?
Thanks.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,350
Location
Sydney, Australia
Programming Experience
10+
To answer the question you asked, it would depend on the database you're using but probably not. Even if there was, you shouldn't do it.

In other news, even if you think that you are safe in the first case because your values are coming from ComboBoxes, you should still be using parameters. Just use them every time and then you can't make a mistake. If nothing else, your code will be more readable and therefore less error-prone.

Also, don't loop over the rows of a DataGridView and insert each row separately. Create a DataTable and bind it to the grid to start with, then use a data adapter to save all the data with a single call to Update.

Finally, having columns named SaleType and TypeofSale is bad design. They both say the same thing. If I asked you what car make you drive and what make of car you drive, would you assume I was asking two different questions?
 
Top Bottom