Re: Ado.net Concurrency exceptions

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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)

--
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Ed Warren" <eowarren@xxxxxxxxxxxxxxx> wrote in message
news:%23Po7nazzFHA.3312@xxxxxxxxxxxxxxxxxxxxxxx
> 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
>
>
> "W.G. Ryan - MVP" <WilliamRyan@xxxxxxxxxxxxxxxx> wrote in message
> news:%23XOU78yzFHA.3780@xxxxxxxxxxxxxxxxxxxxxxx
>> Ed - comments inline
>> "Ed Warren" <eowarren@xxxxxxxxxxxxxxx> wrote in message
>> news:%23U%23D5uyzFHA.3588@xxxxxxxxxxxxxxxxxxxxxxx
>>> 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/
>>>
>>> MessageBox.Show("The update was successful!")
>>>
>>> Catch ex As Exception
>>>
>>> MessageBox.Show("Update Failed", ex.GetType().ToString())
>>>
>>> End Try
>>>
>>>
>>
>>
>
>


.

Relevant Pages

  • Re: Ado.net Concurrency exceptions
    ... test it and help isolate the problem. ... The Update command is going to be the issue, so if you would, post the ... no one else is making changes - if they are, ... rowstate for each row if you have it - otherwise you'll throw an exception. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Problems call DataAdapter.Update and Concurrency
    ... I use a command builder for update command as I want to make use of the ... as soon as the data gets changed it causes a concurrency violation. ... calling the .fill and avoiding another trip to the database. ... By default the DataAdapter will call AcceptChanges on each succesfull ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Problems call DataAdapter.Update and Concurrency
    ... the concurrency exception. ... I use a command builder for update command as I want to make use of ... without calling the .fill and avoiding another trip to the database. ... By default the DataAdapter will call AcceptChanges on each succesfull ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Ado.net Concurrency exceptions
    ... Just when I thought that after 45 years of working with Boolean Logic, ... >> Select Command: ... >> Removing the checkbox for optimistic concurrency fixed the problem (now I ... I'd change this just to test it and help isolate the problem. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: adding a new datarow and updating problem
    ... And the command objects are created by VStudio if that is what you mean. ... and it looks like the second update sees the first update as an concurrency ... >> dsClient.tbClientenRow drClient; ... >> If i am doing a second update on this dsClient with adding the following ...
    (microsoft.public.dotnet.framework.adonet)