insert command in "Open SQL Statement" database step

[5197227090]

Hello,

I have a number variable named Optpwr in TestStand and I need to import the value into

an SQL Server 2005 database.

I have created a sequence as bellow:

Open Database

Open SQL Statement

Close SQL Statement

Close Database

The Open Database step runs successfully but the Open SQL Statement gives me this error:

The following SQL command failed: 'INSERT INTO [Test].[dbo].[Opt]([opt]) VALUES (StationGlobals.Optpwr)...'
Native error code -2147217900 0x80040e14
Microsoft OLE DB Provider for SQL Server:
    The name "StationGlobals.Optpwr" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted. 

The Optpwr variable is defined as a number in TestStand and in my database table I defined the column that Optpwr is supposed to be written to as int (I tried float too, but didn't work.)

I also tried this query as well: insert Opt values(StationGlobals.Optpwr)  but I get the same error.

Can you tell me what I'm doing wrong? In what contexts I can use variables in an insert statement? 

Thanks for your help,

Naeemeh

[Dennis_Knutson]

I don't recognize the syntax you are using. What is the table name and what is the column that you are trying to insert to?

You can certainly use an expression as a value. If you had a table called Opt with a column name of Optpwr, the syntax would be:

INSERT INTO Opt (Optpwr) VAUES (StationGlobals.Optpwr)

p.s. To ease debug of complex statements, I often use a function call to create a string that I can look at after it's been evaluated or even do a copy and execute the statement in the DB query environment.

[5197227090]

To test my query I ran it directly on the database and it was successful.

To clarify: I have a table named Opt with a column named Optpwr with type int.

In TestStand I have a number variable named Optpwr which I want to insert into the Opt table.

The command I use is "insert into Opt(Optpwr) values(StationGlobals.Optpwr)" and I keeo getting the error that I posted

in the first post. The end of the error says: "The name "StationGlobals.Optpwr" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted."

Can you tell me what that means? 

Thanks,

Naeemeh

[Dennis_Knutson]

Sorry, I did not give the correct syntax for evaluation. It would be:

"insert into Opt(Optpwr) values(" + str(StationGlobals.Optpwr) + ")"

The statement in the error is not the same as what you want so the evaluation is the problem. That is why I recomended using a function step that writes to a local so that you can inspect the evaluation result before you pass it to the query.

[5197227090]

Sorry but I don't understand what you mean by " The statement in the error is not the same as what you want" what statement do I want.

I also could you explain what you mean by "using a function step that writes to a local"? How can I make a function step?

Also what does " + str(StationGlobals.Optpwr) + " do? Does it write to an string?

Thanks for your help  

[Dennis_Knutson]

The statement in the error says "'INSERT INTO [Test].[dbo].[Opt]([opt]) VALUES (StationGlobals.Optpwr)" and that's clearly not the same statement as the one you actually want. That's a clear indication that the evaluation of the SQL Statement in the SQL Statement step is incorrect. You have to provide a valid string for evaluation and you would place the quote marks around the string 'constant' sections of your query and not around the section that you want TestStand to evaluate (i.e. StationGlobals.Optpwr). Yes, of course it writes everything to a string and that is the reason the str function is before the numeric station global - that has to be converted to a string as well.

What I am calling a function is the Statement step in TestStand. This is where you would write the expression and assign it to a local variable. Sorry for the confusion.

You sound like you are just beginning at TestStand. Expressions, writing and debugging, is pretty common task and you might want to review the manual and help