Hi,

Could somebody give me some clues as to what is the best way to get a newly generated pkey value back from sql server.

I'm using a dataset and using the sqlcommandbuilder to automatically generate all my insert, delete and update commands. it looks a little like this :

Dim CommandBuilder As New SqlCommandBuilder(DataAdapter)
DataAdapter.DeleteCommand = CommandBuilder.GetDeleteCommand()
DataAdapter.UpdateCommand = CommandBuilder.GetUpdateCommand()
DataAdapter.InsertCommand = CommandBuilder.GetInsertCommand()

Dim ObjNewRow As DataRow
ObjNewRow = dataset.Tables(0).NewRow
dataset.Tables(0).Rows.Add(ObjNewRow)

I have my tables set up with integer based primary key fields which are set up to be auto-incrementing identity fields.

What's the most efficent and flexible way of getting back the pkey from sql server so that it appears in the table in my dataset.

I've read the MS example of creating stored procedures within sql server which perform the insert and pass the pkey back, but this mean I'd have to create a seperate stored procedure for each table, maybe a seperate sp if the same table has a different set of fields being updated

Is they a better way?

Many thanks, Bernard