Jump to content
JDS2018

Delete Error With Delphi and Access Table

Recommended Posts

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

error.PNG

Edited by JDS2018

Share this post


Link to post

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. 

Share this post


Link to post
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! 🙂

Share this post


Link to post
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+'''');

Share this post


Link to post

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;

 

Share this post


Link to post

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

 

where Somedate >= A AND SomeDate <= B

Edited by Richard_Stevens

Share this post


Link to post
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']);

 

 

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×