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.

Order details table. Orden# is a FOREIGN KEY.

This is what I'm looking for:

Order table

Order details table

CodePudding user response：

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