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!
you need close Recordset
first and only after that close connection
con.Close
recset.Close
change to:
recset.Close
con.Close