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.

A couple of VB6 gotchas

If, like me, you're lucky enough to be maintaining legacy software written in ancient development platforms then you're probably used to workarounds rather than waiting for a product to be fixed.

I came across a couple of particularly scary ones in the VB6 environment concerning the loading and saving of files, I say scary because what it actually loads and saves might not be what you'd expect.

Firstly the loading, once a project is loaded, it seems to have a mindset that it is the only program that could possibly be altering any files so that when you close and reload a module for instance, it doesn't actually read the code from the disk but appears to use a cached version of the file. This is great for speed but bad news if like me, you use third party programs that could alter the source files independent of the VB6 environment.

An example would be Tortoise SVN, where if you have different developers working on a project and you're not using an SCC plug-in then getting the latest version of a source file may be futile if the VB6 environment doesn't actually read it and uses its cached version instead, then you will be working with an out of date source file that after saving, will not incorporate the latest amendments from the others in your team.

Workaround: Short of always using a SCC plug-in, which given what's in the marketplace currently, I'd rather not, it'll be a case of closing VB6 down and re-opening it.

Secondly, on saving. In just about every other language or development platform that I have developed in, when you compile the application to an executable, it automatically saves all the source files that you are working on. VB6 is different, being more of a tokeniser than a real compiler, again it's using what it has cached in RAM to convert to an executable rather than in older 3GLs, a separate compiler program would have been called to parse the source file(s) and hence the need to save them automatically when you compile.

So now I have a DLL that is compiled and managed to check in a source file that doesn't, brilliant.

Workaround: Hit the save button.

The only problem with these workarounds is that you have to remember to do them.