I am creating a table (Rtime) and then trying to insert records in the table, but receiving the following error message:
'ElevateDB Error #700 An error was found in the statement at line 14 and column 1 (Expected expression but instead found))'.

Here is my  code:

with CatalogQuery do
     begin
     SQL.Text:='SELECT * FROM Information.Tables WHERE Name=''Period''';
     Open;
     if (RecordCount = 0) then
        begin
        Close;
        SQL.Clear;
        SQL.Add('CREATE TABLE "Period"');
        SQL.Add('(');
        SQL.Add('"PeriodID" INTEGER,');
        SQL.Add('"MonthDesc" VARCHAR(15),');
        SQL.Add('"DateFrom" DATE,');
        SQL.Add('"DateTo" DATE,');
        SQL.Add('CONSTRAINT "PeriodID" PRIMARY KEY ("PeriodID")');
        SQL.Add(')');
        SQL.Add('DESCRIPTION ''Contains Period information''');
        ExecSQL;
        end
     else
        Close;
     end;

with InitialiseQuery do
     begin
     SQL.Text := 'SELECT * FROM Period';
     Open;
     if (RecordCount = 0) then
        begin
        Close;
        SQL.Clear;
        SQL.Add('INSERT INTO "Period"');
        SQL.Add('(');
        SQL.Add('"PeriodID",');
        SQL.Add('"MonthDesc",');
        SQL.Add('"DateFrom",');
        SQL.Add('"DateTo"');
        SQL.Add(')');
        SQL.Add(' VALUES');
        SQL.Add('(');
        SQL.Add('01,');
        SQL.Add('''January'',');
        SQL.Add(':b1Date,');
        SQL.Add(':e1Date,');
        ParamByName('b1Date').AsDate := 2007-12-31;
        ParamByName('e1Date').AsDate := 2008-01-06;
        SQL.Add(')');
        ExecSQL;
        end
     else
        Close;
     end;
  { Now open the tables }
  tblEmployee.Open;
  tblPeriod.open;

Using Delphi 2007.  ElevateDB 1.06 Delphi 2007

Your help will be appreciated.

Regards
Koos

Koos

Point 1 - it has to be the second query - the first doesn't have 14 lines


Point 2 - as a guess its something to do with

        ParamByName('b1Date').AsDate := 2007-12-31;
        ParamByName('e1Date').AsDate := 2008-01-06;


but even if its not 2007-12-31 is not a date its a calculation. - you'll get some weird results

Point 3  - here's how I'd suggest you find the problem.

Insert SQL.SaveToFile(any old file name) before the ExecSQL

Take the text from the file and paste into EDBManager that will allow you to interactively find where the problem is, correct it and put the result back where it belongs.

OR

Use EDBManager to do the job using its dialogs, cut the sql from sqlhistory in explorer and paste that back into your app.

HTH


Roy Lambert

Roy Lambert <roy.lambert@skynet.co.uk> wrote:

Koos

Point 1 - it has to be the second query - the first doesn't have 14 lines


Point 2 - as a guess its something to do with

        ParamByName('b1Date').AsDate := 2007-12-31;
        ParamByName('e1Date').AsDate := 2008-01-06;


but even if its not 2007-12-31 is not a date its a calculation. - you'll get some weird results

Point 3  - here's how I'd suggest you find the problem.

Insert SQL.SaveToFile(any old file name) before the ExecSQL

Take the text from the file and paste into EDBManager that will allow you to interactively find where the problem is, correct it and put the result back where it belongs.

OR

Use EDBManager to do the job using its dialogs, cut the sql from sqlhistory in explorer and paste that back into your app.

HTH


Roy Lambert


Roy thank you for the reply. I did what you said and this is what it's trying to insert (text from the file):

INSERT INTO "Period"
(
"PeriodID",
"MonthDesc",
"DateFrom",
"DateTo"
)
VALUES
(
01,
'January',
:b1Date,
:e1Date,
)

Regards
Koos

Koos Loots wrote:

> ...
> INSERT INTO "Period"
> (
> "PeriodID",
> "MonthDesc",
> "DateFrom",
> :b1Date,
> :e1Date,
> )

What happens if you remove the comma after :elDate, ?

"Malcolm" <malcolm@spam.will.bounce> wrote:

Koos Loots wrote:

> ...
> INSERT INTO "Period"
> (
> "PeriodID",
> "MonthDesc",
> "DateFrom",
> :b1Date,
> :e1Date,
> )

Malcolm,

Thank you it works after taking out the comma, at least the error does not appear anymore. But the date inserted is wrong. I inserted 2007-12-31 and 2008-01-06 but when I run my
program 1905-05-17 and 1905-06-23 was inserted.



What happens if you remove the comma after :elDate, ?

Koos Loots <jjloots@iburst.co.za> wrote:

"Malcolm" <malcolm@spam.will.bounce> wrote:

Koos Loots wrote:

> ...
> INSERT INTO "Period"
> (
> "PeriodID",
> "MonthDesc",
> "DateFrom",
> :b1Date,
> :e1Date,
> )

Malcolm,

Thank you it works after taking out the comma, at least the error does not appear anymore. But the date inserted is wrong. I inserted 2007-12-31 and 2008-01-06 but when I run my
program 1905-05-17 and 1905-06-23 was inserted.

I have change the code to this and it works fine now. ParamByName('b1Date').AsDate := StrToDate('2007-12-31');

Thanks for helping me!



What happens if you remove the comma after :elDate, ?

Koos


I missed the fact that you have a comma after :e1Date. That shouldn't be there. Try removing it and running your code again. Don't forget you'll have to alter

ParamByName('b1Date').AsDate := 2007-12-31;

to

ParamByName('b1Date').AsDate := StrToDate('31/12/2007');

making sure you follow your local date formatting rules

Roy Lambert

Roy Lambert <roy.lambert@skynet.co.uk> wrote:

Koos


I missed the fact that you have a comma after :e1Date. That shouldn't be there. Try removing it and running your code again. Don't forget you'll have to alter

ParamByName('b1Date').AsDate := 2007-12-31;

to

ParamByName('b1Date').AsDate := StrToDate('31/12/2007');

making sure you follow your local date formatting rules

Roy Lambert

Roy,

Thank you for your help. It works fine now.

Koos