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

x
  • 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
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
0
visionetv
Asked:
visionetv
  • 2
  • 2
1 Solution
 
rinfoCommented:
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).
0
 
rinfoCommented:
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.
0
 
visionetvAuthor Commented:
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.
0
 
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.
0

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