471,570 Members | 902 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,570 software developers and data experts.

SQL Server Triggers and Defaults not firing on Windows form

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.Pa rty);

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

Jan 15 '06 #1
2 1711
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.

<ce*******@yahoo.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
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.Pa rty);

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

Jan 15 '06 #2
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

Jan 15 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Matt | last post: by
4 posts views Thread by =?Utf-8?B?QW1yaXQgS29obGk=?= | last post: by
3 posts views Thread by Andy Baker | last post: by
4 posts views Thread by abcd | last post: by
reply views Thread by XIAOLAOHU | last post: by
reply views Thread by lumer26 | last post: by
reply views Thread by lumer26 | last post: by
reply views Thread by lumer26 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.