foxpro parameterized query not working

[hank hank]

What I'm trying to accomplish is to pass a parameterized query to a foxpro database.  At first I tried using the connectivity database kit, and I get this error:
Cmd Execute.vi->db_toolkit.vi<ERR>Exception occured in Microsoft OLE DB Provider for ODBC Drivers, ODBC driver does not support the requested properties..  in Rec Create - Command.vi->Cmd Execute.vi->db_toolkit.vi

The error occurs during the 'open recordset' node of the Rec Create-Command.vi inside the DB tools execute query.vi

Then I built the attached file to try using adodb connections.  It appears that I am somehow assiging the variable incorrectly, b/c I think I'm getting an empty recordset.  I get an "eof or bof is true..." error at the 'movefirst' node after the command-execute.

If I remove the parameterized query portion, and hard code the criteria into the sql statement, it will retrieve the data correctly. 

Also, I ran the following code:
++++++++++++++++++++++++++++++++++++++++++++++++++++=
Dim fpconn As New ADODB.Connection
Dim fpcomm As New ADODB.Command
Dim fprst As New ADODB.Recordset
Dim fpparam As New ADODB.Parameter
Dim var As String

Set fpconn = New ADODB.Connection
fpconn.CursorLocation = adUseServer

fpconn.ConnectionString = "Provider=vfpoledb;DSN=MetTrack_pctoolcrib"

fpconn.Open
Set fpcomm = New ADODB.Command
Set fprst = New ADODB.Recordset

    fpcomm.CommandText = "SELECT * from stno WHERE serialno = ?"
    fpcomm.CommandType = adCmdText
   
    'create parameter
    Set fpparam = fpcomm.CreateParameter("fpparam", adChar, , 64, "1610-13901")
             
    'append paramter and execute
    fpcomm.Parameters.Append fpparam
    fpcomm.ActiveConnection = fpconn
  
    Set fprst = fpcomm.Execute
                   
        If Not (fprst.EOF = True And fprst.BOF = True) Then
         var = fprst!serialno
        End If
fpconn.Close

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

through visual basic for applications, and it retrieved the data correctly.  I think I have the most recent FoxPro driver as well as the OLE DB for ODBC provider.  The VBA code uses the same DSN and the parameter query works, so I don't think that is the case.

Do I have something wired incorrectly or am I missing a step in the test_no_param.vi?

Thanks,
Ken

[Ching_F.]

Hi hank hank,

I have looked through your LabVIEW code, and do not find anything that jumps out at me as being incorrect.  Can you please provide a bit more information for me though?  What version of the Database Connectivity Toolkit are you using?  What version of LabVIEW are you programming with, and do you get a specific error code number or is it just the error description you have given? 

I hope to hear from you soon, and have a great day!!

[hank hank]

Hi Ching,

I got it to work yesterday by changing the connection string in the 'open connection' node from 'DSN=MetTrack_pctoolcrib'  to 'Provider=vfpoledb;DSN=MetTrack_pctoolcrib'.

I think that when I set up the odbc link in the odbc administrator, even though it was using the visual fox pro driver, I think it was defaulting to use the 'ole db for obdc' provider and not allow me to change to it.

Adding the 'provider=vfpoledb' to the connection string made it start working.  It still doesn't seem to like taking field names as parameters, if I use 'select ? from xxx...' and append the field name to the parameters collection, it will return the literal field name, not the field value, but I can live with hard coding them in, since they won't change for what I'm trying to do, but it seemed weird, since only the field names were acting funny.

Thanks,

Ken 

[Ching_F.]

hank hank,

That sounds like a good work aroud for now, but I will look into this and see if there is a better method of doing this.

Please let me know if you run into any other questions regarding this issue.