.net - the - Possible to retrieve IDENTITY column value on insert using SqlCommandBuilder(without using Stored Proc)?

return identity after sql insert stored procedure c# (6)

The insert command can be instructed to update the inserted record using either output parameters or the first returned record (or both) using the UpdatedRowSource property...

InsertCommand.UpdatedRowSource = UpdateRowSource.Both;

If you wanted to use a stored procedure, you'd be done. But you want to use a raw command (aka the output of the command builder), which doesn't allow for either a) output parameters or b) returning a record. Why is this? Well for a) this is what your InsertCommand will look like...

INSERT INTO [SomeTable] ([Name]) VALUES (@Name)

There's no way to enter an output parameter in the command. So what about b)? Unfortunately, the DataAdapter executes the Insert command by calling the commands ExecuteNonQuery method. This does not return any records, so there is no way for the adapter to update the inserted record.

So you need to either use a stored proc, or give up on using the DataAdapter.

FYI: I am running on dotnet 3.5 SP1

I am trying to retrieve the value of an identity column into my dataset after performing an update (using a SqlDataAdapter and SqlCommandBuilder). After performing SqlDataAdapter.Update(myDataset), I want to be able to read the auto-assigned value of myDataset.tables(0).Rows(0)("ID"), but it is System.DBNull (despite the fact that the row was inserted).

(Note: I do not want to explicitly write a new stored procedure to do this!)

One method often posted http://forums.asp.net/t/951025.aspx modifies the SqlDataAdapter.InsertCommand and UpdatedRowSource like so:

SqlDataAdapter.InsertCommand.CommandText += "; SELECT MyTableID = SCOPE_IDENTITY()"
InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord

Apparently, this seemed to work for many people in the past, but does not work for me.

Another technique: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=619031&SiteID=1 doesn't work for me either, as after executing the SqlDataAdapter.Update, the SqlDataAdapter.InsertCommand.Parameters collection is reset to the original (losing the additional added parameter).

Does anyone know the answer to this???

This is a problem that I've run into before, the bug seems to be that when you call da.Update(ds); the parameters array of the insert command gets reset to the inital list that was created form your command builder, it removes your added output parameters for the identity.

The solution is to create a new dataAdapter and copy in the commands, then use this new one to do your da.update(ds);


SqlDataAdapter da = new SqlDataAdapter("select Top 0 " + GetTableSelectList(dt) + 
"FROM " + tableName,_sqlConnectString);
SqlCommandBuilder custCB = new SqlCommandBuilder(da);
custCB.QuotePrefix = "[";
custCB.QuoteSuffix = "]";
da.TableMappings.Add("Table", dt.TableName);

da.UpdateCommand = custCB.GetUpdateCommand();
da.InsertCommand = custCB.GetInsertCommand();
da.DeleteCommand = custCB.GetDeleteCommand();

da.InsertCommand.CommandText = String.Concat(da.InsertCommand.CommandText, 
"; SELECT ",GetTableSelectList(dt)," From ", tableName, 
" where ",pKeyName,"=SCOPE_IDENTITY()");

SqlParameter identParam = new SqlParameter("@Identity", SqlDbType.BigInt, 0, pKeyName);
identParam.Direction = ParameterDirection.Output;

da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;

//new adaptor for performing the update                     
SqlDataAdapter daAutoNum = new SqlDataAdapter();
daAutoNum.DeleteCommand = da.DeleteCommand;
daAutoNum.InsertCommand = da.InsertCommand;
daAutoNum.UpdateCommand = da.UpdateCommand;


You can only issue a single statement per batch in SQL Server Compact, and you cannot use Scope_identity, but must use @@IDENTITY (remember to keep the connection open betweeen the two calls)

database inserts Identity columns byitself...you dont have to include identty column value in your query. If problems are coming try,take the value that your command builder is generating in runtime and execute it in database and see what exactly is happening Make sure our query is in right sequence as in it matches the sequence of column in your table.

Inserting Row and getting Id, get error

See the answer to this question for a suggestion on how to make your example work:

You should change your INSERT to return that inserted ID to you right away (in an OUTPUT clause)

how to get autoincrement id after tableAdapter.update vb

You can get the last IDENTITY Insert "Using SCOPE_IDENTITY with CommandBuilder and DataAdapter"

In stored procedure :

CREATE PROCEDURE dbo.InsertCategory
  @CategoryName nvarchar(15),
  @Identity int OUT
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)

IN VB.net:

SqlParameter identParam = new SqlParameter("@Identity", SqlDbType.BigInt, 0, pKeyName);
identParam.Direction = ParameterDirection.Output;

Please check out following links:

Possible to retrieve IDENTITY column value on insert using SqlCommandBuilder (without using Stored Proc)?