Retrieving Identity field

[siddu57]

I have 2 tables - tblOrders and tblOrderDetails . Every time an order is placed, 2 INSERT statements are executed. The first one enters the general order and customer information in the tblOrders table:

INSERT INTO tblOrders (custname, custdetails, orderdate) VALUES (@custname, @custdetails, @orderdate)

The primary key in this table is OrderID which is an Identity column. This is the foreign key in the tblOrderDetails table.

I'm trying to get the Identity value from the first INSERT statement to use in the second INSERT statement:

INSERT INTO tblOrderDetails (orderid, productid, productcost) VALUES (@orderid, @productid, @productcost)

How do i obtain this value and how would I supply it to the second INSERT statement.

[jjvainav]

Right after you do your insert you can do:

INSERT INTO tblOrders (custname, custdetails, orderdate) VALUES (@custname, @custdetails, @orderdate) SET @OrderID = SCOPE_IDENTITY()

See the following link for a detailed example:

http://davidhayden.com/blog/dave/archive/2006/02/16/2803.aspx

[siddu57]

Thank you. That resource is very helpful but I'm not sure how to do this last bit in VB instead of C#:

SqlParameter shipperIDParameter =  new SqlParameter("@ShipperID", SqlDbType.Int);            
shipperIDParameter.Direction = ParameterDirection.Output; insertCommand.Parameters.Add(shipperIDParameter);