Recordset Operation is Not allowed when object is closed VBS

Safinn picture Safinn · Mar 13, 2014 · Viewed 11.2k times · Source

In the code bellow I get an error on the line reading recset.Close.

Char: 5
Error: Operation is not allowed when the object is closed.
Code: 800A0E78
Source: ADODB.Recordset

If the program reaches the line PQ_ID_number = InputBox("Enter PQ Database ID number, Do not use / ? < > \ : * | !", "PQ ID Number", "Replace Text") it seems to work fine (taking manual input) but when it tries to get the ID from the URL of a browser (automaticaly) it gives the error.

valid_name = 0
Dim objInstances, objIE, counterTab

Do While valid_name = 0  'Flag to exit the loop if the Id  number has content in the SQL Database'

    '-----------------------------------------------------------------------------------------'

    Set objInstances = CreateObject("Shell.Application").windows
    If objInstances.Count > 0 Then '/// make sure we have IE instances open.
        'Loop through each tab.'
        For Each objIE In objInstances
            'Split the url of current tab using /'
            splitURL = Split(objIE.LocationURL,"/")
            'Count how many sub strings are in the URL when split'
            counter = UBound(splitURL)
            if counter = 7 Then
                lastSplit = Split(splitURL(7),".")
                lastURL = splitURL(0) & "//" & splitURL(2) & "/" & splitURL(3) & "/" & splitURL(4) & "/" & splitURL(5) & "/" & splitURL(6) & "/" & lastSplit(0)
                if lastURL = "URL" Then
                    Set IE = objIE
                    counterTab = counterTab + 1
                end if
            end if
            'End if
        Next
    Else
        'If no internet explorer window is open.'
        MsgBox "No Internet Explorer window found."
        wscript.quit
    End if

    'Check if no [] is open in internet explorer'
    if IsObject(IE) Then

        url = Split(IE.LocationURL,"=")
        url2 = Split(url(1),"&")
        PQ_ID_number = url2(0)

    else

        MsgBox "No incident found."
        wscript.quit

    end if

    'counterTab counts how many [] are open. If there is more than 1, ask for confirmation of last [] number.'
    if counterTab > 1 Then

        confirm = msgbox("Please confirm Incident ID: " & incidentID,vbYesNo,"Confirmation")
        'If no is pressed, ask for manual entry.'
        if confirm = vbNo Then

            PQ_ID_number = InputBox("Enter PQ Database ID number, Do not use / ? < > \ : * | !", "PQ ID Number", "Replace Text")
            On Error Resume Next
            If PQ_ID_number = False Then
                wscript.quit
            End If

        end if

    end if

    '-----------------------------------------------------------------------------------------'

    'Open connection in Database'
    dbConnectStr = "connection string"
    Set con = CreateObject("ADODB.Connection")
    Set recset = CreateObject("ADODB.Recordset")
    con.Open dbConnectStr

    'Get PQ Database title and status of incident number provided.
    SQL_String = "Select title, status_id from incident_TBL where incident_ID = " & PQ_ID_number
    recset.Open SQL_String, con
    title = recset.Fields(0).Value
    incidentStatus = recset.Fields(1).Value
    con.Close
    recset.Close

    If title = False Then 'check if PQ_ID given has content in SQL Database
        wscript.echo "Invalid PQ Database ID number, please type correct number"
                valid_name = 0
    Else
                valid_name = 1
    End If

Loop

Thanks for the help!

Answer

MikkaRin picture MikkaRin · Mar 13, 2014

you need close Recordset first and only after that close connection

 con.Close
 recset.Close

change to:

 recset.Close
 con.Close