Hi, everybody!
I have a doubt about using parameters in query with ADO using VfpOledb provider.
When the parameter is a string everything works fine.
When the parameter is numeric it just doesn’t work.
I’m trying to use the method createparameter of object Command.

I’m sending a small routine for testing.If lnchoice equals 1 the parameter is string,otherwise is numeric
Could you please execute it and help me to see what’s wrong?

**Routine
lnchoice=2
If !Directory("c:\test_mz")
md c:\test_mz
Set Default To c:\test_mz
Create DATABASE test
Create TABLE client (Name C(10),Credits N(8,2))
Insert into client values("Moacyr",900)
Insert into client values("Silva",3000)
Insert into client values("Caca",520)
Close DATABASES all
endif

Local loConn as ADODB.Connection
LOCAL loRS as ADODB.RecordSet
Local lcselect as String
Local lvar &&Store Parameter
#DEFINE ADCHAR 129
#DEFINE ADNUMERIC 131
#DEFINE ADBOOLEAN 11

loConn = CreateObject("adodb.connection")
**Connection String
loConn.ConnectionString="provider=vfpoledb;data source=C:\test_mz\test.dbc"
**Opening Connection
loConn.Open

Local loCommand as ADODB.Command
loCommand = CreateObject("ADODB.Command")

** Defining active connection
loCommand.ActiveConnection=loConn
If lnchoice=1
lcselect=[select * from client where name like ?]
lvar="M%"
else
lcselect=[select * from client where credits>?]
lvar=1000
endif

loCommand.CommandText=lcselect
DO case
Case vartype(lvar)=="C"
loParameter=loCommand.CreateParameter(,ADCHAR,1,10,lvar) &&String
Case vartype(lvar)=="L"
loParameter=loCommand.CreateParameter(,ADBOOLEAN,1,,lvar) &&Boolean
Case vartype(lvar)=="N"
loParameter=loCommand.CreateParameter(,ADNUMERIC,1,,lvar) &&Numeric
Endcase
loCommand.Parameters.Append(loParameter)

** Executing Command criating recordset

loRS= loCommand.Execute()
lors.close()
lors.cursorType=1
lors.open()
If loRs.RecordCount>0
loRs.MoveFirst
FOR i=1 to loRs.RecordCount
Wait WINDOW "ADO -"+lors.Fields("name").value TIMEOUT 1
loRs.MoveNext
Next
Else
MessageBox("ADO - No records found!")
EndIf

lors.close()

Thanks a Lot

Moacyr Zalcman