Solved

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

Posted on 2006-11-21
3
1,708 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

860 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