March 26, 2003 at 9:16 am
How do I set the Cursor Type, Cursor Location and other properties for a Recordset Object which is returned by Command Object
March 26, 2003 at 9:31 am
If you are using a command object, then you are not especting any result from SQL.
If you are going to execute something that will return a result, then use the recordset object, not a command object.
For Example:
BACKUP DATABASE ...: use command object
SELECT * FROM .....: use recordset object
March 26, 2003 at 9:33 am
I am using Command Object bcos I want Recordset and Output Parameter from a SP
March 26, 2003 at 9:51 am
Hmmm, using the Command object does not mean you are not recieving results. If you use :
'no records, make sure to use the adExecuteNoRecords if not expecting any records, it speeds up the query
Command.Execute, , adExecuteNoRecords
'returning records :
Set RecordSet = Command.Execute
From MSDN :
quote:
To work with records in a database by using Visual Basic or VBScript code, you use ADO Recordset objects. A Recordset object represents the records from a single table or the set of records returned by executing a command, such as an SQL string, an Access query, or a SQL Server stored procedure.You can open a Recordset object in ADO by using any of these three methods:
The Execute method of the Connection object
The Execute method of the Command object
The Open method of the Recordset object
The syntax for each of these methods is as follows:
Set recordset = connection.Execute ([CommandText, [RecordsAffected, [Options]]])
Set recordset = command.Execute ([RecordsAffected, [Parameters, [Options]]])
recordset.Open [Source, [ActiveConnection, [CursorType, [LockType, [Options]]]]]
Although using the Execute method of the Connection or Command object returns a Recordset object, these methods are primarily intended for executing commands (typically SQL strings) that don't return records; queries that are called action queries in Access. When they are used to return a set of records, they create only Recordset objects of the Forward-only, Read-only cursor type, and there is no way to specify any other cursor type. Because of this limitation, we will not discuss using the Execute method of the Connection or Command object for opening Recordset objects for the purpose of creating a client-side set of records. For information about cursor types, see "Specifying Cursor Types" later in this chapter.
However, a Command object can be passed to the Open method of the Recordset object as the Source argument. This can be useful in two ways:
You can use the Prepared property of the Command object to optimize and precompile the command. If you will be using the Command object more than once within the scope of your procedure, this will optimize the performance of your procedure.
A Command object is required if you want to supply parameters to a query that can be reused efficiently.
I ALSWAYS use a command object with stored procs. To answer your question I am not sure that it will change to any other cursor, but I have not verified this, but maybe using the RescordSet.Open Command.Execute provides the best chance of working. GL
Tim C.
//Will write code for food
Tim C //Will code for food
March 26, 2003 at 11:29 am
Tim's right, using the recordset.Open method with a command object as the first parameter will let you first create and explicitly state values for CursorType, location etc. Here's a small VB example:
' Create connection and command objects
Set con = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
' Set connection properties and open
con.ConnectionString = "ENTER CONNECTION STRING HERE"
con.CursorLocation = adUseClient
con.Open
' Set command properties
With cmd
Set .ActiveConnection = con
.CommandText = "PROCEDURE_NAME"
.CommandType = adCmdStoredProc
Set params = .Parameters
End With
' Define stored procedure params and append to command.
params.Append cmd.CreateParameter("@param1", adInteger, adParamInput, 0)
params.Append cmd.CreateParameter("@param2", adInteger, adParamInput, 0)
' Specify input parameter values
params("@param1") = param1
params("@param2") = param2
' Setup and open Recordset using Command
Set rst = CreateObject("ADODB.Recordset")
With rst
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
End With
rst.Open cmd
Hope this helps.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
Edited by - chrhedga on 03/26/2003 11:31:44 AM
March 26, 2003 at 11:51 am
Thanks Tim!
I passed the Command Object in RS.Open with Cursor Type & Lock Type specified and it works fine.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply