Has anyone ever succeeded to use the scope_identity() from the LabVIEW DB toolkit ?

[DavidBoyd]

Dave (the other Dave!),

Ah, got it (the SQL-S vs. Access thing).

The replies from five years ago are on target, insofar as there may be multiple result sets coming back from compound SQL statements being executed.  The DB toolkit's handling of multiple resultsets is problematic because of a bug in the fetch-next-recordset toolkit VI (see my recent other post about that).

And I absolutely would prefer that you use a stored procedure rather than send SQL command text on the fly.

But never mind that, you just want the quickest solution.  And at least for SQL Server, I think I've got it for you.  It's the OUTPUT INSERTED clause.  Just place it before your VALUES specification, a la:

INSERT INTO mytable (field1, field2, field3)

OUTPUT INSERTED.*

VALUES ('myval1', 'myval2', myval3');

...and be done with the SCOPE_IDENTITY() nonsense completely.  Your INSERT statement should now generate a recordset which will be the complete record you just inserted, including the IDENTITY field you didn't actually assign.  If you don't want the other fields, you can change the OUTPUT INSERTED.* to OUTPUT INSERTED.<whatever-the-ident-field-name-is> (though you'll always get a 2-D variant array from the 'fetch all' VI if that's what you use to get the recordset).

Hope this helps!

Dave (not you, me!)

[The_Seeker]

Hi Dave,

Thanks for the direction. Yes, this method is working! However, I'm seeing something odd. Each Insert produces 4 duplicate records..(?)

I'm looking into this now to see if there is an obvious reason for this in my code. (Perhaps it's related to the bug you mentioned in your last post..?)

Anyway, for others who may have this issue in the future, this is the exact SQL Query that is working for me (-- except for the 4-record duplication issue):

INSERT INTO tblTestRunData(TestRunId,RobotStationEmissivityId,imagepath) OUTPUT INSERTED.* values (31,10,'C:\\Test\TestImage.png')

Thanks again for the pointers, Dave. Based on your comments, I will look into stored procedures. (Maybe this will prevent the multiple record issue?)  

Cheers,

-- (the other, other) Dave

[The_Seeker]

Hi Dave,

To follow-up from my previous posting about the 4 copies from each insert, it appears to be a cursor issue. I had selected a "dynamic" cursor (for the Query function input). After setting the cursor to "forward-only", I am now getting just one record with each INSERT.

So, it looks like we have a solution!

I need to test with 6 systems, all writing 3 records/second, to make sure we don't have any issues, but I expect this should be fine for our purposes.

Thanks again for all the help!

Cheers,

-- (one of the) Dave(s) 

[The_Seeker]

Just following up on my previous thread.

Yes, this approached works. We have been running multiple systems concurrently for several weeks with no data overlap.

Thanks for the assistance, Dave!