SQL Query in TestStand

[mama007]

Hi,

I am having trouble running a query in a Open SQL statement

what I have in SQL statement is:

"SELECT TEMP2.*  FROM TEMP2  WHERE Repair_Code=Locals.Repeted_Fault[1]"

I am getting error:

" Error executing substep 'Post'.
The following SQL command failed: 'SELECT TEMP2.*  FROM TEMP2  WHERE Repair_Code=Locals.Repeted_Fault[1]...'
Native error code -2147217900 0x80040e14
Microsoft JET Database Engine:

Syntax error (missing operator) in query expression 'Repair_Code=Locals.Repeted_Fault[1]'. "

It does work if I remove the local varibale(number) and use absolute number. I have tried various option like leave the local out of quotes, add single quote to query etc. None worked out. I have Statement requires parameters box unticked.

I have similar problem using a local variable of type string. But sorted out using Build example.

Any help please...

Thanks

MAMA007

[Dennis_Knutson]

Your command should be:

"SELECT TEMP2.*  FROM TEMP2  WHERE Repair_Code = " + Locals.Repeted_Fault[1]

By enclosing the TestStand expression inside the quote marks, all you are passing is the literal string and not letting the expression be evaluated.

[mama007]

Thanks Dennis,

I have tried it too and getting this error:

"Error executing substep 'Post'.
Error In SQL Statement Expression. "SELECT TEMP2.*  FROM TEMP2  WHERE Repair_Code ="+ Locals.Repeted_Fault[1]
Specified value does not have the expected type.  "

The only way I can get this statement working is by using absolute value like below

"SELECT TEMP2.*  FROM TEMP2  WHERE Repair_Code =207"

I thought the double quotes were just there because TestStand treat the statement as an expression and to use an string value or parameter all we need to do is add extra set of single quotes to the existing double quotes. But it didn't worked like that

Any clues plz...

Thanks

mama007

[RayFarmer]

Hi,

Dont you have to convert your number to a string  so that it concat with the rest of the string expression.

eg

"SELECT TEMP2.*  FROM TEMP2  WHERE Repair_Code = " + Str(Locals.Reported_Fault[1])

regards

Ray Farmer

[Dennis_Knutson]

You're right. I missed that. Sorry.

[mama007]

Thanks Ray,

It did worked perfectly. I thought it would give a error regarding data type mismatch !!!

I also have another question here?

When u run a qurey, how everyone normally get that information to a seq. variable. I feel i am doing in a weired way. For example if I need to find count of a Column in a table,  I run the qurey and save the information to a new table. Then Select newtable.* and perform read operation. It does work. But I have to create a new table and then delete that at the end

So everytime I need to do count operation I have to create a new table and delete it at the end and this is completely waste of time. But is there any other way to return single value returns like count,max..etc staright to a local variable

Thanks in advance

-mama007

[mama007]

Hi,

Using Labwindows SQL tool kit simplify my previous question. I use both teststand and Labwindows.

I wonder how everyone does run some simple qurries.

All I need here is find the most common entry in my  Log.xls  and append this re-occuring information to my Teststand  print report. I have done it by linking the my table to a access database and running some SQL quries & data operations on it to retrieve the re-occuring information

Please can anyone tell if there is any simpler and better way !!!

Thanks

-mama007

[RayFarmer]

Hi,

That seems to be a long winded way of obatining the result. Have you looked that the example WritingAndReadingTableData.seq in Teststand\examples\database.

That may help.

Regards

Ray Farmer

[ank]

Dear Dennis,

I have a little different problem than this.... I have a database in which table has a string datatype as a primary key and unique ID... Hence I am trying to run the following Statement:

Select * from BIODATA where BIODATA!ID = "T01.01"

The code works great in LabVIEW but I have trouble in the exacting the same in Teststand.

Attached are the sapshots of my Teststand Steps and Database

I have first opend the database, then opened a SQL statement that has the SQL command i want to pass. Next I have commanded a data operation to extract the current record.

It still is not working!!

[RayFarmer]

Hi,

In your Data Operations step your SQL statement should be

"Select * from BIODATA where BIODATA!ID = \"T01.01\""

not

"Select * from BIODATA where BIODATA!ID = T01.01"

Regards

Ray Farmer