modifying a textbox bound to datasource - rowstate always says UNCHANGED

[JohnR]

I have a table with 1 row which is used to hold some application wide items
(one item per field, hence I only need 1 row).

I want to bind one of the fields to a textbox. After setting up the
oledbconnection and dataAdapter and filling the DataSet (ds) I tried this:

TextBox1.DataBindings.Add("text", ds.Tables.Item("MyFile"), "MyField")

I then put the following code in the SAVE button click event:

Dim dsChanged As DataSet = ds.GetChanges()
Try ***************** I put a
breakpoint here and examine ds.tables.item("MyFile").rows(0).rowstate
daMyFile.Update(dsChanged.Tables("MyFile"))
Catch ex As OleDbException
MsgBox(ex.ToString)
Catch ex As InvalidOperationException
MsgBox(ex.ToString)
Catch ex As DBConcurrencyException
MsgBox("concurrency exception - another user has modified this record" &
vbCr & vbLf & ex.ToString)
ds.RejectChanges()
Exit Sub
End Try
ds.AcceptChanges()

When I run the program I see the proper initial value in the textbox (this
value was obtained from the field MyField in the table MyFile), so I know
the binding is grabing the value from the right place.

However, when I change the value in the textbox and hit the SAVE button, at
the breakpoint I examine the dataset and the rowstate for the row says it is
UNCHANGED. I looked at this because I wondered why my database was not
being updated with the new value. At least I now know why the datatable
didn't change... But can anybody tell me why, when I change the value of
the textbox, the rowstate doesn't reflect the changed status???

I know I must be overlooking something simple, and if anybody can help I
would greatly appreciate it.

Thanks,
John

 

[Cor Ligthert [MVP]]

John

One of the most asked questions in these newsgroups.
For databinding there has to be a change of a control others than a button.
Therefore you have to push the data down by hand when you click a button.
(To do it efficient you can do it before the update command)

BindingContext(ds.Tables(0)).EndCurrentEdit()

I hope this helps,

Cor

 

[JohnR]

Hi Cor,

Again, you have come to my aid! I thank you so much for your
assistance. In an effort to see if I understand exactly what is happening
and to present a fuller picture for those who would benefit from it, let me
try to summerize:

If you bind a textbox to a dataset which has been filled with records from a
table you are dealing with 3 distinct items.

1. the actual textbox.text property, which you can type into
2. the dataset which sits in between the textbox and the datatable on the
disk
3. the actual datatable on the disk.

After typing something into the textbox.text property no changes are made to
either the dataset or datatable.

You can then use the associated bindingcontext to either end the current
edit, like this:

BindingContext(ds.Tables.Item("MyTable")).EndCurrentEdit()

in which case the changes to the textbox.text property is transfered to the
dataset.

Or, you can use the associated bindingcontext to cancel any changes you made
to the textbox.text property, like this:

BindingContext(ds.Tables.Item("MyTable")).CancelCurrentEdit()

in which case the changes to the textbox.text property are cancelled and the
dataset remains unchanged.

One of the great things about a dataset is that it automatically keeps the
different "states" of your data. That is, after pushing the textbox changes
to the dataset you can access both the "changed" data or the "original" data
(the dataset keeps both),
by using the "DataRowVersion" specifier when accessing a column in a row...
like this:

ds.Tables.Item(0).Rows(0)(0, DataRowVersion.Current) --will get the current
value of the field
ds.Tables.Item(0).Rows(0)(0, DataRowVersion.Original) --will get the
original value of the field

Now we have one more thing to do. We need to push the changed data from the
dataset to the datatable on the disk.
You do this by using the UPDATE method of the DataAdapter (you must have
previously specified the update command for the dataadapter). For efficency
I transfer all changed records to a separate dataset (I call it dsChanged)
and then check to make sure changes exist (ie: is dsChanged nothing?), and
then, if changes exist, use the update method to push the changes back to
the datatable on disk, like this:

Dim dsChanged As DataSet = ds.GetChanges()
If dsChanged Is Nothing Then
MsgBox("no changed records")
Exit Sub
End If
Try
daGeninfo.Update(dsChanged.Tables("MyTable"))
Catch ex As OleDbException
MsgBox(ex.ToString)
Catch ex As InvalidOperationException
MsgBox(ex.ToString)
Catch ex As DBConcurrencyException
MsgBox("concurrency exception - another user has modified this record" &
vbCr & vbLf & ex.ToString)
ds.RejectChanges() 'not really necessary, cause I'm going to clear and
refresh the data, but I wanted to show it
ds.tables.item("MyFile").clear 'clear the dataset of old data
da.fill(ds.tables.item("MyFile")) 'refresh it to reflect the new,
updated data
Exit Sub
End Try

One thing deserves explanation, and that is the DBConcurrencyException. If
0 records are updated as a result of the update method then the
DBConcurrencyException event is fired. This doesn't necessarily mean that
there was an actual concurrency violation, it simply means that there were 0
records updated, and that it is MOST LIKELY because of a concurrency
violation. Since you must determine what constitutes a concurrency
violation in your own code (VB does not do it for you.. it can help you by
possibly generating an SQL Update command, but it's still just your code)
you must code your SQL Update command not to update a record if you believe
a concurrency violation has occured.

This is most often done one of two ways, both of which are part of your SQL
Update stmt. The first is to compare every field to it's original value and
only update the record if all the fields on disk match the original values
(stored in the dataset, remember?) like this:

sqlUpdateCmd = "update MyFile set mydatafield=? where mykeyfield= ? and
mydatafield=?"
sqlUpdateCmd.Parameters.Add("@mydatafield", OleDbType.VarChar, 13,
"mydatafield")
sqlUpdateCmd.Parameters.Add("@mykeyfield", OleDbType.VarChar, 13,
"mykeyfield")
sqlUpdateCmd.Parameters.Add("@mydatafieldorig", OleDbType.VarChar, 13,
"mydatafield").SourceVersion = DataRowVersion.Original

This is the type of UPDATE command the SQLCommandBuilder or
OledbCommandBuilder class will generate for you, but it can get funky and
doesn't work well all the time.

The second, I believe easier, method is to have a timestamp or counter as
a field in each datatable. Everytime you update a record you update the
timestamp or counter. When specifying the UPDATE command for the
dataadapters update method you include a where clause that compares the
timestamp (or counter) to its original value and only update if they are
equal. The bottom line is that if the values are NOT equal it means that
somebody changed the record while you had it up on your screen, and
therefore, the update command will have updated 0 records (cause it failed
the Where clause test) and it will fire the DBConcurrencyException. At this
point you might want to refresh your data.

I tried to present a full picture of one way to update a datatable, it is
not the only way. The info is accurate to the best of my knowledge and if
anybody has any corrections or additions, feel free to comment (comments
are ALWAYS welcome!).

Hope this helps somebody having trouble figuring it all out...

John

 

[JohnR]

oops, one more thing I forgot.

after doing the dataAdapter.Update method, if it's successful you might want
to do a:

ds.acceptchanges()

this will reset all the RowState properties for the rows back to "unchanged"

John

 

[Cor Ligthert [MVP]]

John,

after doing the dataAdapter.Update method, if it's successful you might
want to do a:

ds.acceptchanges()

This is absolute needed in the way you describe it with the copy of the
dataset that holds the changes (I hope you don't force me to check all your
text, it seems quiet complete).

If the original dataset is updated than this ds.acceptchanges is not needed
because in the update is a step build in that does an acceptchanges in the
supported dataset.

Nice written by the way.

Cor

 

[Guest]

Thanks for Cor's solution and JohnR's detailed writeup. I was having a
similar problem, and the notes in this thread helped me resolve my issue.
However, I still don't completely understand what is going on as I've noticed
some confusing things.

In particular, before adding the EndCurrentEdit statement, I could see
(through debugger watches) that the dataset fields *were* being updated and I
had both Current and Proposed versions of my record. OTOH, the RowState
property was Unchanged, so there were no database rows updated with my Update
statement. After adding the EndCurrentEdit statement, the RowState would
become Modified and the Update command would change the database.

Incidentally, I did not see an "EndCurrentEdit" statement in the "update"
examples that I've found, including ones in Balena's book and the one on
Visual Studio's page on the DataRowVersion Enumeration. I can see why this is
one of the most asked question in the newsgroup by us newbies.

 

[JohnR]

Hi Timbobd,

I really can't speak for why you see changes in the dataset before doing
a endCurrentEdit, but you should be aware that a number of controls that
modify data will do an implicit 'endCurrentEdit' when your current row loses
focus, for example. Sometimes you can control when the implicit EndCurrent
Edit occurs, other times you can't. I agree that it is most confusing when
you have a method that sometimes is called 'behind the scenes' and at other
times, you have to call it explicitly. I've discovered most of the cases by
trial and error, and I just see what works.

John