Solved

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

Posted on 2006-11-21
3
1,714 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

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…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…

705 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