ADO - Operation is not allowed when object is closed

[michaelcoleman]

I'm trying to connect to SQL server:

Set mvarOCN = New ADODB.Connection

mvarOCN.Open "Driver={SQL SERVER};Server=" & mvarserver & ";database=" & mvardatabase & ";Trusted_Connection=yes;"

Set mvarRS = mvarOCN.Execute(sqlStatement)

when my sqlStatement = "Select * from testrun"
everthing works good.

when sqlStatement =
"-- Declare Variables
DECLARE @STARTDATE datetime
DECLARE @ENDDATE datetime
DECLARE @TESTERTYPE varchar(50)

-- Initialize the Parameters
SELECT @STARTDATE = '2003-12-11'
SELECT @ENDDATE = '2003-12-18'
SELECT @TESTERTYPE = 'Chassis'

-- Get the Data
select a.testdate+a.testtime"StartDate", a.esn, b.stationnetid, a.testcomplete
from testrun a, "test station" b
where
a.keystationid = b.keystationid
and a.testdate between @STARTDATE and @ENDDATE
and a.testertype =@TESTERTYPE
and a.keytestid = (select MAX(aa.keytestid) from testrun aa where a.esn = aa.esn)
order by a.testcomplete"

I get an Error in the recordset "Operation is not allowed when object is closed in the mvarrs record set object.

This had worked fine with this connection string:
Provider=SQLOLEDB;Server=" & mvarserver & ";database=" & mvardatabase & ";Trusted_Connection=yes;"

but sqloledb doesn't allow mutliple connections.

Please help

MJC

 

[Glasgow]

>but sqloledb doesn't allow mutliple connections

Are you sure? I didn't know that and swear I have not encountered a problem.

 

[michaelcoleman]

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/adoprg01_0ahx.asp

is where it said about not allowing multiple connections.