How to obtain unique ID for a new SQL record

[ATE Man]

Hi

I am using the database connectivity toolkit

When adding a new record to a table I need to obtain a  unique reference number for the new record so i can use it to set up a relationship with another table. Is it true that in an SQL database a unique reference for ecah record is internally generated and stored against each record?

However I cannot see how by I can obtain this by using the included VI's. This seems to defeat the purpose of purchasing a convenient set of high level VI's for dealing with SQL databases, if this can only be achieved by using executing a direct SQL query i would be  grateful if someone could pass me the required SQL statement.

I have tried using the "get DB properties.vi" on the table just after adding the new record and this does give me the index of the last record but this is not a robust approch because the index of the last record is  relative to the other records before it, and this would cause problems if a record was later deleted.

I would much appreciate it an expert could explain how can I obtain a unique reference to a record using the database connectivity toolkit vi's.

Thanks in anticipation of your Help!

Tristan

[smercurio_fc]

What kind of relationship are you trying to set? I'm assuming you're referring to a foreign key constraint? The most straightforward way of doing this is to configure a column in a table to be an identity column. By doing so, when you add a record to the table SQL Server will automatically create a unique value for this column. You can then set this column to be the one-half of your foreign key constraint. As far as getting the value of this column after SQL Server has added the new row, the way I've always done it is by adding a parameter to a stored procedure. Here's an example of a stored procedure that adds a row to a table and returns the value of the identity column for that new record:

 CREATE PROCEDURE dbo.spPartNumberProductCode_Add
    @productCode char(3),
    @productName varchar(50),
    @id int OUTPUT
AS
INSERT INTO tblPartNumberProductCode (
    product_code,
    product_name
    )
VALUES (
    @productCode,
    @productName
    )
SET @id = @@IDENTITY
RETURN @@ERROR
GO

The key line is SET @id = @@IDENTITY.