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 |
Advertisement
Answer
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