VB.NET Retrieving Identity form MSSQL2000 without using stored procedures

[Taras]

Hello!
I have a problem. I'm using a dataset in VB.NET with multiple tables with
relations between them. I would like dataset to update to all related tables
with right identity, when certain record is added.

The problem is that I don't know how to do it, when I'm not using stored
procedures. I found an article in which OnRowUpdated event of the
dataadapter is used. On this event ExecuteScalar method of the oledbcommand
object is called to retrieve "@@Identity"

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/htm
l/cpconretrievingidentityorautonumbervalues.asp

But this example was for Access 2000 database. I tried to translate it for
MSSQL 2000 but ExecuteScalar always returns
null.

In the same article there's an example which calls a stored procedure in a
insert command. In that case everything is working perfectly, all child
tables in dataset are automatically changed.

Does anybody have any idea how to solve this?

Regards, Taras

 

[alien2_51]

look into 'SetParentRow'
http://www.codeproject.com/cs/database/relationaladonet.asp

 

[Rick Spiewak]

Here is how I do this when adding an order to my order table:

insert orders (order_date, first_name,last_name,title,organization,
address1,address2,city,state,zip,country,phone,email,amount,
bill_to_first_name,bill_to_last_name,bill_to_address1,
bill_to_address2,bill_to_city,bill_to_state,bill_to_zip,bill_to_country)
values ('9/13/2003 8:10:13 PM','Elmer','Fudd','Cartoon Character',
'Looney Tunes','1 Vine St.','2nd Floor','Hollywood Hills','CA','77478',
'USA','508-405-0727','(e-mail address removed)',1.00,'Elmer','Fudd',
'1 Vine St.','2nd Floor','Hollywood Hills','CA','77478','USA')
select @@IDENTITY

Note that this is all one "executescalar" query, returning an integer. In
practice, I build it with stringbuilder. I also use the Microsoft Data
Access Application Block, which I highly recommend, to execute it.