Re: ADO and DELETE Statement

Tech-Archive recommends: Fix windows errors by optimizing your registry

That is one of the parameters of the Command object.

That is exactly what I would need to get but it seems that in case of
the DELETE operation, this is not provided. I at least can't find it.
Which property on the ADOconnection object would that be?

Did you read what I said? Look at the first line quoted above.
I said Command object _NOT_ Connection object.
It is one of the parameters of Command.Execute() method.
You supply a variable that is a 4-byte integer and providing what is
executed does not have "SET NOCOUNT ON" in force, it will be filled in on
completion.

Have a look here
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthcmdexecute.asp

That last part seems wrong. Why not record on the database that you
posted
successfully?
The receipt won't be sent twice as you check for its existence.
You need to get to the bottom of why a small % of deletes fail.

It's unfortunately a little more complex. There is a third-party
receiving software which allows warehouse employeed to scan item
barcodes during receiving. Vendors sometimes overship in which case
the employee actually scans more than what was on the PO. This is a
valid case and I am picking up those additional scans from SQL Server
and I am increasing the quantity on the PO and post the receipt. After
the PO is posted, I delete the line in SQL.

Now, 5 seconds later, my interface polls the SQL Server again to find
new lines that were scanned since the last iteration. If the previous
line has not been deleted on SQL Server (even though I had sent my
DELETE operation)...

What happens in the DELETE is successful but the rows have been since
re-INSERTed?
That then leads you to falsely believe that the DELETE failed.
You said "polls" SQL Server. Sounds like there are other processes running
on this database.
Is that true? Only if your software is the _ONLY_logged-on process will I
beleive you can scratch multi-process synchronisation from categories of
possible bugs.

So yes, instead of deleting it, I could add a field and UPDATE it
after the PO was received. But if the DELETE sometimes fails (like
once or twice out of 10,000 records), the UPDATE could fail as well.

Something very very very very very very very very very very very very very
very very very very very wrong if DELETE and UPDATE can fail. Time for you
to get to the bottom of this.

I guess I could first UPDATE the record and then DELETE it. But that's
just a work-around in my opinion.

Yes right. Or SELECT and then DELETE if present

The worst part is, it works ALMOST always.

Something very very very very very very very very very very very very very
very very very very very wrong
Time for you to get to the bottom of this.
This smells like a bug.

So I have no way of
debugging it. What I do know is that I am reading the record which
contains a unique ID. It is this unique ID that I am using in the
WHERE clause to DELETE the record. There is no way it would not match
because I just read it like 2 seconds ago. So the record is there and
should be deleted.
The whole thing is weird.

What kind of unique ID?
Please mention the SQL Server type

Stephen Howe



.

Relevant Pages

  • Re: "On Error Resume Next" in SQL Server
    ... formatSearchText can output stuff to cause it to fail, ... Pro SQL Server 2000 Database Design - ... > DECLARE TestCursor CURSOR FOR /*WHATEVER*/ ... >>>I am executing a stored procedure that uses a cursor to ...
    (microsoft.public.sqlserver.programming)
  • Re: ADO and DELETE Statement
    ... The receipt won't be sent twice as you check for its existence. ... You need to get to the bottom of why a small % of deletes fail. ... receiving software which allows warehouse employeed to scan item ... valid case and I am picking up those additional scans from SQL Server ...
    (microsoft.public.data.ado)
  • Re: Different results in SQL and Access
    ... I misread 20 as 02 in the date, but even so I don't see how 11:30 AM would fail when the specified time range is: ... I still wonder whether eliminating the first second of the day accounts for the difference. ... number of days since the seed date and the decimal representing the time ... SQL Server stores datetimes as paired integers, ...
    (microsoft.public.access.queries)
  • Re: ADO and DELETE Statement
    ... The receipt won't be sent twice as you check for its existence. ... You need to get to the bottom of why a small % of deletes fail. ... receiving software which allows warehouse employeed to scan item ... valid case and I am picking up those additional scans from SQL Server ...
    (microsoft.public.data.ado)
  • Re: Unable to read local eventlog (reason: The event log file has
    ... I fails to see why agent would fail a job because of this unrelated happening. ... Tibor Karaszi, SQL Server MVP ... >> My guess is that the eventlog was recycled and Agent couldn't access the eventlog during that ...
    (microsoft.public.sqlserver.server)