Beginning question for Access OLEDBUpdate command

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I used the wizard in VB.NET 2003 to generate a data adapter and it created
the following update command. It seems like the WHERE clause would never
evaluate to true? Am I reading this wrong?

The member_ID is the key for the table but it looks like the 2 statements
checking the address_1 and address_2 values would only be true if no changes
were made.

Me.OleDbUpdateCommand1.CommandText = "UPDATE Member_Listing SET address_1 =
?, address_2 = ? WHERE (member_ID = ?) AND (address_1 = ? OR ? IS NULL AND
address_1 IS NULL) AND (address_2 = ? OR ? IS NULL AND address_2 IS NULL)

Thanks for your help.

Dave
 
Dave,

With what wizard did you made this, I miss the select command that is in my
idea standard in the generated dataadapter commands.

Cor
 
Cor,

After setting up a connection, I added a data adapter from the Data tab of
the Toolbox and a wizard started.

Dave
 
Close, but no cigar.

If the Jet OleDb handled named parameters a'la Sql Server then the generated
statement would look like this:

Me.OleDbUpdateCommand1.CommandText = "UPDATE Member_Listing SET address_1 =
@address_1, address_2 = @address_2 WHERE (member_ID = @Original_member_ID)
AND (address_1 = @Original_address1 OR @address_1 IS NULL AND address_1 IS
NULL) AND (address_2 = @Original_address_2 OR @address_2 IS NULL AND
address_2 IS NULL)

Because it doesn't, you have to consider which parameter is satisfying which
'?'.

The condition of the WHERE clause that deals with address_1 is making sure
that the row in the database is in the same state as when you populated your
dataset object and likewise for address_2. If it is not in the same state
then it means that the database row has been modified since you read it and
the UPDATE will fail. This is a technique called 'optimistic concurrency'
which means that rows are not locked for editing and can be modified by
other users or processes at any time and it is used to avoid new data being
overwritten.
 
Hi Dave,

First of all, I'd like to confirm my understanding of your issue.
According to your description, I understand that you have a concern with
update command text.
If I misunderstood anything here, please don't hesitate to correct me.

The where case "WHERE (member_ID = ?) AND (address_1 = ? OR ? IS NULL AND
address_1 IS NULL) AND (address_2 = ? OR ? IS NULL AND address_2 IS NULL"
is used for concurrency issues.

Because DataAdapter is used for DataGrid or other DataBind Control.
DataGrid will have two versions for each parameter. The original version is
the value returned from database and new version is the value which is used
for update. When DataGrid call DataAdapter.update method. The value of the
original version will be used in the where case ("WHERE (member_ID = ?) AND
(address_1 = ? OR ? IS NULL AND address_1 IS NULL) AND (address_2 = ? OR ?
IS NULL AND address_2 IS NULL)") and the value of new version will be used
in the update statement (UPDATE Member_Listing SET address_1 = ?, address_2
= ?)
This is the reason why it seems like the where clause would only be true if
no changes were made.

e.g: If there is someone who has updated this row before you want to update
it, the where case will be false and your update command will not be
executed successfully.

If there is anything unclear, please feel free to contact me.
Sincerely,
WenYuan
 
Back
Top