asp.net - SQL - INSERT with Scope_Identity() - getting the record id -

i have asp.net page written in vb.net gets items gridview using select statement inner join , allows add item invoice.

inner join gets data items , project_items.

select items.item_id, items.item_name, items.item_cost, project_items.item_quantity items  inner join project_items on items.item_id = project_items.item_id  project_items.project_id = @parameter 

@parameter is session("projectid")

(there foreign key project_items.item_id -> items.item_id.)

i have trying use sql statement in vb.net try , insert 2 tables simultaneously. tried tried item_id of last record created , insert table (project_items) using data. however, data being entered first table.

any idea can do?

this code:

protected sub btnadditem_click(sender object, e eventargs) handles btnadditem.click          dim conn new sqlconnection("data source=brian-pc\sqlexpress;initial catalog=master_db;integrated security=true")         dim additemcomm string = "select item_id project_items project_id=@projectid"         dim user_id_select new integer          dim additemsql new sqlcommand          conn.open()          additemsql = new sqlcommand(additemcomm, conn)         additemsql.parameters.addwithvalue("@projectid", convert.toint32(session("projectid")))           dim datareader sqldatareader = additemsql.executereader()           datareader.close()         conn.close()          dim addnewitemcomm string = "insert items (item_name,  item_cost, item_code) values (@itemname, @itemcost, @itemcode); select scope_identity()"         dim addnewitem2comm string = "insert project_items (item_id, project_id, item_quantity) values (@itemid, @projectid, @itemquantity) "         dim addnewitemsql new sqlcommand           conn.open()          addnewitemsql = new sqlcommand(addnewitemcomm, conn)         addnewitemsql.parameters.addwithvalue("@itemname", txtitemname.text.trim)         addnewitemsql.parameters.addwithvalue("@itemcost", convert.toint32(txtitemcost.text))         addnewitemsql.parameters.addwithvalue("@itemcode", txtitemcost.text.tostring.toupper)          dim itemid integer          itemid = addnewitemsql.executescalar()          addnewitemsql.executenonquery()          conn.close()          conn.open()          addnewitemsql = new sqlcommand(addnewitem2comm, conn)          addnewitemsql.parameters.addwithvalue("@itemid", itemid)         addnewitemsql.parameters.addwithvalue("@projectid", convert.toint32(session("projectid")))         addnewitemsql.parameters.addwithvalue("@itemquantity", convert.toint32(txtitemquantity.text))          addnewitemsql.executenonquery()          conn.close()        end sub 

why doing in multiple statements in first place? why not:

insert dbo.items (item_name,  item_cost, item_code)    output inserted.itemid, @projectid, @itemquantity    dbo.project_items(item_id, project_id, item_quantity) values (@itemname, @itemcost, @itemcode); 

now have call 1 executenonquery() , app doesn't have care scope_identity() value generated. (you can still retrieve scope_identity() if want, of course, using executescalar - nenad rightly points out, pick 1 instead of calling both.)

since know there explicit foreign key here, can still reduce c# code 1 call if can't use output clause.

declare @i int;  insert dbo.items (item_name,  item_cost, item_code)    select @itemname, @itemcost, @itemcode;  select @i = scope_identity();  insert dbo.project_items(item_id, project_id, item_quantity)   select @i, @projectid, @itemquantity   select @i; -- if necessary 

would cleaner put stored procedure.