CODEWITHSUNDEEP
vbscriptconnectionado
Satyendra
Satyendra

Reputation: 1705

Multiple ADODB.Connection in vbScript

I have an array of database servers, and want to execute the same query for all of them in a loop.
But after the first iteration the I get following error :
- Error Number: 3704
- Description: Operation is not allowed when the object is closed

The code that I've implemented is: 



     Dim username
     Dim password
     Dim serverList(4)
     serverList(0) = "ServerAddress0"
     serverList(1) = "ServerAddress1"
     serverList(2) = "ServerAddress2"
     serverList(3) = "ServerAddress3"

     'username and password are properly set
     for counter = 0 to UBound(serverList)-1
       Set connObj = CreateObject("ADODB.Connection")
       Set rsObj = CreateObject("ADODB.Recordset")
       connString = ..........
       connObj.Open connString, username, password
       'ERROR comes here, in second iteration.

       sqlScript = "SELECT * FROM ......"
       rsObj.open sqlScript, connObj

       Do While Not rsObj.EOF
          'record set is fetched.....
          rsObj.MoveNext
       Loop

       'current connection is closed
       rsObj.close
       connObj.close

     Next

Note: For the first iteration this code works perfectly. Error come for second iteration.

Upvotes: 0

Views: 3331

Answers (2)

Satyendra
Satyendra

Reputation: 1705

I just found the solution for this issue:
While iterating over the servers, I was also trying to close connection object for each connection.

   ......
   rsObj.close
   connObj.close

Next 'end of iteration over servers.

Now I moved the close statements out of the loop. i.e. while iterating let the objects be open.

   ......
Next 'end of iteration over servers.

rsObj.close
connObj.close

So, it was all related to the open/close state of the connection.

Upvotes: 1

Ekkehard.Horner
Ekkehard.Horner

Reputation: 38765

(0) Disable "On Error Resume Next" (if you use it)

(1) As UBound() returns the last index, not the size, change

for counter = 0 to UBound(serverList)-1

to

for counter = 0 to UBound(serverList)

(2) As it's possible that you messed up the connection string, publish real code for

connString = ..........

(3) As VBScript has Until change

Do While Not rsObj.EOF

to

Do Until rsObj.EOF

(4) As your recordset is named rsObj change

rsNice.MoveNext

to

rsObj.MoveNext

If you did construct the connection string properly, (4) is the cause of the error.

Upvotes: 1

Related Questions