LabVIEW SQL 'Declare' Variable

[SandraY]

Hello,

I have scoured the internet searching for an answer to this, but no luck...

I am attempting to write to a MS SQL database from LabVIEW.

The query is:

DECLARE @k INTEGER
SELECT @k = MAX(Component_ID) FROM Subassembly_Info
UPDATE Subassembly_Info
SET Rework = 'True'
WHERE Component_ID = @k

In this example, Component_ID is my primary key from the table Subassembly_Info, and Rework is a bit (boolean) column

The query works just fine in SQL Server Management Studio, but I get this error when I run it using LabVIEW:

Error -2147217900 occurred at NI_Database_API.lvlib:Conn Execute.vi->Write_SN_To_DB.vi

Possible reason(s):
ADO Error: 0x80040E14
Exception occured in Microsoft SQL Server Native Client 11.0: Incorrect syntax near '@k'. in NI_Database_API.lvlib:Conn Execute.vi->Write_SN_To_DB.vi

What I am trying to do is write data to the last row that already exists in the table (i.e., not append a new row). I am doing this because I am going to be writing ~90 sets of measurements to the table (each measurement gets a column), and I would like to do this in a loop structure.

Any help would be much appreciated...

[TLE · Accepted solution]

Look into using the clause "output inserted.id" on your initial insert statement to get the primary ID.  For example,

INSERT INTO my_table
(
column_name_1,
column_name_2
)
output inserted.id
VALUES
(
'value1',
'value2'
)

You could also look at putting your code in a Stored Procedure and calling it from LabVIEW.  That is what I normally do.

[aputman]

I don't think named parameters are supported.  Look into the Create Parameterized Query VI.

[drjdpowell]

Try this:

UPDATE Subassembly_Info SET Rework = 'True'
WHERE Component_ID = (SELECT MAX(Component_ID) FROM Subassembly-Info)

[SandraY]

Thank you!

I am attaching my VI that returns the index of what I've last written to. It is kind of a mess, but Case 1 illustrates the solution.