Syntax error in query? 1

[G4U]

This code works great in Paradox using the BDE, but returns an Error using quering an Access database:

for i:= 0 to ListView1.Items.Count-1 do
With ListView1 do
begin
Query1.SQL.Clear;
Query1.SQL.Add('UPDATE "'+Table2.TableName+
'" SET OVERTIME ="'+Items.SubItems[2]+'"'+
',SIXTOUR ="'+Items.SubItems[3]+'"'+
',SCHED ="'+Items.SubItems[1]+'"'+
',A_OT ="'+Items.SubItems[4]+'"'+
',REFUSED ="'+Items.SubItems[5]+'"');
Form1.Query1.SQL.Add('WHERE FULLNAME ="'+
Items.Caption+'" AND DATE_IS ="'+Items.SubItems[0]+'"');
Query1.ExecSQL;
end;

 

[Zathras]

Generally dates in Access should be delimited by the pound sign ("#"), so try this:
[tt]
Items.Caption+'" AND DATE_IS =#'+Items.SubItems[0]+'#');
[/tt]
If that doesn't work, try posting on the Access forum to get someone who knows that application.

 

[hilfy]

There is a better way to do this than changing the SQL for every item you want. If you use paramters, you can just pass in the parameter values and re-execute the SQL for each iteration.

With your SQL, since the table name is variable, you might have to change the first line of the SQL prior to entering the for loop, but your SQL would look like this:

UPDATE TableName        
SET OVERTIME = :Overtime,
    SIXTOUR = :SixTour,
    SCHED = :Sched
    A_OT = :A_OT
    REFUSED = :Refused
WHERE FULLNAME = :FullName
  AND DATE_IS = :Date_Is

Delphi will automatically parse the parameters into the Params property of the query. You set the ParamType (ptInput) and DataType (ftString, ftDate, etc.) of the parameters in the IDE (this can also be done in code.) Your for loop will then look like this (I'm guessing at datatypes...):

 for i:= 0 to ListView1.Items.Count-1 do
   With ListView1 do
   begin
     Query1.ParamByName('Overtime').AsInteger := 
          Items[i].SubItems[2];
     Query1.ParamByName('SixTour').AsInteger := 
          Items[i].SubItems[3];
     Query1.ParamByName('Sched').AsString := 
          Items[i].SubItems[1];
     Query1.ParamByName('A_OT').AsInteger := 
          Items[i].SubItems[4];
     Query1.ParamByName('SixTour').AsString := 
          Items[i].SubItems[5];
     Query1.ParamByName('FullName').AsInteger := 
          Items[i].Caption;
     Query1.ParamByName('DateIs').AsDate := 
          Items[i].SubItems[0];
     Query1.ExecSQL;
   end;

When you use parameters like this, you don't have to worry about formatting non-string values into whatever format your database is looking for. This facilitates creating code that can access different databases without making a lot of code changes.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do. --Me

 

[G4U]

I've tried the code posted, however I still get a "Syntax Error" from the Access database. So let me explain some details:
SIXTOUR and OVERTIME: are both Floating point values.
DATE_IS is of ftDate. The rest of the fields are simple strings e.g., (ftString)...These datatypes are the same in both my Paradox and Access Tables.

As you can see, I'M not using a Database grid to display values to the user, but a TListView component instead. I am trying to CONVERT from Paradox to Access using (Of course) ADO! Most of the program is working, except the QUERY.

--------------- ZCode! ------------------------------------

for i:= 0 to ListView1.Items.Count-1 do
With ListView1 do
begin
Query1.SQL.Clear;
Query1.SQL.Add('UPDATE "'+Table2.TableName+
'" SET OVERTIME ="'+Items.SubItems[2]+'"'+
',SIXTOUR ="'+Items.SubItems[3]+'"'+
',SCHED ="'+Items.SubItems[1]+'"'+
',A_OT ="'+Items.SubItems[4]+'"'+
',REFUSED ="'+Items.SubItems[5]+'"');
Query1.SQL.Add('WHERE FULLNAME ="'+
Items.Caption+'" AND DATE_IS ="'+Items.SubItems[0]+'"');
Query1.ExecSQL;
end;
-----------------------------------------------------------
Table2 is a variable because the program only uses tables by the selected year i.e.(T2005.DB). Thus, when a user selects a year to which there is NO table, the program creates this table then fills it with a years worth of default values (Which is a one time deal).

I'M not new to programming but am to Database programming. I would like to rid the program of the BDE, however, the speed (To me) is alot better then Access.

 

[G4U]

Ok I figured out the main problem. This (") and that ([]). I must give credit to:hilfy on the Access date format using the(#)sign in a Date field. Upon doing some reading, I totally forgot about the square brackets required in Access on field-names which contain special characters i.e A_OT becomes [A_OT] and DATE_IS becomes [DATE_IS]! The last part was the extra (") at the beginning of the SQL Statement itself:

Query1.SQL.Add('UPDATE "'+Table2.TableName, Should read:
Query1.SQL.Add('UPDATE '+Table2.TableName.

Thanks to all!

 

[lespaul]

another helpful hint:

Instead of using:

',REFUSED ="'+Items[i].SubItems[5]+'"');

I find it much easier to use:

',REFUSED ='+ QuotedStr(Items[i].SubItems[5]));

Don't have to keep checking out all the 's and "s to make sure they all are right!!


Leslie

 

[hilfy]

Actually, Zathras pointed out the date format issue.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do. --Me