The AddToWhere method adds a condition to the WHERE clause of a SQL statement. This saves having to parse the statement and doing string manipulation to insert the condition at the correct spot. This method can be called, for example, from the Select script of a table to automatically add a filter condition the user doesn't see. If you want to change the filter conditions for a report, such as from the DataEngine.FinalizeSQLStatement script, add new conditions to FilterConditions collection instead because changing the WHERE clause of a report's SQL statement causes Stonefield Query to treat it as a custom SQL statement, which is processed differently than one created by Stonefield Query.
Syntax
AddToWhere(SQLStatement as String, Condition as String) as String
Parameters
SQLStatement
The SQL statement to add a condition to.
Condition
The WHERE condition to add to the SQL statement.
Return Value
The SQL statement with the condition added to the WHERE clause; if there was no WHERE clause in the statement, one is added.
Example
This example, called from the Select script for a table, adjusts the query so the user can only see active records.
Visual FoxPro
lparameters toApplication as SQApplication, ;
toDatabase as Database, tcSelect, tcCursor
local lcSelect, llReturn
lcSelect = toApplication.DataEngine.AddToWhere(tcSelect, ;
'ACTIVE = .T.')
llReturn = not empty(toDatabase.ExecuteSQLStatement(lcSelect, ;
.NULL., tcCursor))
return llReturn
VBScript
function Main(SQApplication, Database, SelectStatement, CursorName)
SQLSelect = SQApplication.DataEngine.AddToWhere(SelectStatement, _
"ACTIVE = 1")
Main = Database.ExecuteSQLStatement(SQLSelect)
end function
JavaScript
function Main(SQApplication, Database, SelectStatement, CursorName) {
var SQLSelect, XMLResult ;
SQLSelect = SQApplication.DataEngine.AddToWhere(SelectStatement,
'ACTIVE = 1') ;
XMLResult = Database.ExecuteSQLStatement(SQLSelect) ;
return XMLResult ;
}
C#
public static string Categories_Select(SFQApplication sfqApplication,
Database database, string selectStatement, string cursorName)
{
string sqlSelect, results;
sqlSelect = sfqApplication.DataEngine.AddToWhere(selectStatement, "ACTIVE = 1");
results = database.ExecuteSQLStatement(sqlSelect);
return results;
}
VB.NET
public shared function Categories_Select(sfqApplication as SFQApplication, _
database as Database, selectStatement as string, cursorName as string) as string
Dim sqlSelect, results as String
sqlSelect = sfqApplication.DataEngine.AddToWhere(SelectStatement, "ACTIVE = 1")
results = database.ExecuteSQLStatement(sqlSelect)
return results
End Function
The code calls the AddToWhere method of the DataEngine object of the Application object to add the condition to the SQL statement, then calls ExecuteSQLStatement to retrieve the data. The VBScript and JavaScript code returns the result set as XML and the Visual FoxPro code creates a cursor.
See also
DataEngine Object | AddToFields | DataEngine.FinalizeSQLStatement | FilterConditions Collection | Select© Stonefield Software Inc., 2023 • Updated: 06/06/16
Comment or report problem with topic