CODEWITHSUNDEEP
sql-servervbscriptadodb
Rick
Rick

Reputation: 43

ADODB.Recordset: Operation is not allowed when the object is closed

I receive the error - ADODB.Recordset: Operation is not allowed when the object is closed. when using a multiple table sql statement.

other sql statements (simple ones) I do not receive and error, and I have copied the sql statement into query to verify it works ok in MSSQL from printed out sql variable - and it does return values.

Method

sql = "SET NOCOUNT ON;SET ANSI_WARNINGS OFF;use dbcheck; " &_
"select year([00080020]) as YEAR, month([00080020]) as MONTH,sum(bytesize/1024/1024/1024) as GBStored, count(distinct [0020000d]) " &_
" as FinalCount from tblt t, tbls s, tblf f where" &_
" id1 = _id1 and id2 = _id2file" &_
" and [00080020] is not null" &_
" group by year([00080020]), month([00080020]) order by year([00080020]) desc, month([00080020]) desc"

Dim cndb : Set cndb = CreateObject("ADODB.Connection")
Dim rsdb: Set rsdb = CreateObject("ADODB.Recordset")
cndb.Provider = "sqloledb"
cndb.Properties("Data Source").Value = "127.0.0.1"
cndb.Properties("Initial Catalog").Value = "master"
cndb.Properties("Integrated Security").Value = "SSPI"

cndb.CursorLocation = 3
cndb.Open
set rsdb = CreateObject ("ADODB.Recordset")
cmd.ActiveConnection = cndb
cmd.CommandType = 1
cmd.CommandText = sql
rsdb.CursorLocation = 3
rsdb.CursorType = 3
rsdb.LockType = 3
Set rsdb = cmd.Execute
    IF Not (rsdb IS Nothing) Then
    rsdb.MoveFirst ** ERRORS OUT HERE

Thanks --------2nd attempt sql is same

Dim cndb : Set cndb = CreateObject("ADODB.Connection")
Dim rsdb: Set rsdb = CreateObject("ADODB.Recordset")

cndb.Provider = "sqloledb"
' Set SQLOLEDB connection properties.
cndb.Properties("Data Source").Value = "127.0.0.1"
cndb.Properties("Initial Catalog").Value = "master"

' Windows NT authentication.
cndb.Properties("Integrated Security").Value = "SSPI"

cndb.CommandTimeout=900
cndb.CursorLocation = 3
cndb.Open

rsdb.CursorLocation = 3
rsdb.CursorType = 3
rsdb.LockType = 3
rsdb.Open sql,cndb

rptFile.WriteLine sql
rptFile.WriteLine cndb.State
rptFile.WriteLine rsdb.State

IF Not (rsdb IS Nothing) Then
  If Not rsdb.EOF Then  * Errors out on this line
    rsdb.MoveFirst

Upvotes: 2

Views: 16654

Answers (2)

Rick
Rick

Reputation: 43

I found my issue - in the SQL statement I was had use dbcheck trying to select it with use statement worked in SQL QUERY ANALYZER - just not in the script, I changed the Initial Catalog to dbcheck and removed the "use" from sql and it worked.

Thanks everyone for your help.

Upvotes: 1

Mathieu Guindon
Mathieu Guindon

Reputation: 71157

I think you're receiving an empty recordset.

Set rsdb = cmd.Execute
If Not (rsdb IS Nothing) Then
    rsdb.MoveFirst ** ERRORS OUT HERE

You can't move to first record if rsdb.EOF returns True. Try this:

Set rsdb = cmd.Execute
If Not (rsdb Is Nothing) Then
    If Not rsdb.EOF Then
        rsdb.MoveFirst
        ...
    End If
End If

And then diagnose your query and see if it actually returns anything. This might help:

Echo sql

That said, you're setting the recordset's reference 3 times, and discarding the previous reference every time.

Dim rsdb: Set rsdb = CreateObject("ADODB.Recordset")

That Set statement is superfluous, because...

cndb.Open
set rsdb = CreateObject ("ADODB.Recordset")

You're setting it again here. But that Set statement is also superfluous, because...

Set rsdb = cmd.Execute

That's the only reference assignment that actually needs to happen. The CursorLocation and other properties you're setting, are discarded along with the discarded reference. Remove them too.

Upvotes: 1

Related Questions