Ado.net Concurrency exceptions

[Ed Warren]

I'm trying to figure out how to use Ado.net with Visual Studio 2003.

I have a very, very simple setup.

Winforms:

Datagrid1 with a datasource set to datasetTest1

oledbconnection1 connected to an access database with one table
(appointments)

OleDbDataAdapter1 using OleDbConnection1

DataSetTest1 with one table (appointments) :: generated from
OleDbDataAdapter1

the application opens and fills the dataset (I can see the values in the
datagrid.

I edit the one of the values in the datagrid and that appears to work

When I run the code below to post back to the database I get an
OleDBConcurrency exception thrown (on the update command)

I have spent several hours trying to find out why and/or figure out how to
troubleshoot this.



Thanks

Ed Warren.

Try

OleDbDataAdapter1.Update(DataSetTest1)

DataSetTest1.AcceptChanges()

MessageBox.Show("The update was successful!")

Catch ex As Exception

MessageBox.Show("Update Failed", ex.GetType().ToString())

End Try

 

[W.G. Ryan - MVP]

Ed - comments inline

I'm trying to figure out how to use Ado.net with Visual Studio 2003.

I have a very, very simple setup.

Winforms:

Datagrid1 with a datasource set to datasetTest1

oledbconnection1 connected to an access database with one table
(appointments)

OleDbDataAdapter1 using OleDbConnection1

DataSetTest1 with one table (appointments) :: generated from
OleDbDataAdapter1

the application opens and fills the dataset (I can see the values in the
datagrid.

I edit the one of the values in the datagrid and that appears to work

When I run the code below to post back to the database I get an
OleDBConcurrency exception thrown (on the update command)

I have spent several hours trying to find out why and/or figure out how to
troubleshoot this.



Thanks

Ed Warren.

Try

---What do your update, insert and delete commands look like?
Are you using a CommandBuilder, the Data Adapter Configuration Wizard or
did you roll your own? This is going to be where the problem is coming in in
all likelihood. Also, have you isolated it so that you are sure no one else
(or no other program) is making changes that could be affecting this? Also,
just to help isolate things, on the Data Adapter configuration wizard, at
the end, you can specify the concurrency options. I'd change this just to
test it and help isolate the problem. If it 'fixes' the problem, then at
least you know exactly where the issue is (although how you handle
concurrency depends on a lot of factors and it may well be a doubleplusbad
to leave it this way).

The Update command is going to be the issue, so if you would, post the
code - that'll definitely help tracking it down. Also, I know I mentioned it
above, but make sure that you isolate things so that you're sure nothing or
no one else is making changes - if they are, then this is precisely the
behavior you want so there may be nothing to fix.

OleDbDataAdapter1.Update(DataSetTest1)

DataSetTest1.AcceptChanges()

-----When Update is called, the adapter loops through your table, row by row
and checks the rowstate. Then it fires the command that corresponds to the
rowstate for each row if you have it - otherwise you'll throw an exception.
After it calls that command, it calls AcceptChanges on each row, row by row
so this is unnecessary and while it may seem nitpicky to mention this - it
can be confusing to new developers and can potential cause behavior you
weren't expecting. For isntnace, if you called AcceptChanges immediately
before calling Update, then you'd never send the changes back to the
database/

 

[Christiaan van Bergen]

Hi Ed,

A concurrency problem is detected when an original row in your dataset is
not refound in the database.
Could it be that you accidently placed an AcceptChanges too many in your
code?

If so, the current rows will become the original rows, and with that they
will not match the database rows and cause a concurrency exception.

Also, the second line in your sample
DataSetTest1.AcceptChanges()
is surplus.....after an OleDbDataAdapter1.Update(..) the DataSet will have
its changes accepted.

HTH
Christiaan

 

[Ed Warren]

Thanks for your quick response, I think you have 'led me to a solution', but
one I still don't fully understand the why of!!

I'm the only user.
This is the only application connecting to the database.

Thanks for the note abut not having to call the *.acceptupdates (I got that
from a Microsoft sample).

I'm using the default select and update from the DataAdapter Configuration
Wizard (I'm don't know enough yet to be overly adventureous)
:>


Select Command:

SELECT appointmentID, length, start_date, start_time, subject
FROM APPOINTMENT

Update Command

UPDATE APPOINTMENT
SET appointmentID = ?, length = ?, start_date = ?, start_time =
?, subject = ?
WHERE (appointmentID = ?) AND (length = ?) AND (start_date = ?) AND
(start_time = ?) AND (subject = ? OR
? IS NULL AND subject IS NULL

Removing the checkbox for optimistic concurrency fixed the problem (now I
know there is a checkbox for concurrency!)

New Update Command

UPDATE APPOINTMENT
SET appointment_guid = ?, length = ?, start_date = ?,
start_time = ?, subject = ?
WHERE (appointment_guid = ?)

Now I'm really scratching my A--- aaaaaa ... (head).


So the issue has to be with the extra parameters added in the update command
to check the field values for concurrency.
I don't understand what the wizard is trying to do with the ("subject = ? OR
? is null and subject is NULL"). Note subject is the only field that allows
a NULL value all others are required fields.


Ed Warren

 

[Mark Ashton]

The reason for 'WHERE (subject = ? OR (? IS NULL AND subject IS NULL))' in
ANSI SQL, NULL != NULL. If writing just 'WHERE (subject = ?)' and the value
of ? was NULL, it would never evaluate to be true even if the value of
subject was NULL.

You can read about this in more detail at
http://www.sqlservercentral.com/columnists/mcoles/fourrulesfornulls.asp

If hand writing for sql server you could write this as 'WHERE (subject = @P1
OR (@P1 IS NULL AND subject IS NULL))' and just use a single named paramter.
But for Odbc & OleDb, each ? becomes its own parameter and you essentually
have to send the value twice.

In ADO.Net V2.0, we generate something like 'WHERE (subject = ? OR (?=1 AND
subject IS NULL))' translating the value of the second parameter to 1 or 0
depending on the value of the first parameter. That we the original value,
which could be a large string, isn't sent to the server twice. (For those
that care, this is why DbParameter.SourceColumnNullMapping exists)

 

[Ed Warren]

Just when I thought that after 45 years of working with Boolean Logic, I was
beginning to have a glimmer of understanding of 0&1, they kicked the Boolean
out ;>

Wonder if the 'three value' logic is also a complete algebra like Boolean?

Guess there is always something else to learn, very interesting, thanks for
the information

Ed Warren

 

[Ed Warren]

Thanks, your comments will help me track down why the error is being thrown.

Ed Warren.