asked on

Delphi ADO query as a string

Hello Experts;

Using Delphi 7 ADO components the following code snippet works to retrieve a row from a SQL DB table named 'Item' where the column field is an integer. The result is displayed in a DBGrid.

ADOQuery1.Prepared := True;
with ADOQuery1 do
begin
SQL.Text := 'SELECT * FROM Item WHERE BinLocation= 2001126';
Active := True;

Open in new window

How can I retrieve a row where the variable is a string and the column field is set as a nvarchar. I tried ADOQuery1.FieldByName('ItemLookupCode').AsString followed by the query but the program returns an error 'ItemLookupCode field not found'.

Without the .AsString statement to modify the flowing query;

'SQL.Text := 'SELECT * FROM Item WHERE ItemLookupCode= T-23RF';

Open in new window

the code returns the error 'Conversion failed when converting the nvarchar value #2472 to data type int.'

I'm not sure I understand the SQL conversion issue other than the code is apparently trying to use strings to retrieve a variable that is an integer. I need help in creating a query statement that will allow retreiving rows using strings in the WHERE clause?

Thank you,
Visionetv

ASKER CERTIFIED SOLUTION

rinfo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rinfo

This is the standard procedure for using string fldvalue in a query string.
IF some value is not quoted and is not a column name , it assumes that column must be
of type integer for which no quotation is needed.

visionetv

ASKER

Rinfo;

Thank you for the response I declared field value (fldval) as a string variable and that cured the conversion error but the record set that is returned by the Query is empty, only the DB column headings are showing in the DBGrid.

visionetv

ASKER

Disregard my last comment, there was a typo in the code. It's now retrieving record sets and dsiplaying them in DBGrid properly.

Thanks again for the info.