>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
FWIW switch from integer autoinc keys to uniqueidentifier (NewId()) as the PK. Then you would create GUID and know the key before you submit it.
Cetin