SQL 6.5 with Delphi 1.0 on Win95 problem.

[Scott Murray]

Hello,
I have a situation where I have to run a Delphi 1.0 application on
Win95 via ODBC to MS-SQL 6.5 server. This is working for me on
several workstations.
I always install 16-bit BDE and make appropriate changes to odbc*.INI
files, etc.
I have a machine which has just now been setup for this situation -
and at the same time converted to a wireless network card.

Everything works great as far as network connection, running querys,
etc. But when get to the point of saving a transaction - I am getting
the message:
"Error - EDBEngineError(General SQL error. [Microsoft]{ODBC SQL Server
Driver][SQL Server]Line 2: Incorrect syntax near 'DATE_TIME')

Here is the sql statement and partial source code:

sql.Add('INSERT INTO INVOICES (INVOICE_NO, TOTAL, TAX, VOID_CODE,
TYPE_CODE,');
SQL.Add('invoices."DATE_TIME", TERM_NUMB, USER_NUMB, CUSTOMERID,
BATCH)');
SQL.Add('VALUES (:xINVOICE_NO, :xTOTAL, :xTAX, :xVOID_CODE,
:xTYPE_CODE,');
SQL.Add(':xDATE_TIME,'+':xTERM_NUMB, :xUSER_NUMB, :xCustomerID,
:xBATCH)');
ParamByName('xINVOICE_NO').AsString := InvoiceTotals.InvoiceNumber;
ParamByName('xTOTAL').AsFloat := InvoiceTotals.Total;
ParamByName('xTAX').AsFloat := InvoiceTotals.Tax;
ParamByName('xVOID_CODE').AsString := vcFinalized;
ParamByName('xTYPE_CODE').AsString := InvoiceTotals.TypeCode;
ParamByName('xDATE_TIME').AsDateTime := InvoiceTotals.DateTime;
ParamByName('xTERM_NUMB').AsInteger :=
FUseStation;{InvoiceTotals.StationNumb;}
ParamByName('xUSER_NUMB').AsInteger := InvoiceTotals.UserNumb;
ParamByName('xCUSTOMERID').AsString := InvoiceTotals.CustomerID;
ParamByName('xBATCH').AsString := DateToFieldStr(FUseBatch);
ExecSQL;

I realize DATE_TIME is some sort of reserved word but in this context
(ie. table name and quotes) it works on all other stations. This is
the first part of a two part SQL transaction.

What could be wrong?
Thanks - Scott Murray.

[Dan Guzman]

Since the error is pointing to the second line of your SQL statement, you
might try removing the table name prefix from the DATE_TIME column line the
example below. There may be issues with your ancient ODBC/BDE drivers and
the redundant table name.

SQL.Add('"DATE_TIME", TERM_NUMB, USER_NUMB, CUSTOMERID,BATCH)');

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Scott Murray" <sc****@cland.net> wrote in message
news:12*************************@posting.google.co m...

Hello,
I have a situation where I have to run a Delphi 1.0 application on
Win95 via ODBC to MS-SQL 6.5 server. This is working for me on
several workstations.
I always install 16-bit BDE and make appropriate changes to odbc*.INI
files, etc.
I have a machine which has just now been setup for this situation -
and at the same time converted to a wireless network card.

Everything works great as far as network connection, running querys,
etc. But when get to the point of saving a transaction - I am getting
the message:
"Error - EDBEngineError(General SQL error. [Microsoft]{ODBC SQL Server
Driver][SQL Server]Line 2: Incorrect syntax near 'DATE_TIME')

Here is the sql statement and partial source code:

sql.Add('INSERT INTO INVOICES (INVOICE_NO, TOTAL, TAX, VOID_CODE,
TYPE_CODE,');
SQL.Add('invoices."DATE_TIME", TERM_NUMB, USER_NUMB, CUSTOMERID,
BATCH)');
SQL.Add('VALUES (:xINVOICE_NO, :xTOTAL, :xTAX, :xVOID_CODE,
:xTYPE_CODE,');
SQL.Add(':xDATE_TIME,'+':xTERM_NUMB, :xUSER_NUMB, :xCustomerID,
:xBATCH)');
ParamByName('xINVOICE_NO').AsString := InvoiceTotals.InvoiceNumber;
ParamByName('xTOTAL').AsFloat := InvoiceTotals.Total;
ParamByName('xTAX').AsFloat := InvoiceTotals.Tax;
ParamByName('xVOID_CODE').AsString := vcFinalized;
ParamByName('xTYPE_CODE').AsString := InvoiceTotals.TypeCode;
ParamByName('xDATE_TIME').AsDateTime := InvoiceTotals.DateTime;
ParamByName('xTERM_NUMB').AsInteger :=
FUseStation;{InvoiceTotals.StationNumb;}
ParamByName('xUSER_NUMB').AsInteger := InvoiceTotals.UserNumb;
ParamByName('xCUSTOMERID').AsString := InvoiceTotals.CustomerID;
ParamByName('xBATCH').AsString := DateToFieldStr(FUseBatch);
ExecSQL;

I realize DATE_TIME is some sort of reserved word but in this context
(ie. table name and quotes) it works on all other stations. This is
the first part of a two part SQL transaction.

What could be wrong?
Thanks - Scott Murray.

[Ryan]

> sql.Add('INSERT INTO INVOICES (INVOICE_NO, TOTAL, TAX, VOID_CODE,

TYPE_CODE,');
SQL.Add('invoices."DATE_TIME", TERM_NUMB, USER_NUMB, CUSTOMERID,
BATCH)');
SQL.Add('VALUES (:xINVOICE_NO, :xTOTAL, :xTAX, :xVOID_CODE,
:xTYPE_CODE,');
SQL.Add(':xDATE_TIME,'+':xTERM_NUMB, :xUSER_NUMB, :xCustomerID,
:xBATCH)');

Try...

sql.Add('INSERT INTO INVOICES (INVOICE_NO, TOTAL, TAX, VOID_CODE,
TYPE_CODE,');
SQL.Add('invoices, DATE_TIME, TERM_NUMB, USER_NUMB, CUSTOMERID,
BATCH)');
SQL.Add('VALUES (:xINVOICE_NO, :xTOTAL, :xTAX, :xVOID_CODE,
:xTYPE_CODE,');
SQL.Add(':xDATE_TIME,'+':xTERM_NUMB, :xUSER_NUMB,
:xCustomerID,:xBATCH)');

I think it may be a typo after the word invoices in your second Add
statement. If you need to add in the quotes, try adding ''' instead of
" on either side of the words Date_Time

HTH

Ryan

[Erland Sommarskog]

[posted and mailed, please reply in news]

Scott Murray (sc****@cland.net) writes:

Everything works great as far as network connection, running querys,
etc. But when get to the point of saving a transaction - I am getting
the message:
"Error - EDBEngineError(General SQL error. [Microsoft]{ODBC SQL Server
Driver][SQL Server]Line 2: Incorrect syntax near 'DATE_TIME')

Here is the sql statement and partial source code:

sql.Add('INSERT INTO INVOICES (INVOICE_NO, TOTAL, TAX, VOID_CODE,
TYPE_CODE,');
SQL.Add('invoices."DATE_TIME", TERM_NUMB, USER_NUMB, CUSTOMERID,
BATCH)');

Since you are on SQL 6.5, and you are using an antique ODBC driver,
the setting QUOTED_IDENTIFIER is likely to be off, so "DATE_TIME"
here is a string literal, which of course is not legale here.

Also, I don't think that the prefix 'invoices.' is legal, and anyway
it serves no purpose.

DATE_TIME itself is not a reserved word in any sense in T-SQL.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp