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

Hi there

I get the above message when trying to write to a SQL 2000 database. Can
anyone perhpas shed some light on this for me?

In theory I am tryng to create a generic subroutine which I can simply pass
my INSERT/UPDATE/DELETE statements to, and then have it executed. The
following subroutine gets called for each server that I am running through.
The error above is returned on the line "objRecordSet2.Close"

My code excerpt is as follows:
Sub AccessDB()
' Set the database connection information
sServer = "test"
sLogin = "logon"
sPwd = "pwd"

' Create the ADO Connection and Recordset objects.
Set objConnection2 = CreateObject( "ADODB.Connection" )
Set objRecordSet2 = CreateObject( "ADODB.Recordset" )

' Set the connection string, open the connection and execute the statement
objConnection2.ConnectionString = "PROVIDER=SQLOLEDB" & _
";SERVER=" & sServer & _
";UID=" & sLogin & _
";PWD=" & sPwd & _
";DATABASE=ServerSupport"
WScript.Echo & SQLString
objConnection2.open
objRecordSet2.Open SQLString, objConnection2

objRecordSet2.Close
objConnection2.Close
End Sub

Bob
Tue Nov 30 09:08:22 CST 2004

William Robertson wrote:
> Hi there
>
> I get the above message when trying to write to a SQL 2000 database.
> Can anyone perhpas shed some light on this for me?
>
> In theory I am tryng to create a generic subroutine which I can
> simply pass my INSERT/UPDATE/DELETE statements to, and then have it
> executed. The following subroutine gets called for each server that I
> am running through. The error above is returned on the line
> "objRecordSet2.Close"
>
> My code excerpt is as follows:
> Sub AccessDB()
> ' Set the database connection information
> sServer = "test"
> sLogin = "logon"
> sPwd = "pwd"
>
> ' Create the ADO Connection and Recordset objects.
> Set objConnection2 = CreateObject( "ADODB.Connection" )
> Set objRecordSet2 = CreateObject( "ADODB.Recordset" )
>
> ' Set the connection string, open the connection and execute the
> statement objConnection2.ConnectionString = "PROVIDER=SQLOLEDB" & _
> ";SERVER=" & sServer & _
> ";UID=" & sLogin & _
> ";PWD=" & sPwd & _
> ";DATABASE=ServerSupport"
> WScript.Echo & SQLString
> objConnection2.open
> objRecordSet2.Open SQLString, objConnection2
>
> objRecordSet2.Close
> objConnection2.Close
> End Sub

If your sql string is an insert/update/delete statement, then it will not
return a recordset. DON'T USE A RECORDSET TO EXECUTE A SQL STATEMENT THAT
DOES NOT RETURN RECORDS.


objConnection2.open
objConnection2.Execute SQLString,,129
objConnection2.Close: Set objConnection2 = Nothing

((the 129 tells ADO that you are executing text string containing a sql
statement that does not return records, which prevents ADO from
instantiating a recordset object implicitly. For further explanation, look
up CommandOptionEnum and ExecuteOptionEnum at msdn.microsoft.com/library)

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Richard
Tue Nov 30 09:12:42 CST 2004

Hi,

The code you have looks OK to me, but you don't give us the value of
SQLString. When I modify tables, I use a Command object. I bind, assign the
ActiveConnection and CommandText properties, then invoke the Execute method.

Set objCommand = CreateObject("ADODB.Command")
objCommand.ActiveConnection = objConnection2
objCommand.CommandText = SQLString
objCommand.Execute

--
Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab web site - http://www.rlmueller.net
--
"William Robertson" <robertson.william@columbus.co.za> wrote in message
news:suGdnVfSpd6YGjHcRVn-uw@is.co.za...
> Hi there
>
> I get the above message when trying to write to a SQL 2000 database. Can
> anyone perhpas shed some light on this for me?
>
> In theory I am tryng to create a generic subroutine which I can simply
pass
> my INSERT/UPDATE/DELETE statements to, and then have it executed. The
> following subroutine gets called for each server that I am running
through.
> The error above is returned on the line "objRecordSet2.Close"
>
> My code excerpt is as follows:
> Sub AccessDB()
> ' Set the database connection information
> sServer = "test"
> sLogin = "logon"
> sPwd = "pwd"
>
> ' Create the ADO Connection and Recordset objects.
> Set objConnection2 = CreateObject( "ADODB.Connection" )
> Set objRecordSet2 = CreateObject( "ADODB.Recordset" )
>
> ' Set the connection string, open the connection and execute the
statement
> objConnection2.ConnectionString = "PROVIDER=SQLOLEDB" & _
> ";SERVER=" & sServer & _
> ";UID=" & sLogin & _
> ";PWD=" & sPwd & _
> ";DATABASE=ServerSupport"
> WScript.Echo & SQLString
> objConnection2.open
> objRecordSet2.Open SQLString, objConnection2
>
> objRecordSet2.Close
> objConnection2.Close
> End Sub
>
>
>

William
Wed Dec 01 15:18:11 CST 2004

Hi Bob,

Thanks for your reply. I hear what you are saying, and will investigate the
correct method to perform such an action.

Sorry 'bout the newbie mistake... still trying to learn how to do things by
getting code excerpts from books & tutorials... I guess I have just outlined
the main problem with this method of leraning though, haven't I :)

Cheers
William R.

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:Odam65u1EHA.824@TK2MSFTNGP11.phx.gbl...
> William Robertson wrote:
>> Hi there
>>
>> I get the above message when trying to write to a SQL 2000 database.
>> Can anyone perhpas shed some light on this for me?
>>
>> In theory I am tryng to create a generic subroutine which I can
>> simply pass my INSERT/UPDATE/DELETE statements to, and then have it
>> executed. The following subroutine gets called for each server that I
>> am running through. The error above is returned on the line
>> "objRecordSet2.Close"
>>
>> My code excerpt is as follows:
>> Sub AccessDB()
>> ' Set the database connection information
>> sServer = "test"
>> sLogin = "logon"
>> sPwd = "pwd"
>>
>> ' Create the ADO Connection and Recordset objects.
>> Set objConnection2 = CreateObject( "ADODB.Connection" )
>> Set objRecordSet2 = CreateObject( "ADODB.Recordset" )
>>
>> ' Set the connection string, open the connection and execute the
>> statement objConnection2.ConnectionString = "PROVIDER=SQLOLEDB" & _
>> ";SERVER=" & sServer & _
>> ";UID=" & sLogin & _
>> ";PWD=" & sPwd & _
>> ";DATABASE=ServerSupport"
>> WScript.Echo & SQLString
>> objConnection2.open
>> objRecordSet2.Open SQLString, objConnection2
>>
>> objRecordSet2.Close
>> objConnection2.Close
>> End Sub
>
> If your sql string is an insert/update/delete statement, then it will not
> return a recordset. DON'T USE A RECORDSET TO EXECUTE A SQL STATEMENT THAT
> DOES NOT RETURN RECORDS.
>
>
> objConnection2.open
> objConnection2.Execute SQLString,,129
> objConnection2.Close: Set objConnection2 = Nothing
>
> ((the 129 tells ADO that you are executing text string containing a sql
> statement that does not return records, which prevents ADO from
> instantiating a recordset object implicitly. For further explanation, look
> up CommandOptionEnum and ExecuteOptionEnum at msdn.microsoft.com/library)
>
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Bob
Wed Dec 01 16:06:42 CST 2004

William Robertson wrote:
>
> Sorry 'bout the newbie mistake...

No need to apologize. I was emphasizing, not shouting out of anger. I was
there at one time myself.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"