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.