DataAdapter, sedond update

[Guest]

I keep getting a concurrency exception the second time I make a change and
attempt to update a dataadapter. It appears this is by design, so there must
be something I can do to avoid it.

Example:

I have a dataadapter that contains one table with one row. I change the
value of the 'FisrtName' column in that row from Jack to John. I call
..update on the dataadapter it goes through fine. Now I change that same
column in that same row from John to Peter. Update fails. I'ts because the
'origianl' value is still getting passed as the parameter value in the update
command. In other words, it is looking for a row where firstname = 'Jack'
and finds none.

How do you get the updated values to become the current values for
subsequent updates without making an additional run back to the database to
refresh the whole dataset?

Thanks!

 

[Guest]

Zorpiedoman,

Datasets (and datatables and datarows) have an AcceptChanges method for
making the current values the original values.

Kerry Moorman

 

[Guest]

Thanks, but I've already been down that road. The DataSet is behaving
'properly' in that the row shows a unchanged after the first update, then
shows as modified just prior to the second update. The problem is that the
parameter for the updatecommand (which was built using the commandbuilder
method) still holds the ORIGINAL value (Jack, in my example) when it tries to
do the update.

-js

 

[Guest]

Zorpiedoman,

Are you saying that you are doing this:

1. Change the value of FirstName from Jack to John.
2. Call Update on the dataadapter.
3. Call AcceptChanges on the dataadapter.
4. Change the value of FirstName from John to Peter.
5. Call Update on the dataadapter.

And you get a concurrency violation?

Kerry Moorman

 

[Guest]

Almost. There is no acceptchanges on the dataadapter object, but I HAVE
called it on the DataSet that the data adapter fills. It does not matter,
however.

The root of the problem is this: The Update Command, which was built by the
command builder, has a gazillion parameters. It actually checks to find the
exact row it had at the time of filling in order to update.

Let's say my table had only two columns, ID and FirstName. When I call
mydataadapter.fill(mydataset) the data set is filled. The update command,
built by the command builder, looks something like this:

"Update SomeTable Set FirstName = @p1 Where (ID is null or ID = @p2) and
(FirstName is null or Firstname = @p3)"

After the fill, the parameter values are : @p2 = 12345, @p3 = 'Jack'

I change FirstName from Jack to John and call the update on the dataadapter.

No problem. Database is updated, Datatable is updated, marked unchanged,
etc. All is well.

Then I change FirstName from 'John' to 'Peter' When I call .update on the
dataadapter, the value of the parameters have NOT changed, so it is the same
as running this statement:

Update SomeTable Set FirstName = 'Peter' where (Id Is Null or Id = 12345)
and (FirstName is null or FirstName = 'Jack')

And herein lies the rub: There is no longer a row in the table where id =
12345 and FirstName = 'Jack' becuase it has been changed to 'John'

So what I REALLY need to know is how to get those parameters to change to
the current values prior to calling the .update on the dataadapter.

Does anyone know how THIS can be accomplished without a ton of manual
maniulatoin?

 

[Guest]

Zorpiedoman,

Here is an example that I just put together. It allows me to update the
"Name" column of the first row (row 0) in a datatable, using a dataadapter, a
commandbuilder, a dataset and a connection. I never get a concurrency
violation and I never need to call AcceptChanges.

Create a Windows Forms project. Place an Update button on the form.

In the form's declarations section:

Dim cn As New
OleDb.OleDbConnection(("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=CourseInfo.mdb;"))
Dim da As New OleDb.OleDbDataAdapter("Select * From Students", cn)
Dim cb As New OleDb.OleDbCommandBuilder(da)
Dim ds As New DataSet

In the form's Load event:

'Fill the dataset with data from the database
cn.Open()
da.Fill(ds, "Students")
cn.Close()

In the Update button's click event:

'Display the current value of the "Name" column from row 0 of the
Students datatable
MsgBox(ds.Tables("Students").Rows(0)("Name"))

'Change the value in the "Name" column of row 0 of the Students
datatable
ds.Tables("Students").Rows(0)("Name") = InputBox("New Student Name?")

'Update the database with changes to the Students datatable
cn.Open()
da.Update(ds, "Students")
cn.Close()

I can repeatedly click the Update button, see the current value in the
"Name" column and change the name.

Is this similar to what you are attempting, or am I misunderstanding you?

Kerry Moorman

 

[Guest]

Kerry -

Wow. First of all, thank you for working on this so intently. I owe you
one.

The code you lay out here is basically the same. The main differences are
you are using an oleDB data adapter connecting to an access database, I'm
using an MsSql data adapter connecting to SQL server.

I also don't see where you are setting the updatecommand statement for the
data adapter.

Can you do me a favor, if you have not deleted this code yet: can you put a
break point at the da.Update(ds, "Students") line and show me what the
da.updatecommand.commandtext returns?

Perhaps it is different for an oleDBadapter than it is for a MsSql DA.

Again, thanks!

-zorpie

 

[Guest]

Zorpie,

I modified my code to use SQLClient objects and an SQL Server database. It
still works fine and lets me update the row multiple times.

This line of code uses the commandbuilder to set up the dataadapter's
UpdateCommand:

Dim cb As New OleDb.OleDbCommandBuilder(da)

I don't have my program in front of me at the moment, so I can't try setting
break points, etc.

How are you using the commandbuilder to set up your dataadapter's
UpdateCommand?

Could you post your code that is not working?

Kerry Moorman

 

[Guest]

I figured it out!

My Select statement included a calculated column. In my Users table, I have
a column FullName that concatonates the FirstName and LastName fields. As
long as my select statement was "Select ID, FirstName From Users" it worked
fine, just like your code. As soon as I changed the Select statement to
"Select ID, FirstName, FullName From Users" it fails.

That's gotta be because in the database, the FullName field (which was
originally 'Jack Smith') was now 'John Smith' but locally, the value of
FullName was still 'Jack Smith' becuase this won't get updated until you FILL
the DataSet again.

Horay!!!!! Thanks so much for your help. Like I said, I owe you one. Feel
free to contact me directly next time you have a stumper.

-zorpie