Deiete in TSQLQuery is ignored

Discussion:

(too old to reply)

Mikael

2008-02-08 10:01:59 UTC

Delphi 2006 and Interbase 7.5.1

I can't understand why this command (qryReset.ExecSQL(False);) don't delete
the rows?

Mikael

qryReset.Close;
qryReset.sql.text:='Delete from ESVInvoiceDay where aDay between ' +
quotedStr(POTStart) + ' and ' + quotedStr(POTStart);
WriteReport('DoReset: Executing = ' + Trim(qryReset.sql.text),1,4);
nrRowsAffected:=qryReset.ExecSQL(False);
WriteReport('DoReset: ' + IntToStr(nrRowsAffected) + ' rows
deleted',1,4);

2008-02-08 10:57:18: DoReset: StartTransaction
2008-02-08 10:57:18: DoReset: Executing = Delete from ESVInvoiceDay
where aDay between '2008-02-08' and '2008-02-08'
2008-02-08 10:57:18: DoReset: 0 rows deleted
2008-02-08 10:57:18: DoReset: Commit

Paul Hughes

2008-02-08 10:12:05 UTC

Permalink

Hi,

I would be wary of using the quotedStr(POTStart) and would suggest using
query parameters to set your date range. Depending on the field type, your
query may not be finding any records to actually delete.

e.g. If there is any time part to those fields (timestamps) your query will
exclude those records.

Have you confirmed that running the same query directly (via ISQL for
example) is finding any records to delete?

Regards, Paul.

Post by Mikael
Delphi 2006 and Interbase 7.5.1
I can't understand why this command (qryReset.ExecSQL(False);) don't
delete the rows?
Mikael
qryReset.Close;
qryReset.sql.text:='Delete from ESVInvoiceDay where aDay between ' +
quotedStr(POTStart) + ' and ' + quotedStr(POTStart);
WriteReport('DoReset: Executing = ' + Trim(qryReset.sql.text),1,4);
nrRowsAffected:=qryReset.ExecSQL(False);
WriteReport('DoReset: ' + IntToStr(nrRowsAffected) + ' rows
deleted',1,4);
2008-02-08 10:57:18: DoReset: StartTransaction
2008-02-08 10:57:18: DoReset: Executing = Delete from ESVInvoiceDay
where aDay between '2008-02-08' and '2008-02-08'
2008-02-08 10:57:18: DoReset: 0 rows deleted
2008-02-08 10:57:18: DoReset: Commit

yannis

2008-02-08 10:13:44 UTC

Permalink

Post by Mikael
Delphi 2006 and Interbase 7.5.1
I can't understand why this command (qryReset.ExecSQL(False);) don't delete
the rows?
Mikael
qryReset.Close;
qryReset.sql.text:='Delete from ESVInvoiceDay where aDay between ' +
quotedStr(POTStart) + ' and ' + quotedStr(POTStart);
WriteReport('DoReset: Executing = ' + Trim(qryReset.sql.text),1,4);
nrRowsAffected:=qryReset.ExecSQL(False);
WriteReport('DoReset: ' + IntToStr(nrRowsAffected) + ' rows
deleted',1,4);
2008-02-08 10:57:18: DoReset: StartTransaction
2008-02-08 10:57:18: DoReset: Executing = Delete from ESVInvoiceDay where
aDay between '2008-02-08' and '2008-02-08'
2008-02-08 10:57:18: DoReset: 0 rows deleted
2008-02-08 10:57:18: DoReset: Commit

From the log it seems that it is executed correctly and deleted 0 rows.
What do you get if you execute the following statement

Select
Count(0)
from
ESVInvoiceDay
where
aDay between '2008-02-08' and '2008-02-08'

regards
Yannis.

--
You talk a great deal about building a better world for your children,
but when you are young you can no more envision a world inherited by
your children than you can conceive of dying. The society you mold, you
mold for yourself.
----Russell Baker-------

albert drent

2008-02-08 10:29:43 UTC

Permalink

Post by Mikael
Delphi 2006 and Interbase 7.5.1
I can't understand why this command (qryReset.ExecSQL(False);) don't delete
the rows?
Mikael
qryReset.Close;
qryReset.sql.text:='Delete from ESVInvoiceDay where aDay between ' +
quotedStr(POTStart) + ' and ' + quotedStr(POTStart);
WriteReport('DoReset: Executing = ' + Trim(qryReset.sql.text),1,4);
nrRowsAffected:=qryReset.ExecSQL(False);
WriteReport('DoReset: ' + IntToStr(nrRowsAffected) + ' rows
deleted',1,4);
2008-02-08 10:57:18: DoReset: StartTransaction
2008-02-08 10:57:18: DoReset: Executing = Delete from ESVInvoiceDay
where aDay between '2008-02-08' and '2008-02-08'
2008-02-08 10:57:18: DoReset: 0 rows deleted
2008-02-08 10:57:18: DoReset: Commit

Because dayis in between is 0?

Mikael

2008-02-08 12:00:21 UTC

Permalink

OPS.......

You have to open the connection first. (Strange but no error message was
detected...)

sorry for trouble you.

Mikael

Jan Doggen

2008-02-12 17:18:22 UTC

Permalink

Is it a date or datetime field?
There may be no records between 2008-02-08 00:00:00 and 2008-02-08 00:00:00
;-)

Bye
Jan

Jan Doggen

2008-02-17 12:54:38 UTC

Permalink

Mikael,
did you ever find out the reason?
Then please post it here so that others can benefit from it.

Bye
Jan