Command Object, Parameters, Set Cursor Type. etc?

B

+Bob+

I have a command procedure that previously used an RS.opn statement
where I could specify cursor type, lock type, etc. I need to use a
stored procedure now with a command object so that I can pass some
parameter information to the stored proc.

Is there any way to set cursor type and lock type with a command
object? Or is there some other way to call a stored proc and pass a
parameter that allows for setting these features ?

Thanks,
 
B

Bob Barrows [MVP]

+Bob+ said:
I have a command procedure that previously used an RS.opn statement
where I could specify cursor type, lock type, etc. I need to use a
stored procedure now with a command object so that I can pass some
parameter information to the stored proc.

Is there any way to set cursor type and lock type with a command
object? Or is there some other way to call a stored proc and pass a
parameter that allows for setting these features ?
You can still use the recordset open method with a command object:
rs.open cmd

See:
http://msdn.microsoft.com/library/en-us/ado/html/3236749c-4b71-4235-89e2-ccdfaaa9319d.asp

If you don't have output parameters, and you don't need to read the Return
value, you don't even neeed to use a Command object. See:
http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/5d3c9d4409dc1701?hl=en&
 
B

+Bob+

You can still use the recordset open method with a command object:
rs.open cmd

See:
http://msdn.microsoft.com/library/en-us/ado/html/3236749c-4b71-4235-89e2-ccdfaaa9319d.asp

Bob:

So, I can setup the command object and parameters for the command
object, like this:

Set objComm = server.CreateObject("ADODB.Command")
objComm.CommandText = "myStoredProc"
objComm.CommandType = adCmdStoredProc
passedStaff_ID = Request.Querystring("Staff_ID")
Set passedParam1 = _
objComm.CreateParameter ("LocationCode", adUnsignedInt,
adParamInput,,passedStaff_ID)
objComm.Parameters.Append(passedParam1)

Then, instead of doing this as I am now:

' Create recordset by executing the command - get staff record
Set objComm.ActiveConnection = Conn
Set myRST = objComm.Execute

I can do this ?:
Set objComm.ActiveConnection = Conn
Set RS = server.CreateObject("ADODB.Recordset")
RS.open ObjComm ,, adOpenForwardOnly,adLockReadOnly,

I am a little confused as to the exact syntax for the command object
in the RS.open statement - can I use it just "bare" like this as the
command object itself ?

Also, can I simply leave the Connection Object out of the RS.Open
statement since it's already assigned to the command object via the
objComm.ActiveConnection?

Conversely, could I skip that assignment of the ActiveConection
property to the command object and just put the Conn object in as the
second parameter in the RS.Open statement ? As I would normally do
with an RS.open and no command object use).

Bob

Thanks,
Bob
 
B

Bob Barrows [MVP]

+Bob+ said:
http://msdn.microsoft.com/library/en-us/ado/html/3236749c-4b71-4235-89e2-ccdfaaa9319d.asp

Bob:

So, I can setup the command object and parameters for the command
object, like this:

Set objComm = server.CreateObject("ADODB.Command")
objComm.CommandText = "myStoredProc"
objComm.CommandType = adCmdStoredProc
passedStaff_ID = Request.Querystring("Staff_ID")
Set passedParam1 = _
objComm.CreateParameter ("LocationCode", adUnsignedInt,
adParamInput,,passedStaff_ID)
objComm.Parameters.Append(passedParam1)

Then, instead of doing this as I am now:

' Create recordset by executing the command - get staff record
Set objComm.ActiveConnection = Conn
Set myRST = objComm.Execute

I can do this ?:
Set objComm.ActiveConnection = Conn
Set RS = server.CreateObject("ADODB.Recordset")
RS.open ObjComm ,, adOpenForwardOnly,adLockReadOnly,

Yes (get rid of the ending comma). Actually, since those settings are
default, there is no need to go to this trouble.
Set myRST = objComm.Execute will produce the same result
I am a little confused as to the exact syntax for the command object
in the RS.open statement - can I use it just "bare" like this as the
command object itself ?

Yes. You did not read my link. It contains the following:
Source
Optional. A Variant that evaluates to a valid Command object, an SQL
statement, a table name, a stored procedure call, a URL, or the name of
a file or Stream object containing a persistently stored Recordset.
Also, can I simply leave the Connection Object out of the RS.Open
statement since it's already assigned to the command object via the
objComm.ActiveConnection?
Yes

Conversely, could I skip that assignment of the ActiveConection
property to the command object and just put the Conn object in as the
second parameter in the RS.Open statement ? As I would normally do
with an RS.open and no command object use).
No. You did not read my link. It contains the following note:
Note
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,734
Messages
2,569,441
Members
44,832
Latest member
GlennSmall

Latest Threads

Top