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.

Single parent childless families

I was updating a DLL that takes XML from a web page and extracts the data from it to update a database. The web control had been upgraded from a simple data grid to a tree grid.

The DLL only handled data grids so I wrote a new routine to handle the whole tree grid, which would recursively process the child nodes.

In the original routine, I put a check for any tree like behavior within the XML and kick it out to the calling process with a simple message - "Tree", the calling process would see this message and call my new routine to process the XML in a different way.

Little did I realise that my check in the first routine had a slight bug in it, it kicked normal data grids back as trees as well but the whole process still worked, this is because the routine that handles trees with all the child nodes also handles straight lists as well.

A straight list is but a tree without the child nodes, so no problem then. I didn't leave it there of course, I tidied up the code and made it look like I meant to do that in the first place. :-)

A Light hearted one

I was compressing a large file in 7Zip when I decided to cancel it, it prompted me to make sure.

So, I presume that [Yes] would cancel it, [No] would not and ahem, [Cancel], what do you do? Cancel the compression or Cancel the request to cancel?

Have you tried turning it off and on again?

Have you ever wondered why IT support people ask you to turn it off and on again?

Well, sometimes it is just to get you off their back whilst they finish off their morning coffee and croissant.

Sometimes it helps, like it as not, software has bugs and some of those bugs manifest themselves as memory leaks. A memory leak is where a program will allocate memory for its own use but not completely release it before allocating more memory later on, if left unchecked a program can consume vast amounts of memory and slow the system down and/or prevent other programs from executing properly. Other times it could be a program that gone into some infinite loop and hogs the CPU thereby denying CPU time to other processes.

You could try tracking down such problems but as support time is usually paid for by either the client or the software house providing the support, it is generally preferable to resolve an issue quickly rather than string it out for long periods of (costly) time.

Other times, turning it off and on again is the only way, e.g., consider your Internet connection, if it slowed down after a while you might call your ISP's support line, the first thing they usually tell you to do is turn your router off for 10 seconds and switch it back on again. This is because your connection is going down a telephone line that is logically split into pipes, each pipe can carry a certain amount of traffic and the pipe that you're connected to may be very busy, a simple disconnect and reconnect may cure the problem by the sheer fact that reconnecting will generally connect you to a less busy pipe.

Another thing you may ask, why do support guys ask you to power something off for 10 seconds before powering it back on? I'm no electronics expert but I would imagine this has something to do with allowing the capacitors to discharge fully as they can still retain charge some time after powering off, after all, they are in there to smooth out the current in the first place so removing the power for a very short period of time may not completely power down the unit hence the delay asked for by the support technician.

Shutting down Windows 7 without installing updates

Sometimes I want to do this, I may be in a hurry but it appears the only shutdown option on the start menu will always install updates if they are there. Lately for me, they're always there as I have about 7 of them that keep failing to install, perhaps that's a subject for another post as I don't want to just post questions here, I want solutions as I'm sure anyone reading this does too.

Anyway, back to shutting down Windows, if you press Alt+F4  on the desktop it will prompt with a set of shutdown options that you probably haven't seen since Windows XP, one will be an option to install updates and shutdown but there is another option there with just "Shut down".

Shortcut keys are a real blessing in any GUI.

Google

Oh dear, it seems Google aren't without their own issues, I was trying to search something from Google+ and got this:

Nice robot by the way

Trouble checking in files using Tortoise-SVN

I had this problem today, where I could not check-in my modified files to SVN, the message I got was:

I tried all sorts of fixes (update, cleanup commands, etc) and googled it too, in the end I decided the brute force attack was the only way to go so I copied the modified files to another location, deleted the local folder from my hard drive, updated the parent folder from SVN, which restored the troublesome folder then copied the modified files back, ran the commit and hey presto, fixed.