sql server - Insert multiple rows, count based on another table columns

Question

I have 3 Tables. Item, StockDetail, Branch

I want to INSERT 2 of them at once. The Item and StockDetail table.

Item has 3 columns = ItemID, Title, Price.

StockDetail has 3 columns = ItemID, BranchID, Stock.

Branch has 1 column = BranchID.

In this code below, INSERT into Item works fine, but not for StockDetail table, it doesn't INSERT anything!

Now for the StockDetail if it works, I want to insert it with the condition below:

If you add an item, then it'll add this item with all existed BranchID.

That mean, every Branches will have this item.

e.g:

You add an item, while

Branch has 3 rows of BranchID = BR000, BR001, BR002.

It will insert to StockDetail with 3 rows as well, at once (single Query)

Complete result of StockDetail (single Query):

  ItemID | BranchID  | Stock
______________________________
  IM000  |   BR000   |   0
  IM000  |   BR001   |   0
  IM000  |   BR002   |   0

---

The Code:

'Add function'
'Insert to StockDetail'
Dim theCommand As New SqlCommand
Dim theDataAdapter As New SqlDataAdapter
Dim theDataTable As New DataTable
theCommand.Connection = theConnection
theCommand.CommandText = "INSERT INTO StockDetail VALUES(
                         '" & Me.TextBox_ItemID.Text & "',
                         SELECT COUNT(BranchID) FROM Branch,
                         '0'
                         )"
theDataAdapter.SelectCommand = theCommand
'Insert to Item'
theCommand.Connection = theConnection
theCommand.CommandText = "INSERT INTO Item VALUES('" & Me.TextBox_ItemID.Text & "', '" & Me.TextBox_Title.Text & "', '" & Me.TextBox_Price.Text & "')"
theDataAdapter.SelectCommand = theCommand
theDataAdapter.Fill(theDataTable)
DataGridView_Item.DataSource = theDataTable
theCommand.Dispose()
theDataAdapter.Dispose()

UPDATE:

The code below will tell you the working multiple INSERT, but not with the BranchID INSERT.

'Insert to StockDetail'
theConnection.Open()
Dim theCommand As New SqlCommand
Dim theDataAdapter As New SqlDataAdapter
theCommand.Connection = theConnection
theCommand.Parameters.Add("@ItemID", SqlDbType.VarChar).Value = Me.TextBox_ItemID.Text
theCommand.CommandText = "INSERT INTO StockDetail(ItemID, BranchID, Stock) SELECT @ItemID, COUNT(Branch.BranchID), '0' FROM Branch GROUP BY Branch.BranchID"
theDataAdapter.SelectCommand = theCommand
Using theDataAdapter
    theCommand.ExecuteNonQuery()
    theCommand.Parameters.Clear()
    theCommand.Dispose()
    theConnection.Close()
    SqlConnection.ClearPool(theConnection)
End Using

What do I want now?

Well instead of inserting 1, 1, . . .

I'd like to INSERT it with BR000, BR001 . . . (Based on all existed BranchID)

Answer 1

Here is how you use a parameter in your first insert statement. I think you still have some very serious logic issues here though. This is going to insert 1 row into StockDetail and the values don't make any sense at all. You would be inserting the count of rows from the Branch table as the BranchID which is probably not what you really want. I suspect what you want is a row in this table for each Branch?

theCommand.CommandText = "INSERT INTO StockDetail(ItemID, BranchID, Price) VALUES(
                         @ItemID,
                         (SELECT COUNT(BranchID) FROM Branch),
                         0
                         )"
theCommand.Parameters.Add("@ItemID", SqlDbType.Varchar).Value = Me.TextBox_ItemID.Text;

I suspect what you really want is something more like this.

theCommand.CommandText = "INSERT INTO StockDetail(ItemID, BranchID, Price) 
                        select @ItemID
                            , BranchID
                            , 0
                        from Branch";
theCommand.Parameter.Add("@ItemID", SqlDbType.Varchar).Value = Me.TextBox_ItemID.Text;