How to use ADO Recordset.open method to execute a command object

sramkris
sramkris used Ask the Experts™
on
hey all

I am using an ASP page in which i am passing ADO Recordset values to Crystal Reports at runtime..I am using a command object to fire off a stored proc..and the return value is stored in a recordset which is passed to the reports. Right now if i set the connection to close the subreports come out blank..I am using the execute method

Set session("oADORs") = Server.CreateObject("ADODB.Recordset")

CmdProd.Parameters.Append CmdProd.CreateParameter ("@CustID",adGUID,adParamInput, ,customerid)
CmdProd.Parameters.Append CmdProd.CreateParameter ("@FYID",adGUID,adParamInput, ,fiscalyearid)
CmdProd.Parameters.Append CmdProd.CreateParameter ("@FPID",adGUID,adParamInput, ,dropdownval4 )
CmdProd.Parameters.Append CmdProd.CreateParameter ("@Acctid",adGUID,adParamInput, ,dropdownval2)

set session("oADORs") = CmdProd.execute

I need to use the

oADORecordset.ActiveConnection = oADOConnection
oADORecordset.Open("Select [Employee ID], [First Name], [Last Name] From Employee")
oADORecordset.ActiveConnection = Nothing

oADOConnection.Close
set oADOConnection = nothing

but i have no idea how to use the open method with a command object..I need to use this method for my reports to work properly..

Thanks
Ram
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
There is nothing like open property for command object.
If you want to get recordset from command object you have to write a stored procedure having statement like :
   select * from table_name.

use this code for defining session("oADORs") before taking any recordset into it..

dim rsObj
set rsObj=server.createobject("adodb.recordset")
set session("oADORs")=rsObj

eNjOy!!

Author

Commented:
Hey

My question is about the open property of the recordset object.. I need to use the open property of the recordset object..i am not sure if you can say
oADORecordset.open = cmdArea.Execute

I need to use the OADOrecordset.open statement and call the command object...

I hope this helps
Thanks
Ram

Commented:
Did I forget to tell u..

.open method of recordset object helps in executing a SQL query to open a recordset and returns recordset to the recordset object so u need a SQL STATEMENT with open method of recordset.
AND
.execute method of command object is used to execute a stored procedure and it might or might not return a recordset depending upon ur stored procedure.
Incase u use .execute method and it returns a recordset then u are left only to set a recordset object to it.. like this
set rs=commandobj.execute().

hence u cant use recordsets open method with command's execute method..
hope this answers all.

eNjOy!!
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
To open the recordset with a command object, simply pass the command as parameter:
oADORecordset.open = cmdArea

Davee: the .Execute() function has the disadvantage that the recordset will ALWAYS be clientside, forwardonly and readonly. Using the open() method of the recordset, you can configure the recordset options as needed

CHeers

Commented:
Exactly it is.. but not when we explicit define those properties as per needs..

set rsobj=server.createobject("adodb.recrodset")
rsobj.cursorlocation=aduseserver
rsobj.cursortype=adOpenDynamic
rsobj.locktype=adlockoptimistic

now use
set rsobj=cmdAction.execute()

Angellll: what do u think abt it?
Infact can u put a working example for using command objec the way u told?

Commented:
I suppose this is one way..

set rsobj=server.createobject("adodb.recordset")

rsobj.source=commandObj
'no activeconnection as it becomes readonly for source equals to command object
rsobj.options=adCmdStoredProc
rsobj.open

else

rsobj.open commandObj,,adOpenDynamic,adlockOptimistic,adcmdStoredProc

try it now..

eNjOy!!
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
if you use this:
set rsobj=server.createobject("adodb.recrodset")
rsobj.cursorlocation=aduseserver
rsobj.cursortype=adOpenDynamic
rsobj.locktype=adlockoptimistic

now use
set rsobj=cmdAction.execute()

then the last line will create a completely new recordset object!

CHeers

Author

Commented:
Hey all

Thanks for your reply but i am a novice as compared to u guys so i just want to make sure that i get this thing right...Could any of u sum it up and let me know as to what steps i should follow to achieve what i have mentioned above then that would solve a major problem..Since there are a lot of options given i am a bit confused as to which steps should be taken to get the right solution.

I really appreciate the effort you have given to solve this problem..

Thanks
Ram
Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
To open the recordset with a command object, simply pass the command as parameter:

'create the recordset with the properties requested
set rsobj=server.createobject("adodb.recordset")
rsobj.cursorlocation=aduseserver
rsobj.cursortype=adOpenDynamic
rsobj.locktype=adlockoptimistic
'get the data
oADORecordset.open = cmdArea

Note that after this call, you might need to check if the properties are still correct (locktype for example), as they can be changed ...

CHeers

Author

Commented:
Hey guys
I will be testing this code by Friday so please bear with me.. I will post another message or accept the ans after i test this code..

Thanks a lot for your help
Ram

Author

Commented:
Thanks All for your response..I got that thing to work..

Here is my code
Set oADORs = Server.CreateObject("ADODB.Recordset")
oADORs.cursortype = 2
oADORs.cursorlocation = 3
oADORs.Open cmdProd, ,adOpenDynamic,adLockOptimistic  

cmd Prod is a command object

Thanks
Again
Ram

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial