Delete Error With Delphi and Access Table

[JDS2018 1]

Hi 

i am using MS Access Database With Delphi RAD Studio 10.1 and i am trying to Delete All the Records Between 2 Days with following SQL

 

  StartDate:= '';
  EndDate:= '';
  StartDate:=FormatDateTime('m/d/yyyy',DStartDate.Date);
  EndDate:=FormatDateTime('m/d/yyyy',DEndDate.Date);

 

Query.SQL.Add('Delete * From Ledger');

Query.SQL.Add('Where  LedgerDate between '''+ StartDate +''' And'''+ EndDate+'');

 

but when i run i am getting error please check the attach image

 

if any one can tell me what is the issue , relay appreciate. thanks

Edited December 23, 2018 by JDS2018

[Guest]

You forgot a space after And.

[ByteJuggler 45]

To add to Dany's answer, you should preferably avoid dynamic SQL generation like above.  There's a bunch of reasons for this, including SQL injection attacks, and brittleness (which is to say it's prone to breakage and hard to test) and being slower as it makes it makes it hard for the database to optimise for the general case (as it will all else being equally have to parse and prepare an execution plan for each parameter combination presented to it).  Instead use parameterized queries where possible or (for suitable back-end databases) stored procedures. 

[dummzeuch 1505]

Isn't the "*" wrong in a delete query?

Delete from <table> where <condition>

[ByteJuggler 45]

6 minutes ago, dummzeuch said:

Isn't the "*" wrong in a delete query?

Delete from <table> where <condition>

Yep, that's also going to be wrong! Missed that! 🙂

[JDS2018 1]

7 hours ago, Dany Marmur said:

You forgot a space after And.

This is the code but still same error

 

Query.SQL.Add('Delete  From Ledger');
 Query.SQL.Add('Where LedgerDate between  '''+StartDate+ '''  AND  '''+EndDate+'''');

[dummzeuch 1505]

OK, that probably leaves only the date format.

[Richard_Stevens 2]

From memory, the Access date format for queries is quite unusual - found this in my legacy code library which may point in right direction?

 

function TpdSQLsearch.GetDateQueryString(AFieldName: string;
  ADate: TDateTime; ADateSearchType: TpdDateSearchType): string;
begin
{$IFDEF Access}
  if ADateSearchType = dtsAfter then
    Result := AFieldName + ' >=  #' + DateToStr(ADate) + '#';
  if ADateSearchType = dtsBefore then
    Result := AFieldName + ' <=  #' + DateToStr(ADate) + '#';
{$ELSE}
  if ADateSearchType = dtsAfter then
    Result := AFieldName + ' >= ' + QuotedStr(FormatDateTime('dd-mmm-yyyy', ADate));
  if ADateSearchType = dtsBefore then
    Result := AFieldName + ' <= ' + QuotedStr(FormatDateTime('dd-mmm-yyyy', ADate));
{$ENDIF}
end;

 

[Richard_Stevens 2]

Also not 100% sure Access supports BETWEEN? So you may have to do:

 

where Somedate >= A AND SomeDate <= B

Edited December 24, 2018 by Richard_Stevens

[zinpub 0]

On 12/24/2018 at 7:50 AM, JDS2018 said:

This is the code but still same error

 

Query.SQL.Add('Delete  From Ledger');
 Query.SQL.Add('Where LedgerDate between  '''+StartDate+ '''  AND  '''+EndDate+'''');

Is there space before "Where" or after "Ledger" ?  Try print in one string ....

 

const

  sSql = "Delete from Ledger where LedgerDate between %s and %s";

 

Query.Sql.Text:= Format(sSql, ['01.01.2019', '01.01.2019']);

 

 

[zinpub 0]

Much better use ParamByName

[zinpub 0]

just saw !

Your SQL is 'ID Delete from Ledger ..... '   🙂