Solved

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

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

758 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now