Using a number variable in an SQL statement

[Stuart_M]

Hi,

I am trying to use a variable in an sql statement and I have run into problems when the variable is a number. The following line of code works if the variable is a string but not if it is a number.

"SELECT TOP 1 UUT_STATUS FROM UNIT_UUT_RESULT WHERE UnitID =  '" + Locals.LocalUnitID + "' ORDER BY START_DATE_TIME DESC"

Is there a difference in the use of the single and double quotes and the + sign for number variables?

Thanks

Stuart

[Jervin Justin]

Hi Stuart,

I am assuming that the UnitID is stored as a numeric in the database? If so, the proper SQL syntax for comparing with numerics should not use a single quote (or any quotes for that matter). The quotes are used only for strings.

So you would want to use:
"SELECT TOP 1 UUT_STATUS FROM UNIT_UUT_RESULT WHERE UnitID =  " + Locals.LocalUnitID + " ORDER BY START_DATE_TIME DESC"

This is really more of an SQL question universal to all languages, not just TestStand.
Here is an excellent resource that you can consult:
http://www.w3schools.com/sql/sql_where.asp

[Josh_W · Accepted solution]

Jervin is almost correct in the above post.

While he is correct that numbers in SQL are not surrounded by single quotes ('), we still need the entire expression to be a string.  If Locals.LocalUnitID is stored as a number in TestStand, you will need to cast it to a string so that the concatination of strings works correctly.

I believe that the proper form is this:

"SELECT TOP 1 UUT_STATUS FROM UNIT_UUT_RESULT WHERE UnitID =  " + Str(Locals.LocalUnitID) + " ORDER BY START_DATE_TIME DESC"

[Stuart_M]

Hi Josh,

That works.

Thanks

Stuart