Obtaining Primary Key Values

Use a stored procedure to obtain values generated by the data source if the table you are updating has an auto-incremented primary key, or if the primary key comes from a primary key pool.

When using the AseDataAdapter, this technique can be used to fill the columns in the DataSet with the primary key values generated by the data source. If you use this technique with the AseCommand object, you can either get the key columns from the parameters or reopen the DataReader.

Use a table “adodotnet_primarykey” that contains two columns, “id” and “name.” The primary key for the table is “id,” which is a NUMERIC(8) that contains an auto-incremented value; the name column is CHAR(40).

These examples call the following stored procedure to retrieve the auto-incremented primary key value from the database:

create procedure sp_adodotnet_primarykey
@p_name char(40),
@p_id int output
as 
begin 
   insert into adodotnet_primarykey(name)
      VALUES(@p_name)
   select @p_id = @@identity
END