Re: ADO and DELETE Statement

On Fri, 9 Jun 2006 18:26:37 +0100, "Stephen Howe"
<stephenPOINThoweATtns-globalPOINTcom> wrote:


It is up to the provider to signal to ADO that the SQL has failed in some
way. And if the provider raises no error, how will ADO know? What you can
get back from Action queries is the number of rows affected.
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?

Note also, it is not an error if DELETE failed to delete rows because the
WHERE clause meant no rows qualified.

Yeah - that would make sense but I know that the WHERE clause is
correct (and should delete one line).


You also said


It is basically reading a record, posting a purchase order
and if the order posted successfully, I delete the record. So if it is
not deleted (for whatever reason), the purchase receipt is posted
twice.


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), the interface will now pick it up again and it will
assume that this was an over-shipment and increase the quantity on the
PO in Navision, even though we have not physically received the items.

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.

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

The worst part is, it works ALMOST always. 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.
.

Relevant Pages

  • 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: ADO and DELETE Statement
    ... 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: "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: 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: 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)