Insert into ADO (something) values (gotcha)

Yep, another gotcha. This time revolving around a SQL Identity column, when inserting a row into a table, you might want to know what SQL Server has populated the identity column with, in SQL this is done by using a built in function called "scope_identity()". I use this to get around another gotcha in that using the build in global variable "@@Identity" is flakey at best as if the table has a trigger that inserts into another table with an identity column then @@Identity will contain the value fro mthe other table.

Using VB6 and ADO to insert into a table, you can open a recordset on the following SQL:

set nocount on
insert into MyTable (GroupCode, TypeCode, Description, SomeID)
values ('TBTEST', 'Blah', 'UnitTest 39408.8', Null)
set nocount off
select scope_identity()

The one row and column that is returned is the value of scope_identity(), which is the value we want.

Now opening this recordset presents problems as unless you specify a forward-only cursor type, the ADO engine will read back and forth in the recordset, not normally a problem for normal recordsets, if a little wasteful (re-reading the recordset many times when you only asked it to read it once) but on this recordset that executes stuff as well it is a real problem.

If you open up the recordset, and it re-reads the first record, the insert statement is executed again, this would result in a duplicate record being inserted into the table or an error occurring if a unique index/constraint has been violated.

The simple solution is to use a forward-only cursor when opening the recordset, e.g.

rst.Open strSQL, Me.Connection, adOpenForwardOnly, adLockReadOnly

This will ensure that the insert statement at the begining of the SQL is only excuted the once.