CODEWITHSUNDEEP
vbscriptasp-classicado
Ian Warburton
Ian Warburton

Reputation: 15676

Cannot change the ActiveConnection property of a Recordset object

I'm trying to open a recordset such that I can get an accurate value for RecordCount. I have some code that successfully does this by using Recordset.Open with the adOpenStatic and adLockReadOnly constants. However, when I use the following code.

With cmd
    .ActiveConnection = db
    .CommandType = adCmdStoredProc
    .CommandText = "doGetBookingCrewDetails"

    .Parameters.Append .CreateParameter("@bookingid", adInteger, adParamInput)
    .Parameters("@bookingid") = 42943

    .Parameters.Append .CreateParameter("@starttime", adDBTimeStamp, adParamInput)
    .Parameters("@starttime") = "07/10/2016 00:00"
End With

Set rsCr = Server.CreateObject("ADODB.Recordset")
rsCr.Open cmd, db, adOpenStatic, adLockReadOnly

I get the error

0x800a0e7b - ADODB.Recordset: Cannot change the ActiveConnection property of a Recordset object which has a Command object as its source.

The above code returns the correct set of results when using Command.Execute, but then I can't specify the constants that seem to make the row count property work.

How do I make this work?

Upvotes: 2

Views: 4291

Answers (1)

Nick.Mc
Nick.Mc

Reputation: 19184

Here are some points from Open Method (ADO Recordset):

The ActiveConnection property is read-only for Recordset objects whose Source property is set to a valid Command object, even if the Recordset object is not open.

and

the activeconnection parameter is optional.

also

If you pass a Command object in the Source argument and also pass an ActiveConnection argument, an error occurs. The ActiveConnection property of the Command object must already be set to a valid Connection object or connection string

So in short, try not passing db into rsCr.open at all.

Something like this: 

rsCr.Open cmd, , adOpenStatic, adLockReadOnly

Upvotes: 2

Related Questions