SQL Return Code Processing

[sshafe]

Ok, I'm back again. I'm running into this problem. I am trying to catch errors when inserting records into a table. No problems if they are non-existant, but if the record already exists, I get a "cannot insert duplicate key in object". I am a DBA, so I am forcing this error and trying to get my script to write the error to the log file and continue processing rest of the inserts I need to do. The problem is, my error handling is not working.

Could someone point out what I am doing wrong or how to fix it?

Thanks :-(


If Err.number <> 0 Then
LogFile.WriteLine(&quot;Error Adding user&quot; & strUserName & &quot; for group &quot; & _
strUserGroup & vbCRLF & vbTab & _
&quot;Description: &quot; & err.Descripion & vbCRLF & vbTab & _
&quot;Error Number: &quot; & err.Number & vbCRLF & vbTab & _
&quot;SQL State: &quot; & err.SQLState)


Else
LogFile.WriteLine(DateToday & &quot; &quot; & Time & vbTab & &quot;Successful add of &quot; & strUserName & _
&quot; for group &quot; & strUserGroup)

End If

 

[dilettante]

If you still have a valid connection object at this point you can check the Errors collection. Otherwise all you have is Err.Number - there is no such thing as Err.SQLState.

Perhaps you are thinking of cases where you declare a variable called Err and use it in a &quot;For Each Err In <conn>.Errors&quot; loop?

It is always bad to declare something to be called Err because of the confusion with the built-in object Err. I'd call it ADOErr or something.

You still need to traverse the Errors collection to examine each Error object though.

Perhaps this will make it clearer. Be sure to see the &quot;VB Example&quot; (link at the bottom of the article) as well for some inspiration:

http://msdn.microsoft.com/library/d...ry/en-us/ado270/htm/mdaobj01_9.asp?frame=true

Note the poor choice of Err in the VB example they show here. They can get away with it because they are using the VB syntax &quot;On Error GoTo <label>&quot; so they don't need to test Err.Number. Don't do this in VBScript. Call it ADOErr or something.

And while we all forget to, try to be sure to declare Option Explicit to avoid nasty, hard to find bugs.