473,320 Members | 2,146 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

modifying a textbox bound to datasource - rowstate always says UNCHANGED

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
Nov 21 '05 #1
6 13851
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
Nov 21 '05 #2
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")).EndCurre ntEdit()

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")).CancelCu rrentEdit()

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


"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:Og*************@TK2MSFTNGP09.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)).EndCurrentEdit()

I hope this helps,

Cor

Nov 21 '05 #3
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

"JohnR" <Jo******@hotmail.com> wrote in message
news:eJLIe.18$D4.1@trndny07...
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")).EndCurre ntEdit()

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")).CancelCu rrentEdit()

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


"Cor Ligthert [MVP]" <no************@planet.nl> wrote in message
news:Og*************@TK2MSFTNGP09.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)).EndCurrentEdit()

I hope this helps,

Cor


Nov 21 '05 #4
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
Nov 21 '05 #5
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. :-)
Nov 21 '05 #6
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

"timbobd" <ti*****@discussions.microsoft.com> wrote in message
news:A0**********************************@microsof t.com...
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. :-)

Nov 21 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
by: Paul Slavin | last post by:
I have a textbox bound to a dataview, when I update the text in the textbox no changes take place in the underlying dataset. Why is this?? any answers appreciated, as to due to the underlying...
2
by: certolnut | last post by:
Hi all I'm using the "Merge" method for datasets to append data from an external excel spreadsheet into a Sql Server Table The problem is that "Merge" method copies over the current RowState...
2
by: Phil Townsend | last post by:
I have a datagrid in a windows forms application. The datagrid gets its data via a web service. Users can modify rows in the datagrid then post their changes. However, when changes are made the...
3
by: matthew schouppe | last post by:
I have a datagrid with two columns, the first a normal bound column, the second is a template column created from a bound column. For the ItemTemplate of the Template Column, I removed the label...
6
by: timbobd | last post by:
I have a Windows form that displays a database table in a DataGrid. When you click on a row, the row's values get copied to bound TextBoxes below, and when the "Save" button is clicked the database...
1
by: AssOnFire | last post by:
Hello, I have bound a textbox to a CustomerName column of my DB, using the data binding property, and selected it from the DataTable. when i change the text in the texbBox, i try to update the...
5
by: Ken Varn | last post by:
I have just started using VS.NET 2005 after using VS.NET 2003. One of the things that I noticed is that in ASP.NET, the DataSource property for TextBoxes and other web controls is no longer...
4
by: sklett | last post by:
(I posted this in a databinding NG, but it's a VERY low traffic NG so I thought I would post here as well. I hope no one minds too much, if you do I'm sorry) I have a DGV that is bound to a...
12
by: cj | last post by:
When viewing a datatable in a datagridview one of the columns in it is a "note" field which can be quite long. I would like to have the note field of the currently selected row of the datagrid...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.