SQL Server Triggers and Defaults not firing on Windows form

[cefrancke]

I have a datagrid on a windows form with a bindingnavigator. The
tableadapter, bindingsource and dataset are there also.


I can browse and edit data normally.


I had added the "click" event to the "save" button on the navigator's
toolstrip.


I would like to have the data for the datagrid's table(or tables if it
is a joined recordset) to be updated to the SQL Server table that is
being displayed in the grid.


I used the following code...


private void saveToolStripButton_Click(object sender, EventArgs

e)
{
try
{
this.Validate();
this.partyBindingSource.EndEdit();
this.partyTableAdapter.Update(this.storeDataSet.Party);

MessageBox.Show("Update successful");
}
catch (System.Exception ex)
{
MessageBox.Show("Update failed");
}
}
}



}


I compared my changes to the SQL table and the changes were there, but
the default values and the values on the fields set by an UPDATE
trigger did not show up.
BTW, the triggers and defaults work when editing in Enterprise Manager
or Query Analyser.

Any ideas?


Also, would this be a good place to call the "Fill" method on the table

adapter to refresh the records after the save/update? (assuming the
triggers and default values work, there would be something to see when
the refresh occurs)


TIA

 

[Morgan]

Default constraints only apply on Insert statements, not Updates.

What does the trigger look like? Keep in note that triggers are "set" based,
in that they fire once per batch, not per-row. At best, the last row updated
will be affected by the trigger, which is why it appears to work in
Enterprise Manager, since the editor is row based.

 

[cefrancke]

Thanks Morgan for helping me think things through.

The problem is:

When a DataSet DataTable includes fields from a table with default
values and/or triggers, the cache in memory has <NULL> values stored
upon .fill method.
So when the database table is updated, the defaults are replaced with
NULLS and the UPDATE trigger wont fire if it depends on the field being
in an UPDATE (SQL Server) state.

Once I removed the fields with default values and UPDATE triggers from
the DataTable Schema, everything worked fine.

If one wants to see default data in a datagridview, I'm having trouble
calling a function in the dataset designer to set the column default
value.
I would love to simple put in the properties for the datatable column a
default value of System.DataTime.Now, but it fails with an error.

Any ideas?

TIA