Delphi ADO query as a string

Posted on 2012-08-18
Last Modified: 2012-08-19
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,
Question by:visionetv
    LVL 9

    Accepted 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).
    LVL 9

    Expert Comment

    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.

    Author Comment


    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.

    Author Closing Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
    This article will show, step by step, how to integrate R code into a R Sweave document
    The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.
    This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now