Help with weird ADO problem

[DHess]

Using vb.net, ado (obviously) newest mdac and 1.1 framework. Here is my
problem:
I have a table that gets a record inserted into it, with a flag set to the
number 1. Potentially, there could be a record with the flag set to 0 as
well. What I do in my codebehind is first, delete the "0" record with a
call to executenonquery. Then, I "update" the the one record and change
it's flag to "0". What happens is randomly, sometimes, it works right and
sometimes it ends up deleting BOTH records. It is hard to reproduce the
error. What seems like is happening is that the update is getting called
FIRST, so BOTH records are getting set to "0", then it calls the delete
SECOND. It doesn't ALWAYS do this, but does sometimes. I tried making a
transaction and then forcing it to commit after the delete, but that
didn't seem to work. Pro grammatically, the delete is always called
first.
Does anyone have any suggestions? I didn't write this code, and am
only trying to bugfix it. This routine appears TONS of times in the code,
so I hope I don't have to re-write it.

Don

 

[Miha Markic [MVP C#]]

Can you show us some code?

 

[DHess]

Can you show us some code?

Yes. Here it is:

If rbApprove.checked = True Then
strSQL = "DELETE FROM MyTable Where MyID = " & key & " " _
& "AND EDIT = 0"

cmdUpdateApproval.CommandText = strSQL
cmdUpdateApproval.ExecuteNonQuery()

strSQL = "UPDATE MyTable SET EDIT = 0, VERIFICATION_ID = 1 WHERE
MyID = " & key

cmdUpdateApproval.CommandText = strSQL
cmdUpdateApproval.ExecuteNonQuery()
End If

Key has been determined to always be the proper value through single-step
debugging. And the command object has been properly defined. Note two
things that might(?) be important:

1) There isn't always a record to delete. Procedure fails randomly in
both cases (ie there is and isn't a record to delete first) and
2) MyID is not the primary key, niether is the edit flag

Don

 

[William \(Bill\) Vaughn]

Did you try submitting both operations in the same line of SQL? I would
build a SP that did both of these and accepted parameters.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[DHess]

No I haven't, but I can give it a try.
Don