Select crashes

Vrotie

Delphi Developer

Select crashes

2005-11-08 09:41:34 AM
delphi96
Hi,
I have a problem selecting and displaying fields. If the data in the field
'JOBNUM' is digits only (eg 500) all works fine. If however there is a
character as part of the field (eg A520), I get the error message 'unknown
column'.
*****
JobNumber is defined as a string variable.
JOBNUM is 'varchar(20) not null' in the database.
This is my select statement..............
******* routine ********
IBSQL1.SQL.Text := 'SELECT * FROM JOBCARDS WHERE JOBNUM = ' + JobNumber;
IBSQL1.ExecQuery;
labCustomerDynamic.Caption := IBSQL1.Fields[1].AsString ;
labCustomerNumberDynamic.Caption := IBSQL1.Fields[2].AsString;
labDateInDynamic.Caption := IBSQL1.Fields[0].AsString;
labJobNumberDynamic.Caption := IBSQL1.Fields[3].AsString;
labONDynamic.Caption := IBSQL1.Fields[4].AsString;
***** end of routine *****
Hope someone can help.
Lew

Vrotie

Delphi Developer

2005-11-08 11:39:49 AM

Re:Select crashes

Thank you very much for your kind assistance Wayne.
Got it working just fine now.
"Wayne Niddery [TeamB]" <XXXX@XXXXX.COM>writes

Quote

Vrotie writes:
>
>I have a problem selecting and displaying fields. If the data in the
>field 'JOBNUM' is digits only (eg 500) all works fine. If however
>there is a character as part of the field (eg A520), I get the error
>message 'unknown column'.
>
>IBSQL1.SQL.Text := 'SELECT * FROM JOBCARDS WHERE JOBNUM = ' + JobNumber;

Interbase is smart enough to be able to compare a number passed as a
parameter with a field defined as a varchar without error, but when you
pass
a parameter that is not a number, you *must* enclose it in quotes.
Otherwise, as you see, it assumes you are naming a column - Interbase
sees:

SELECT * FROM JOBCARDS WHERE JOBNUM = A520;

Either manually include quotes, or use a parameterized query:

IBSQL1.SQL.Text := 'SELECT * FROM JOBCARDS WHERE JOBNUM = '
+ QuotedStr(JobNumber);

or

IBSQL1.SQL.Text := 'SELECT * FROM JOBCARDS WHERE JOBNUM = :jobnum';
IBSQL1.Params[0].AsString := JobNumber;

--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: www.logicfundamentals.com/RADBooks.html
"Some see private enterprise as a predatory target to be shot, others
as a cow to be milked, but few are those who see it as a sturdy horse
pulling the wagon." - Winston Churchill

Wayne Niddery [TeamB]

Delphi Developer

2005-11-08 02:23:49 PM

Re:Select crashes

Vrotie writes:

Quote

I have a problem selecting and displaying fields. If the data in the
field 'JOBNUM' is digits only (eg 500) all works fine. If however
there is a character as part of the field (eg A520), I get the error
message 'unknown column'.

IBSQL1.SQL.Text := 'SELECT * FROM JOBCARDS WHERE JOBNUM = ' + JobNumber;
Interbase is smart enough to be able to compare a number passed as a

parameter with a field defined as a varchar without error, but when you pass
a parameter that is not a number, you *must* enclose it in quotes.
Otherwise, as you see, it assumes you are naming a column - Interbase sees:
SELECT * FROM JOBCARDS WHERE JOBNUM = A520;
Either manually include quotes, or use a parameterized query:
IBSQL1.SQL.Text := 'SELECT * FROM JOBCARDS WHERE JOBNUM = '
+ QuotedStr(JobNumber);
or
IBSQL1.SQL.Text := 'SELECT * FROM JOBCARDS WHERE JOBNUM = :jobnum';
IBSQL1.Params[0].AsString := JobNumber;
--
Wayne Niddery - Logic Fundamentals, Inc. (www.logicfundamentals.com)
RADBooks: www.logicfundamentals.com/RADBooks.html
"Some see private enterprise as a predatory target to be shot, others
as a cow to be milked, but few are those who see it as a sturdy horse
pulling the wagon." - Winston Churchill