How to use CursorAdaptor with ADO record set

[masterm]

Hi,

I am using ADO to access SQL server stored procedures. I can access these procedures and send parameters to them, but how do I assign the returned record set to the CA? I want to be able to navigate through the record set and use the returned values with textboxes,grids etc.

Here is my code I use:
LOCAL loConn AS ADODB.CONNECTION, ;
loCommand AS ADODB.COMMAND, ;
loException AS EXCEPTION, ;
loCursor AS CURSORADAPTER, ;
laErrors[1]

SET MULTILOCKS ON

lcFile = "FILE NAME=" + "devccdb.udl"
loConn = NEWOBJECT("adodb.connection")
oRecordSet = NEWOBJECT("adodb.recordset")
loCursor = CREATEOBJECT('CURSORADAPTER')
loCommand = CREATEOBJECT('ADODB.COMMAND')

loConn.open(lcFile)
oRecordSet.cursortype = 3
oRecordSet.LockType = 2

WITH loCommand
.activeconnection = loConn
.commandtext = "select_bkclaman_1"
.commandtype = 4
.parameters("@am_code").value = ""
.parameters("@am_desc").value = ""
ENDWITH

oRecordSet = loCommand.Execute

? orecordset.fields("am_code").value
? orecordset.fields("am_desc").value
orecordset.movenext()

I have descovered I can move forward with movenext but how do I move back,go to the beginning and go to end.

How can I assign the returned record set to the cursor adaptor.
When I add this code. I get and error that the connection that the "ADODB record set already open"
WITH loCursor
.ALIAS = 'bkclaman'
.DATASOURCETYPE = 'ADO'
.DATASOURCE = oRecordSet
.SELECTCMD = 'select_bkclaman_1'
.DATASOURCE.ACTIVECONNECTION = loConn
loCommand.ActiveConnection = loConn

IF .CURSORFILL (.F., .F., 0, loCommand)
browse
ELSE
AERROR(laErrors)
MESSAGEBOX(laErrors[2])
ENDIF
loCommand.ActiveConnection = loConn
? .cursorstatus
WAIT WINDOW


Thanks in advance

 

[Mike Lewis]

Masterm,

The returned record set is in fact the cursor whose name you have stored in the Alias property -- Bkclaman in your example.

You should be able to treat this just like any other VFP cursor.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[masterm]

Mike,

When I select the alias it is not available. When the code executes the ".CURSORFILL (.F., .F., 0, loCommand)" command is not executed as it says the the ADODB.recordset is already open, therefore my alias is not available. That is my problem .. therfore no cursor to work with.

 

[Mike Lewis]

Masterm,

I can't off-hand see the reason for this problem. Sorry.

Is there any special reason for you to work with ADO rather than ODBC? I have always found ODBC to be a much simpler option when using cursoradapters, although the basic technique is pretty similar.

Here's some possible code:

LOCAL loCursor, laErr(1)

* Instantiate a cursoradapter object
loCursor = CREATEOBJECT("CursorAdapter")

WITH loCursor

  * Set its properties
  .Alias = 'bkclaman'
  .DataSourceType = "ODBC"
  .DataSource = SQLCONNECT(<your DSN goes here>)
  .SelectCmd = ;
     < your SELECT or EXEC command here >

  * Get the data
  IF .CursorFill()
    BROWSE
  ELSE
    * Error 
    AERROR(laErr)
    MESSAGEBOX(laErr(2))
  ENDIF

ENDWITH

If you have a special reason for preferring ADO, the above won't be much use. Otherwise, perhaps you could give it a try.

Mike

Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com