Bernard,

We use Stored Procs for all our database access, but that also means that we don't use the CommandBuilder, so that would be a bit of a shift in paradigm for you.

Yes, we have Stored Procs for every table ... we have 3 for each table, a SELECT, a DELETE and a PUT (which does an update or an insert depending on whether or not the PK is non-zero). However, we've got a utility (written in VFP because it was easier at the time <g>) that will automatically generate these procs, so it's no big deal to have to have them. We call these our basic Stored Procs. There are obviously lots of others as well, but these basic ones do the brunt of the update work.

Let me know if you want more information on this approach or not.

~~Bonnie


>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