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

i have 3 tables. item, stockdetail, branch

i want insert 2 of them @ once. item , stockdetail table.

item has 3 columns = itemid, title, price.

stockdetail has 3 columns = itemid, branchid, stock.

branch has 1 column = branchid.

in code below, insert item works fine, not stockdetail table, doesn't insert anything!

now stockdetail if works, want insert condition below:

if add item, it'll add item existed branchid.

that mean, every branches have item.

e.g:

you add item, while

branch has 3 rows of branchid = br000, br001, br002.

it insert stockdetail 3 rows well, @ 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 stockdetail' dim thecommand new sqlcommand dim thedataadapter new sqldataadapter dim thedatatable new datatable thecommand.connection = theconnection thecommand.commandtext = "insert stockdetail values(                          '" & me.textbox_itemid.text & "',                          select count(branchid) branch,                          '0'                          )" thedataadapter.selectcommand = thecommand 'insert item' thecommand.connection = theconnection thecommand.commandtext = "insert 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 tell working multiple insert, not branchid insert.

'insert stockdetail' theconnection.open() dim thecommand new sqlcommand dim thedataadapter new sqldataadapter thecommand.connection = theconnection thecommand.parameters.add("@itemid", sqldbtype.varchar).value = me.textbox_itemid.text thecommand.commandtext = "insert stockdetail(itemid, branchid, stock) select @itemid, count(branch.branchid), '0' branch group branch.branchid" thedataadapter.selectcommand = thecommand using thedataadapter     thecommand.executenonquery()     thecommand.parameters.clear()     thecommand.dispose()     theconnection.close()     sqlconnection.clearpool(theconnection) end using 

what want now?

well instead of inserting 1, 1, . . .

i'd insert br000, br001 . . . (based on existed branchid)

here how use parameter in first insert statement. think still have serious logic issues here though. going insert 1 row stockdetail , values don't make sense @ all. inserting count of rows branch table branchid not want. suspect want row in table each branch?

thecommand.commandtext = "insert stockdetail(itemid, branchid, price) values(                          @itemid,                          (select count(branchid) branch),                          0                          )" thecommand.parameters.add("@itemid", sqldbtype.varchar).value = me.textbox_itemid.text; 

i suspect want more this.

thecommand.commandtext = "insert stockdetail(itemid, branchid, price)                          select @itemid                             , branchid                             , 0                         branch"; thecommand.parameter.add("@itemid", sqldbtype.varchar).value = me.textbox_itemid.text;