using @@Identity in SQl Server

[swan]

I'm attempting to retrieve a records unique id after an insert using MS SQL Server. A bit of research tells "Select @@Identity" is the most appropriate.
My code snippet:

Code: Select all

            If DatabaseUpdate(0, MSSQL_query)
              If DatabaseQuery(0, "SELECT @@IDENTITY AS 'id'")
                MSSQL_Products_ProductID = GetDatabaseLong(0, 0)
                sw_log+ " "+Str(MSSQL_Products_ProductID)+" "
              EndIf
              debug sw_log
            EndIf

Always returns null. I'd very much appreciate a shove in the right direction. Cheers

[los]

I don't know either, but just from looking at the manual I wonder: don't you have to make a call to NextDatabaseRow to get at the results?

[swan]

Thanx los. Yes U picked the error.
I thought because only one result would ever return it wasn't necessary to kick the next row.
Forever learning

[Seldon]

Swan I'd suggest you to include in the query the name of the table which you need the last ID from, in this way:

Code: Select all

If DatabaseQuery(0, "SELECT @@IDENTITY AS 'id' FROM TBL_yourtable;")

That is a far better to avoid cuncurrency problems if many users are accessing (inserting into) the database, though you're not 100%
sure. According to my experience the only 100% sure method to get the last ID back, is to make a SELECT looking for data you've just inserted
in the WHERE clause.

[swan]

Thanx Seldon. Yes your right as I just found out. The database I'm writing to isn't mine and on that table had a trigger to insert into another and because of that I was getting strange results. So, yes nominating a table seemed to have overcome it. Cheers ...