Solved

VBScript Error Handling failing with ADO BOF/EOF msgs, error 3021 and 3705

Posted on 2006-11-21
3
1,707 Views
Last Modified: 2008-01-09
Howdy folks,

   I'm running a script that queries for a list of server names in a table... (no primary key),  and dumps it into an array.  From that array, it performs another query against the DB, this time looking for records that match a certain criteria (effectively duplicates).  Normal duplicate query functions don't seem to fit the bill, but this solution should work fine, even if its not the most efficient...

   Nonetheless, the script queries for servernames fine, and then calls a function to check for the duplicates of each server returned.  It works fine until it find a record that doesn't have a duplicate, and then it dies out and thats that.  Complete script is at bottom, however this is the section of concern...

   In this function, the first set of data, has a duplicate fouind, so it runs just fine.  When it comes around again, the next record does not have a duplicate, and the query should error out and continue on.  However, it errors out and exits the function, instead of entering the TRUE portion of the If/Then statement.  It does not echo the test statement at the end of the function.  I've used the debugger to verify that err.number = 3021 at that point, but that doesn't explain why its not moving through the IF/Then statement.

    Oddly enough, the error # when the script finally ends, is 3705... Operation is not allowed when the object is open.


Function Only

**********
Function checkfordupes(ssid,assetname,serial1,serial2)
WScript.Echo "Hello"
Err.Number = 0
    querystring = "SELECT IMSAssetsTemp.SS_ID, IMSAssetsTemp.[Asset Name], IMSAssetsTemp.[Hardware Serial Number] " & _
                "FROM IMSAssetsTemp where (IMSAssetsTemp.[Asset Name] <> '" & assetname & _
                "') AND ((IMSAssetsTemp.[Hardware Serial Number] = '" & serial1 & "' OR IMSAssetsTemp.[Hardware Serial Number] = '" & _
                serial2 & "'))"
    WScript.Echo "Query = " & querystring
          objRecordSet.Open querystring, objConnection, adOpenStatic, adLockOptimistic
                        WScript.echo Err.number
              duplicatearray = objrecordset.getrows()
                If Err.Number <> 0 Then
                 WScript.echo Err.number
                 'performed if NO dupes are found
                 WScript.Echo "NO DUPLICATES found for Serial #'s "& coreserial & "," & adjustedserial & " for asset named - " & serverarray(1,servercounter)

      Else
            WScript.echo Err.number
            'performed if duplicate records are found
                  objrecordset.close
                  numOFdupes = UBound(duplicatearray,2)
                  For dupecounter = 0 To numOFdupes
                        WScript.Echo serverarray(1,servercounter) & " has asset records with a matching or similar serial # named - " & duplicatearray(1, dupecounter) & ", SSID = " & duplicatearray(0, dupecounter) & ", Serial = " & duplicatearray(2, dupecounter)
                  Next
            End If

WScript.Echo  Err.Number
WScript.Echo "I made it to the end of the function"
End Function
********************

Complete script below..

********************

On Error Resume Next

      WScript.echo ("Began IMS Duplicate run at: " & (FormatDateTime(Now(),3)))
      ' Open DB connection
Const adOpenStatic = 3
Const adLockOptimistic = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
'get list of unique serves.
objConnection.Open _
    "Provider=SQLOLEDB;Data Source=mcc-csc-04;" & _
        "Trusted Connection=Yes;Initial Catalog=CIASATS;" & _
             "User ID=xxx;Password=xxx;"
objRecordSet.Open "SELECT IMSAssetsTemp.SS_ID, IMSAssetsTemp.[Asset Name], IMSAssetsTemp.[Hardware Serial Number] FROM IMSAssetsTemp INNER JOIN Tracking ON IMSAssetsTemp.[Asset Name] = Tracking.ServerName where imsassetstemp.product = 'server' and (IMSAssetsTemp.[Hardware Serial Number] <> '' or IMSAssetsTemp.[Hardware Serial Number] is not null) ORDER BY IMSAssetsTemp.[Asset Name]", objConnection, adOpenStatic, adLockOptimistic
'dump recordset into Array
serverArray = objrecordset.getrows()
'release recordset
objrecordset.close
'----------------

'define boundaries of Array
numOFservers = UBound(serverarray,2)
WScript.Echo "# of records returned by query " & numOFservers
' begin to iterate through

For servercounter = 0 To numOFservers
coreserial = ""
adjustedserial = ""
    WScript.Echo "Asset with SSID - " & serverarray(0,servercounter) & " Name = " & serverarray(1,servercounter) & " and Serial # " & serverarray(2,servercounter) & " - looking for duplicates"
      coreserial = serverarray(2,servercounter)
      If instr(coreserial, "-") Then
            adjustedserial = replace(serverarray(2,servercounter),"-", "")
      Else
            ' this adds a dash to serial number, as its not always recorded properly
            'thelength = len(coreserial)
            'theleft = Left(coreserial,2)
            'theright = Right(coreserial, thelength -2)
            adjustedserial = Left(coreserial,2) & "-" & Right(coreserial, (len(coreserial) -2))
            'WScript.Echo "Serial #'s to check " & coreserial & "," & adjustedserial
      End If
dupes = checkfordupes(serverarray(0,servercounter), serverarray(1,servercounter), coreserial, adjustedserial)
Next
WScript.Echo Err.number
WScript.Echo Err.Description
WScript.Echo Err.Source
wscrtip.echo Err.HelpFile
      WScript.echo ("Finished IMS duplicate run at: " & (FormatDateTime(Now(),3)))
      '--------------------------- End of regular code, functions below.
Function checkfordupes(ssid,assetname,serial1,serial2)
WScript.Echo "Hello"
Err.Number = 0
    querystring = "SELECT IMSAssetsTemp.SS_ID, IMSAssetsTemp.[Asset Name], IMSAssetsTemp.[Hardware Serial Number] " & _
                "FROM IMSAssetsTemp where (IMSAssetsTemp.[Asset Name] <> '" & assetname & _
                "') AND ((IMSAssetsTemp.[Hardware Serial Number] = '" & serial1 & "' OR IMSAssetsTemp.[Hardware Serial Number] = '" & _
                serial2 & "'))"
    WScript.Echo "Query = " & querystring
          objRecordSet.Open querystring, objConnection, adOpenStatic, adLockOptimistic
            WScript.echo Err.number
            duplicatearray = objrecordset.getrows()
            If Err.Number <> 0 Then
            WScript.echo Err.number
                  'performed if NO dupes are found
                  WScript.Echo "NO DUPLICATES found for Serial #'s "& coreserial & "," & adjustedserial & " for asset named - " & serverarray(1,servercounter)

            Else
            WScript.echo Err.number
            'performed if duplicate records are found
                  objrecordset.close
                  numOFdupes = UBound(duplicatearray,2)
                  For dupecounter = 0 To numOFdupes
                        WScript.Echo serverarray(1,servercounter) & " has asset records with a matching or similar serial # named - " & duplicatearray(1, dupecounter) & ", SSID = " & duplicatearray(0, dupecounter) & ", Serial = " & duplicatearray(2, dupecounter)
                  Next
            End If

WScript.Echo  Err.Number
WScript.Echo "Goodbye"
WScript.Echo "---------------------------------------------------------------------------------------------------"

End Function
0
Comment
Question by:jiriki76
  • 2
3 Comments
 
LVL 28

Accepted Solution

by:
AzraSound earned 125 total points
ID: 17990246
Try putting On Error Resume Next inside your function as well.
0
 

Author Comment

by:jiriki76
ID: 17990511
Turns out...  you were right, but that only fixed the error where it kicked out of the function..

the 3705 error was coming from (and it was easy to see, once i put the on error resume next inside the function)... when no dupes were found (error condition was true), i never closed the recordset out.  I was only closing it when itactually had records.  Hence the issue.

So two parts, but that led me down the right path.  Thanks a million.
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 17990594
The reason is the scope of the error handling.  Having On Error Resume Next outside of your function calls means that as soon as an error happens within your function (where there was no error handling) it jumped out and was handled by your global On Error Resume Next.  By placing it inside the function too, it allowed the function to continue to the next line, as well.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question