Retreive 'Identity' field upon record insert 1

[zemp]

I am wondering if there is any way to retrieve the value of the 'identity' field in a newly added record from VB while using an Insert SQL statement. Similar to the '@@Identity' property in MS SQL Server. Thanks and Good Luck!

zemp

 

[mirtheil]

What version of Pervasive are you using? Pervasive.SQL 2000i SP4 and Pervasive.SQL V8 both support @@IDENTITY. Also, are you using the ODBC driver or the OLEDB driver?
info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[zemp]

I am using pervasive 2000i SP4 using the OLEDB driver. I am using ADO 2.6.

I can use either driver, whichever gives me the best results. Thanks and Good Luck!

zemp

 

[mirtheil]

Actually the SP4 OLEDB provider has a bug that prevents @@IDENTITY from returning correctly. You can contact Pervasive for an updated driver. The ODBC driver should work however. Pervasive V8 doesn't have this problem.
info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[zemp]

I was able to get the @@Identity property to work with the ODBC provider. However only when I made a separate call to the database. Is there any way to include this in the SQL Insert statement so that only one call is made (like MS SQL Server)? In a multiuser environment it is possible for another user to enter a record between the two calls and then the @@Identity could return an incorrect value, even as it returns the latest created identity value. Thanks and Good Luck!

zemp

 

[mirtheil]

The only way to wrap it in one call would be in a stored procedure. Just out of curiosity, what's the MS SQL call that would return it in one call? You might submit it to Pervasive in case they want to add it. info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[zemp]

With MS SQL I can make one call with an Insert statement and retreive the @@Identity with ADO as follows.

l_strSQL = &quot;SET NOCOUNT ON;INSERT INTO [Table1] ([Field1],[Field2],[Field3]) Values (<intVaLue1>,'<strValue2>','<strValue3>');SELECT @@IDENTITY AS [ID];SET NOCOUNT OFF;&quot;
rs.Open l_strSQL, CONN, adOpenKeyset, adLockOptimistic
lngID= rs(&quot;ID&quot.value

Sounds like a good idea. I think I will send this to Pervasive.


BTW, with Pervasive 2000i SP4 I make a seperate call immedialtly after the insert is successful like this,

l_strSQL = &quot;SELECT @@IDENTITY AS ID FROM Table1&quot;
rs.Open l_strSQL, CONN, adOpenDynamic, adLockOptimistic, adCmdText
lngID = rs![ID].value


Thanks for all your help mirtheil. Now I just need to get the Create Table statements working.
Thanks and Good Luck!

zemp