Retrieving a value using a SQL command via FoxPro

[computerguy2]

Hi...I need to retrieve a value from a FoxPro database using a SQL statement and put that value into a variable. When I try to Select QOH and run lQOH = conn.Execute sqlstatement, recordsaffected, adCmdText I get a compile error, expected '='. Could someone please help me out.

Thanks..John

Set conn = New ADODB.Connection
Let conn.ConnectionString = "Driver={Microsoft Visual Foxpro Driver};UID=;SourceType=DBF;SourceDB=c:\Inventory;Exclusive=No;BackgroundFetch=No;Collate=Machine;Null=Yes;Deleted=Yes;"
conn.Open

Set rsStock = New ADODB.Recordset
rsStock.Open "select * from stock", conn, adOpenDynamic, adLockOptimistic

sqlstatement = "Select qoh from stock where trim(barcodeno) = '0166410600'"
conn.Execute sqlstatement, recordsaffected, adCmdText

 

[computerguy2]

Would someone have any ideas on the above problem. Thx.

 

[BlackburnKL]

I think you can do this more simply using the following modified code:

Set conn = New ADODB.Connection
Let conn.ConnectionString = "Driver={Microsoft Visual Foxpro Driver};UID=;SourceType=DBF;SourceDB=c:\Inventory;Exclusive=No;BackgroundFetch=No;Collate=Machine;Null=Yes;Deleted=Yes;"
conn.Open
sqlstatement = "Select qoh from stock where trim(barcodeno) = '0166410600'"
Set rsStock = New ADODB.Recordset
rsStock.Open sqlstatement, conn, adOpenDynamic, adLockOptimistic

In your original code, it would appear that you retrieved a bunch of records into your rsStock recordset (with the "select * from stock" portion of your .open command)--and then never used them. Then, it would apear that your conn.Execute command pulled a smaller subset of records (perhaps only one record), but didn't place it into your rsStock recordset.

The code above doesn't put it into a variable, but it should more efficiently get it into a recordset that you can use to place it into a variable.

Good luck,

BlackburnKL