12-19-2018 12:52 PM
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...
Solved! Go to Solution.
12-19-2018 01:36 PM - edited 12-19-2018 01:37 PM
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.
12-19-2018 02:32 PM
I don't think named parameters are supported. Look into the Create Parameterized Query VI.
12-19-2018 02:48 PM
Try this:
UPDATE Subassembly_Info SET Rework = 'True'
WHERE Component_ID = (SELECT MAX(Component_ID) FROM Subassembly-Info)
12-19-2018 03:15 PM
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.