Hi all,

Need some help with the CursorAdapter - not my first choice, but unfortunately due to some Unicode requirements SQL Passthrough won't work so I'm trying to rig up some routines that wrap ADO to work like my existing data access classes.

I got most of it working but I can't figure out how to do dynamci parameterized queryies with a cursoradapter.

The docs show and sevearl articles I checked show that you should be able to use the same ?pcParameter syntax that SQLPassthrough supports but that doesn't work for me.

The following code works fine when not parameterized:

CLEAR
CLOSE DATA
LOCAL ca as CursorAdapter

PRIVATE pcDescript
pcDescript = "English"
lcSQL = "SELECT Id,Descript,lDescript,lDescriptBin from ForeignData where descript=?pcDescript"

ca = CREATEOBJECT("CursorAdapter")

ca.Alias = "tShapes"
ca.DataSourceType = "ADO"
ca.SelectCmd = lcSQL
ca.ADOCodePage = 65001

*** HAve COM pass and return parameters as UTF-8 rather than Codepaged strings
SYS(3101,65001)

LOCAL oConn as ADODB.Connection
oConn = CREATEOBJECT("ADODB.Connection")
oConn.ConnectionString = "Provider=sqloledb;Data Source=(local);Initial Catalog=WestWindAdmin;Integrated Security=SSPI"
? oConn.Open()
? oConn.Errors.Count

LOCAL loCommand as AdoDb.Command
*loCommand = CREATEOBJECT("AdoDb.Command")
*loCommand.ActiveConnection = oConn
*loCommand.CommandText = lcSQL
*loCommand.Parameters.Refresh()
*? loCommand.Parameters.Count
*loCommand.Parameters.Item(0).Value = "English"


LOCAL oRS as Adodb.Recordset
oRS = CREATEOBJECT("ADODB.RecordSet")
oRS.ActiveConnection = oConn

ca.DataSource = oRS

? ca.CursorFill() &&.f.,.f.,0,loCommand)
? oConn.Errors.Count

BROWSE

It works with a literal SQL string no problem, but it doesn't work with:

pcDescript = 'English'
lcSQL = "SELECT Id,Descript,lDescript,lDescriptBin from ForeignData where descript=?pcDescript"

In fact it's not even getting to SQL Server at this point (nothing in the trace but a connection opening and hten closing).

Is this ? syntax supposed to work with SQL Server data?

My next thought was to use a COmmand object and add parameters to that, but I cannot for the life of me figure out how to get the CA to recognize the Command object.

Again there's a sample that uses the Command object at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_foxhelp/html/folrfcursorrefreshmethod.asp

but in my code above, the CA ignores any settings on the command object when passed as the last parameter to the CursorFill() method.

I haven't used the CA before (too many overloaded properties to make for a generic solution), so it's possible I'm being ignorant here <g>...

Anybody have any insight into this?

TIA,

+++ Rick ---