Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3190
  • Last Modified:

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
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,
  • 2
  • 2
1 Solution
You are trying to filter table on a  fieldvalue which is of type nvarchar.
To achieve this you need to change sql to
fldval = 'T-23RF';
SQL.Text := 'SELECT * FROM Item WHERE ItemLookupCode='+quotedstr(fldval).
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.
visionetvAuthor Commented:

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.
visionetvAuthor Commented:
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.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now