StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POADO .net 2.0: PrimaryKey not updated after calling DataAdapter.Update()
text
Body
copied!<p>I came across a problem while updating a typed DataTable that has a primary key column. At some point in my code I fill DataTables (some of them have primary and foreign key columns) and then I insert the data of all DataTables in one transaction using DataAdapters and Update(). Because the typed DataTables do not allow the PrimaryKey table to be empty I insert some integer values in there. After calling Update() I expected the PK columns to be updated with the database PKs.</p> <pre><code> public void UpdateMethod(DbTransaction transaction) { DbDataAdapter dataAdapter = mDbProviderFactory.CreateDataAdapter(); using (DbCommand insertCommand = CreateCommand()) { insertCommand.Connection = mDbConnection; insertCommand.Transaction = transaction; dataAdapter.InsertCommand = insertCommand; dataAdapter.Update(dataTable); } // not sure if i need to do this: dataTable.AcceptChanges(); // I would expect that databaseId is now the Id used in the database, // but it is the original Id which I set while creating the row entry databaseId = (int)dataTable.Rows[0]["Id"]; } private DbCommand CreateCommand() { // Make command object. DbCommand cmd = mDbProviderFactory.CreateCommand(); // add command input parameters DbParameter parameter1 = mDbProviderFactory.CreateParameter(); parameter1.ParameterName = mDatabaseParameterPrefix + "someColumn"; parameter1.SourceColumn = "someColumn"; parameter1.Size = 255; parameter1.DbType = DbType.String; // Output parameter DbParameter idParameter = mDbProviderFactory.CreateParameter(); idParameter.ParameterName = mDatabaseParameterPrefix + "ID"; idParameter.SourceColumn = "ID"; idParameter.Direction = ParameterDirection.InputOutput; idParameter.DbType = DbType.Int32; // setup sql command cmd.Parameters.Add(parameter1); cmd.Parameters.Add(idParameter) cmd.CommandText = @"INSERT INTO [SomeTable] ([someColumn], ...) VALUES(@someColumn, ... ) SELECT CAST(SCOPE_IDENTITY() AS int) AS 'ID'"; cmd.UpdatedRowSource = UpdateRowSource.Both; return cmd; } </code></pre> <p>Thanks for any hints!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload