[WinForms] Databinding and an update trigger

[E®!k \\/!sser]

Hi all,
I am facing the following problem.
I have a Invoice form with a grid, the Invoice_details.
Now some data on the Invoice_Details form depend upon the data on the
Invoice grid. For example, on the Invoice_Details form is a combox and the
listed values differ for each (group of) customers.
As the whole application is datadriven, the changes on the Invoice form
needs to be written to the database before the Invoice_Details form is build
up.
So this code is called to write the changes to the database: (Before the
Invoice_Details form is opened)

this.Validate();
this.oBindingSource.EndEdit();
new SqlCommandBuilder(this.DaUpdate);
this.DaUpdate.Update(this.DtUpdate);
this.DtUpdate.AcceptChanges();

This works ok the first time it is called.
But when the user makes another change on the invoice form, after editing
the Invoice_Details, I get a concurrency exception. This has to do with a
trigger on the database table that updates a LastChanged_Date column on the
Invoice table on every time changes are written to the database. Removing
the trigger solves the problem, but you'll understand that is not the real
solution.

So I guess I'll have to update the datatable with the new values on the
database.

this.DaUpdate.Fill(this.DtUpdate);
this.oBindingSource.ResetBindings(false);

But this does not solve it: the problem remains.

Any comments on this are highly apreciated.


Erik

 

[vanderghast]

Since the ADONet model is disconnected, when it tries to update a record, it
looks at the oldValues, the values that were in each field when you read
them initially, and compare them to the actual value in the same record, in
the DATABASE, the databaseValues. If they are all equal, it is safe to
assume no one else has change anything since the moment you read the data
and so, that you can safely update the values with your newValues. Now, if
the oldValues differ from the databaseValues, it assumes someone else may
have modify the record and thus, don't let you blindly update the record,
since you were working on 'buffered' data which is clearly invalid, at this
point.

There are many solutions. As example, you can 'mark' some fields as being
'irrelevant' for the check process involving oldValues and databaseValue,
which can make your application runs a little bit faster (and the exact way
to do it depends on what tool you use to define your datasets), or, if are
sure that your application is the only one working on that set of data, you
can simply issue your own sqlCommand to UPDATE the data:

sqlCommand.CommandType=CommandType.Text;
sqlCommand.CommandText = "UPDATE ... "
sqlCommand.ExecuteNonQuery( );

as example, rather than relaying on the full-check-implemented default
Update method.


Or maybe, you should take time to see if the overall design about the data
could not be improved in the first place, to avoid the problem.

Note: with C#, you can get what I called 'oldValues' and 'newValues' for a
DataRow with the syntax:

myDataRow["fieldname", DataRowVersion.Original]

or with DataRowVersion.Proposed as second parameter.



Vanderghast, Access MVP

 

[vanderghast]

Since the ADONet model is disconnected, when it tries to update a record, it
looks at the oldValues, the values that were in each field when you read
them initially, and compare them to the actual value in the same record, in
the DATABASE, the databaseValues. If they are all equal, it is safe to
assume no one else has change anything since the moment you read the data
and so, that you can safely update the values with your newValues. Now, if
the oldValues differ from the databaseValues, it assumes someone else may
have modify the record and thus, don't let you blindly update the record,
since you were working on 'buffered' data which is clearly invalid, at this
point.

There are many solutions. As example, you can 'mark' some fields as being
'irrelevant' for the check process involving oldValues and databaseValue,
which can make your application runs a little bit faster (and the exact way
to do it depends on what tool you use to define your datasets), or, if are
sure that your application is the only one working on that set of data, you
can simply issue your own sqlCommand to UPDATE the data:

sqlCommand.CommandType=CommandType.Text;
sqlCommand.CommandText = "UPDATE ... "
sqlCommand.ExecuteNonQuery( );

as example, rather than relaying on the full-check-implemented default
Update method.


Or maybe, you should take time to see if the overall design about the data
could not be improved in the first place, to avoid the problem.

Note: with C#, you can get what I called 'oldValues' and 'newValues' for a
DataRow with the syntax:

myDataRow["fieldname", DataRowVersion.Original]

or with DataRowVersion.Proposed as second parameter.



Vanderghast, Access MVP

 

[E®!k \\/!sser]

Thanks,

The problem is not how to write the updated data, but to (re)read the data
modified by the trigger(s)

Erik

Since the ADONet model is disconnected, when it tries to update a record,
it looks at the oldValues, the values that were in each field when you
read them initially, and compare them to the actual value in the same
record, in the DATABASE, the databaseValues. If they are all equal, it is
safe to assume no one else has change anything since the moment you read
the data and so, that you can safely update the values with your
newValues. Now, if the oldValues differ from the databaseValues, it
assumes someone else may have modify the record and thus, don't let you
blindly update the record, since you were working on 'buffered' data which
is clearly invalid, at this point.

Of course, but I already was aware of that.

There are many solutions. As example, you can 'mark' some fields as being
'irrelevant' for the check process involving oldValues and databaseValue,
which can make your application runs a little bit faster (and the exact
way to do it depends on what tool you use to define your datasets)

Not sure what you mean by this. I do not use datasets, but datatables.

, or, if are

sure that your application is the only one working on that set of data,
you can simply issue your own sqlCommand to UPDATE the data:

sqlCommand.CommandType=CommandType.Text;
sqlCommand.CommandText = "UPDATE ... "
sqlCommand.ExecuteNonQuery( );

as example, rather than relaying on the full-check-implemented default
Update method.


Or maybe, you should take time to see if the overall design about the data
could not be improved in the first place, to avoid the problem.

Note: with C#, you can get what I called 'oldValues' and 'newValues' for
a DataRow with the syntax:

myDataRow["fieldname", DataRowVersion.Original]

or with DataRowVersion.Proposed as second parameter.

Yes, i Know. I have a Concurrency- conflict handler based on this.

 

[E®!k \\/!sser]

Thanks,

The problem is not how to write the updated data, but to (re)read the data
modified by the trigger(s)

Erik

Since the ADONet model is disconnected, when it tries to update a record,
it looks at the oldValues, the values that were in each field when you
read them initially, and compare them to the actual value in the same
record, in the DATABASE, the databaseValues. If they are all equal, it is
safe to assume no one else has change anything since the moment you read
the data and so, that you can safely update the values with your
newValues. Now, if the oldValues differ from the databaseValues, it
assumes someone else may have modify the record and thus, don't let you
blindly update the record, since you were working on 'buffered' data which
is clearly invalid, at this point.

Of course, but I already was aware of that.

There are many solutions. As example, you can 'mark' some fields as being
'irrelevant' for the check process involving oldValues and databaseValue,
which can make your application runs a little bit faster (and the exact
way to do it depends on what tool you use to define your datasets)

Not sure what you mean by this. I do not use datasets, but datatables.

, or, if are

sure that your application is the only one working on that set of data,
you can simply issue your own sqlCommand to UPDATE the data:

sqlCommand.CommandType=CommandType.Text;
sqlCommand.CommandText = "UPDATE ... "
sqlCommand.ExecuteNonQuery( );

as example, rather than relaying on the full-check-implemented default
Update method.


Or maybe, you should take time to see if the overall design about the data
could not be improved in the first place, to avoid the problem.

Note: with C#, you can get what I called 'oldValues' and 'newValues' for
a DataRow with the syntax:

myDataRow["fieldname", DataRowVersion.Original]

or with DataRowVersion.Proposed as second parameter.

Yes, i Know. I have a Concurrency- conflict handler based on this.

 

[vanderghast]

Maybe someone else has an idea, but given the fact you use trigger to update
(delete? ) other records, I personnally see nothing not obvious.

Vanderghast, Access MVP

Thanks,

The problem is not how to write the updated data, but to (re)read the data
modified by the trigger(s)

Erik

Since the ADONet model is disconnected, when it tries to update a record,
it looks at the oldValues, the values that were in each field when you
read them initially, and compare them to the actual value in the same
record, in the DATABASE, the databaseValues. If they are all equal, it is
safe to assume no one else has change anything since the moment you read
the data and so, that you can safely update the values with your
newValues. Now, if the oldValues differ from the databaseValues, it
assumes someone else may have modify the record and thus, don't let you
blindly update the record, since you were working on 'buffered' data
which is clearly invalid, at this point.

Of course, but I already was aware of that.

There are many solutions. As example, you can 'mark' some fields as being
'irrelevant' for the check process involving oldValues and databaseValue,
which can make your application runs a little bit faster (and the exact
way to do it depends on what tool you use to define your datasets)

Not sure what you mean by this. I do not use datasets, but datatables.

, or, if are

sure that your application is the only one working on that set of data,
you can simply issue your own sqlCommand to UPDATE the data:

sqlCommand.CommandType=CommandType.Text;
sqlCommand.CommandText = "UPDATE ... "
sqlCommand.ExecuteNonQuery( );

as example, rather than relaying on the full-check-implemented default
Update method.


Or maybe, you should take time to see if the overall design about the
data could not be improved in the first place, to avoid the problem.

Note: with C#, you can get what I called 'oldValues' and 'newValues'
for a DataRow with the syntax:

myDataRow["fieldname", DataRowVersion.Original]

or with DataRowVersion.Proposed as second parameter.

Yes, i Know. I have a Concurrency- conflict handler based on this.

Vanderghast, Access MVP

 

[vanderghast]

Maybe someone else has an idea, but given the fact you use trigger to update
(delete? ) other records, I personnally see nothing not obvious.

Vanderghast, Access MVP

Thanks,

The problem is not how to write the updated data, but to (re)read the data
modified by the trigger(s)

Erik

Since the ADONet model is disconnected, when it tries to update a record,
it looks at the oldValues, the values that were in each field when you
read them initially, and compare them to the actual value in the same
record, in the DATABASE, the databaseValues. If they are all equal, it is
safe to assume no one else has change anything since the moment you read
the data and so, that you can safely update the values with your
newValues. Now, if the oldValues differ from the databaseValues, it
assumes someone else may have modify the record and thus, don't let you
blindly update the record, since you were working on 'buffered' data
which is clearly invalid, at this point.

Of course, but I already was aware of that.

There are many solutions. As example, you can 'mark' some fields as being
'irrelevant' for the check process involving oldValues and databaseValue,
which can make your application runs a little bit faster (and the exact
way to do it depends on what tool you use to define your datasets)

Not sure what you mean by this. I do not use datasets, but datatables.

, or, if are

sure that your application is the only one working on that set of data,
you can simply issue your own sqlCommand to UPDATE the data:

sqlCommand.CommandType=CommandType.Text;
sqlCommand.CommandText = "UPDATE ... "
sqlCommand.ExecuteNonQuery( );

as example, rather than relaying on the full-check-implemented default
Update method.


Or maybe, you should take time to see if the overall design about the
data could not be improved in the first place, to avoid the problem.

Note: with C#, you can get what I called 'oldValues' and 'newValues'
for a DataRow with the syntax:

myDataRow["fieldname", DataRowVersion.Original]

or with DataRowVersion.Proposed as second parameter.

Yes, i Know. I have a Concurrency- conflict handler based on this.

Vanderghast, Access MVP