asked on

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

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

Davee

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!!

sramkris

ASKER

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

Davee

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!!

Guy Hengel [angelIII / a3]

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

Davee

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?

Davee

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]

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

sramkris

ASKER

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

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sramkris

ASKER

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

sramkris

ASKER

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