DataAdapter update not including all parameters

[kwgainey]

Hi folks,

I have some code which takes a datatable, makes some changes to the
rows, and uses the dataadapter.update(datatable) command to write the
changes back to the db. When I look at the changed rows in the
datatable (and export them out to xml), the updated fields are present.
When I capture the UpdatingEvent thrown by the dataadapter, the
updatecommand is missing one of my parameters. Because of this, the
changes don't make it into the database.

What would cause a parameter to NOT be included in the update command?
The field does allow nulls and every other field in the table is
updating fine, so I know the transaction is committing. Thanks in
advance for your help!

-Kevin

Code (some of the variable declarations moved for brevity):

public void updateProcedure(ref SqlTransaction tran)
//transaction object passed into this method

//create the datatable to be updated and dataadapter
updateDataAdapter = new SqlDataAdapter();
updateDataAdapter.SelectCommand = new SqlCommand(sqlStatement, cn,
tran);
SqlCommandBuilder custCB = new SQLCommandBuilder(updateDataAdapter);
DataTable updateTable = new DataTable();
updateDataAdapter.Fill(updateTable)

//call method to make some changes to the updateTable

//now that the table has been update, write to xml to confirm changes
DataTable xDataTable = updateTable.GetChanges();
WriteXMLToFile(xDataTable, "c:\temp\DataTableChanges.xml");

//perform the update
//add update handlers
updateDataAdapter.RowUpdating += new
SqlRowUpdatingEventHandler(OnRowUpdating);
updateDataAdapter.RowUpdated += new SqlRowUpdatedEventHandler(
OnRowUpdated );

//call the update method
updateDataAdapter.Update(updateTable);

//remove handlers
updateDataAdapter.RowUpdating -= new
SqlRowUpdatingEventHandler(OnRowUpdating);
updateDataAdapter.RowUpdated -= new SqlRowUpdatedEventHandler(
OnRowUpdated );

//this method exits and the transaction object which was passed in is
committed.

 

[W.G. Ryan - MVP]

What do you mean by "Missing" is it not there or is a value not present.

 

[kwgainey]

Sorry for the confusion. When I say missing, I mean that neither the
fieldname or a value to populate it is in the UpdateCommand string.
For example, I have 15 fields (not including the primary key) in the
database schema. The updateTable object shows all 15 fields. When
..NET populates the UpdateCommand.CommandText property, only 14 of the
updateable fields are in the SET or WHERE sections of the SQL
statement. It truly is missing and does not show up at all in the
Command objects Parameters collection. Let me know if that makes sense
or you need additional info. Thanks in advance!

 

[Cor Ligthert [MVP]]

Kwgainey,

Can it be that there is a timestamp field including your columns?

Cor

 

[kwgainey]

Cor,

There are no timestamp fields in this table. Plus, other columns in
the table update fine. Thanks.

 

[Marina]

how does the UpdateCommand get created? If you are using the design time
wizards, then maybe you changed the database schema after the fact, or maybe
you just need to rerun the wizard. I would also try to see what is
different about the column being omitted.

 

[Ola Fjelddahl]

hejdig.

It sounds like the adapter and your tables are out of sync.

Check:
DataTable has the right columns with the right types and the right allowNull
and map to the right DatabaseTableColumn.
UpdateCommand SQL string looks right.
Check the parameter collection that the names are right and the types are
right, a tedious task.
Run the Update command from the wizard.

Or
If it is a simple TableAdapter - recreate it; probably faster.

HTH

/OF