The connection cannot be used to perform this operation.

[vshunjan]

I am using a command object to call a stored procedure which returns a recordset.
It works alright at times but sometimes comes up with this error -
3709 : The connection cannot be used to perform this operation. It is either closed or invalid in this context

How do i resolve this ?

 

[Sammy145]

all i can suggest is post your code and try pasting the error in

http://www.google.com

what mdac version have you got
what version of sql

your are not alone in this problem

 

[vshunjan]

I am using SQL SERVER 2000
and MDAC 2.7
Connection made are through System DSN
using SQL Server Drivers

************* CODE *****************


Dim objCmd
Dim objRS
Dim lng_Counter
Dim arrParam

g_strLastSP = strSP

Set objRS = server.CreateObject("ADODB.RecordSet&quot
Set objCmd = server.CreateObject("ADODB.Command&quot

With objCmd
On Error Resume Next
.ActiveConnection = m_strConnectionString
.CommandTimeOut = 3000
if Err.number <> 0 then
SendMessage &quot;Cannot open database on connection '&quot; & m_strConnectionString & &quot;'&quot;, &quot;DBConnection.RunSP&quot;
end if
On Error Goto 0

.CommandType = adCmdStoredProc
.CommandText = strSP
End With

' Attach parameters
Call AttachParams(objCmd, arrParams)

On error resume next
With objRS
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic

' Run the command
.Open objCmd
if err.number <> 0 then
SendMessage Err.number & &quot; : &quot; & Err.Description
end if
On Error goto 0
end if
End With
on error goto 0
Set objCmd = Nothing

 

[Sammy145]

you say it only happens sometimes have or can you try using a DSNless Connection (shouldnt make a differnce BUT you never know can you also try reinstalling MDAC)

sam