How can I insert data in SQL server to two different tables while having a ORDER# primary key in SQL

Question

I want to insert data on to two different tables in SQL server from VB.net. I have Identity increment set up in SQL that is where the order# comes from. The rest of the data comes from vb.net. Another issue is that every time I capture an order, everything from the Order table copies to the Order Details table which causes a lot of unnecessary duplicated rows. Here is my current code in VB:

   Private Sub btnGetTotal_Click(sender As Object, e As EventArgs) Handles btnGetTotal.Click

    Dim connection As SqlConnection = New SqlConnection("x")

    Dim cmd As New Data.SqlClient.SqlCommand
    cmd.CommandText = "INSERT INTO [dbo].[Ordenes_5]([Sub_Total]) VALUES (@SubTotal)"

    cmd.Parameters.Add("@SubTotal", SqlDbType.VarChar).Value = lbltotal.Text
    connection.Open()
    cmd.Connection = connection
    cmd.ExecuteNonQuery()
    connection.Close()

    Dim icmd As SqlCommand = New SqlCommand("insert into ordenes_5_details (Orden#) select Orden# from Ordenes_5", connection)
    connection.Open()
    icmd.ExecuteNonQuery()
    connection.Close()

    Dim command As New Data.SqlClient.SqlCommand

    command.CommandText = "INSERT INTO dbo.Ordenes_5_details (Articulo, Cantidad, Precio) VALUES (@Articulo, @Cantidad, @Precio)"

    command.Parameters.Add("@Articulo", SqlDbType.VarChar)
    command.Parameters.Add("@Cantidad", SqlDbType.Int)
    command.Parameters.Add("@Precio", SqlDbType.Float)


    connection.Open()
    command.Connection = connection

    For i As Integer = 0 To DataGridView1.Rows.Count - 1

        command.Parameters(0).Value = If(DataGridView1.Rows(i).Cells(0).Value, DBNull.Value)
        command.Parameters(1).Value = If(DataGridView1.Rows(i).Cells(1).Value, DBNull.Value)
        command.Parameters(2).Value = If(DataGridView1.Rows(i).Cells(2).Value, DBNull.Value)


        command.ExecuteNonQuery()

    Next

    MsgBox("se capturo en ambas tablas")
    connection.Close()


End Sub

These are my results in SQL server:

Order table. Orden# is the PRIMARY KEY in this table.

Orden#	Sub_Total
1015	$11.28

Order details table. Orden# is a FOREIGN KEY.

Orden#	Articulo	Cantidad	Precio
1015	NULL	NULL	NULL
NULL	BURRITO	3	6.9
NULL	COOKIE	4	3.96

This is what I'm looking for:

Order table

Orden#	Sub_Total
1015	$11.28

Order details table

Orden#	Articulo	Cantidad	Precio
1015	BURRITO	3	6.9
1015	COOKIE	4	3.96

Does this answer your question? [SQL add data to two tables at once using VB.NET](https://stackoverflow.com/questions/48502696/sql-add-data-to-two-tables-at-once-using-vb-net) — SMor, Sep 10 '21 at 17:13
`command.Parameters.Add("@Cantidad", SqlDbType.VarChar)` You have been told this is a problem; learn to parameterize properly. Your first INSERT is not parameterized at all. Develop good habits - write code consistently. — SMor, Sep 10 '21 at 17:18
@SMor There I changed it just for you cause I appreciate you lookin out for us beginners. — jumexmango, Sep 10 '21 at 20:05
@SMor I have been trying to use `SCOPE_IDENTITY()`, I am getting this error : 'Failed to convert parameter value from a String to a Int32.' I am also now getting an extra row in my Order table. Orden# 1015 | Sub_total 11.28, Orden# 1016 | Sub_total 11.28. How do I implement it correctly? — jumexmango, Sep 10 '21 at 20:28
It's inserting the extra row in order details because you're telling it to, `Dim icmd As SqlCommand = New SqlCommand("insert into ordenes_5_details (Orden#) select Orden# from Ordenes_5", connection)` — AlwaysLearning, Sep 10 '21 at 22:34
Also, you probably want to avoid `float` in your currency columns as this will lead to errors in totals, ref: [float and real (Transact SQL)](https://learn.microsoft.com/en-us/sql/t-sql/data-types/float-and-real-transact-sql) _Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly._ — AlwaysLearning, Sep 10 '21 at 22:37

score 1 · Accepted Answer · answered Sep 11 '21 at 00:48

Order/Order Detail pattern is quite common in database work so you need to become comfortable with it. There are several ways to deal with this. I will demonstrate one way.

You have already been cautioned in comments that the field in the database for currency amounts should be decimal.

Database objects like Connection and Command use unmanaged code. To release these resources, the architects of these libraries provide Dispose methods. .Net has provided vb.net with Using...End Using blocks to close and dispose objects. You can combine more than one object in a single Using block by using commas.

You can pass the CommandText and the Connection directly to the constructor of the Command. I have added an extra line to the first Insert statement. This is and output parameter. We add the parameter to the parameters collection and set the Direction. Retrieve the value after the Command is executed.

The second Insert can now add the field Oden# and a parameter, @OrderID, to the VALUES list. We set the Value to NewOrderID since it is the same for each execution of the Command.

Since I added a parameter, I changed the indexes of the Parameters in the For loop.

Private Sub btnGetTotal_Click(sender As Object, e As EventArgs) Handles btnGetTotal.Click
    Dim sql = "INSERT INTO [dbo].[Ordenes_5]([Sub_Total]) VALUES (@SubTotal);
                Set @ID = SCOPE_IDENTITY();"
    Dim NewOrderID As Integer
    Using connection As New SqlConnection("x"),
            cmd As New SqlCommand(sql, connection)
        cmd.Parameters.Add("@SubTotal", SqlDbType.Decimal).Value = CDec(lbltotal.Text)
        cmd.Parameters.Add("@ID", SqlDbType.Int).Direction = ParameterDirection.Output
        connection.Open()
        cmd.ExecuteNonQuery()
        NewOrderID = CInt(cmd.Parameters("ID").Value)
    End Using

    sql = "INSERT INTO dbo.Ordenes_5_details (Oden#, Articulo, Cantidad, Precio) VALUES (@OrderID, @Articulo, @Cantidad, @Precio)"
    Using cn As New SqlConnection("Your connection string"),
            cmd As New SqlCommand(sql, cn)
        cmd.Parameters.Add("@OrderId", SqlDbType.Int).Value = NewOrderID
        cmd.Parameters.Add("@Articulo", SqlDbType.VarChar)
        cmd.Parameters.Add("@Cantidad", SqlDbType.Int)
        cmd.Parameters.Add("@Precio", SqlDbType.Decimal)
        cn.Open()
        For i As Integer = 0 To DataGridView1.Rows.Count - 1
            cmd.Parameters(1).Value = If(DataGridView1.Rows(i).Cells(0).Value, DBNull.Value)
            cmd.Parameters(2).Value = If(DataGridView1.Rows(i).Cells(1).Value, DBNull.Value)
            cmd.Parameters(3).Value = If(DataGridView1.Rows(i).Cells(2).Value, DBNull.Value)
            cmd.ExecuteNonQuery()
        Next
    End Using
    MessageBox.Show("se capturo en ambas tablas")
End Sub

How can I insert data in SQL server to two different tables while having a ORDER# primary key in SQL

1 Answers1