Q: How to use variables or parameters in dBASE SQL Statement.
A: In order to run a query using a variable lookup value you need to use the query.Params array.
Here is more information on the params property and how to use it.(can also be found in dBASE Help files)
PARAMS
Property of
Query, StoredProc
Description
The params property contains an associative array that contains parameter names
and values, if any, for an SQL statement in a Query object or a stored procedure
call in a StoredProc object.
For a Query object, assigning an SQL statement with parameters to the sql property automatically creates the corresponding elements in the params array. Parameters are indicated by colons. The values you want to substitute are then assigned to the array elements in one of two ways:
1. Manually, before the query is activated or requeried with requery( ).
2. By assigning a masterSource to the query, in which case parameters are substituted with the matching fields from the fields array of the masterSource’s rowset. Parameters are matched to fields by name.
For a StoredProc object, the Borland Database Engine will try to get the names and types of any parameters needed by a stored procedure, once the procedure name is assigned to the procedureName property. This works to varying degrees for most SQL servers. If it succeeds, the params array is filled automatically with the corresponding Parameter objects. You must then assign the values you want to substitute to the value property of those objects.
For SQL servers that do not return the necessary stored procedure information, include the parameters, preceded with colons, in parentheses after the procedure name. The corresponding Parameter objects in the params array will be created for you; then you must assign the necessary type and value information.
****** EXAMPLE ******
q = new Query( )
q.sql = "select * from CUST where CUST_ID = :custid"
q.params[ "custid" ] = 123
q.active = true