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")).End CurrentEdit()
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")).Can celCurrentEdit( )
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.Updat e(dsChanged.Tab les("MyTable"))
Catch ex As OleDbException
MsgBox(ex.ToStr ing)
Catch ex As InvalidOperatio nException
MsgBox(ex.ToStr ing)
Catch ex As DBConcurrencyEx ception
MsgBox("concurr ency exception - another user has modified this record" &
vbCr & vbLf & ex.ToString)
ds.RejectChange s() 'not really necessary, cause I'm going to clear and
refresh the data, but I wanted to show it
ds.tables.item( "MyFile").c lear 'clear the dataset of old data
da.fill(ds.tabl es.item("MyFile ")) 'refresh it to reflect the new,
updated data
Exit Sub
End Try
One thing deserves explanation, and that is the DBConcurrencyEx ception. If
0 records are updated as a result of the update method then the
DBConcurrencyEx ception 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.Pa rameters.Add("@ mydatafield", OleDbType.VarCh ar, 13,
"mydatafiel d")
sqlUpdateCmd.Pa rameters.Add("@ mykeyfield", OleDbType.VarCh ar, 13,
"mykeyfield ")
sqlUpdateCmd.Pa rameters.Add("@ mydatafieldorig ", OleDbType.VarCh ar, 13,
"mydatafield"). SourceVersion = DataRowVersion. Original
This is the type of UPDATE command the SQLCommandBuild er or
OledbCommandBui lder 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 DBConcurrencyEx ception. 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
"Cor Ligthert [MVP]" <no************ @planet.nl> wrote in message
news:Og******** *****@TK2MSFTNG P09.phx.gbl...
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)).E ndCurrentEdit()
I hope this helps,
Cor
