Executing Dynamic Queries

See Also

If the recordset script object is bound to an SQL query, you can change the query at run time and re-execute it. This is particularly useful if you want to run a query that is based on information provided by the user.

The current text of your SQL command is available by calling the recordset script object's getSQLText method. You can set the SQL text by calling its setSQLText method. After setting the SQL text, you re-execute the query by calling the recordset script object's open method.

The following example shows how you can dynamically change the sort order of the records in your recordset. It gets the SQL text, appends a new ORDER BY clause onto the end of the command, updates the recordset again, and runs the query. After the query has been run, the handler restores the original query text.

Sub btnByFName_onclick()
   oldSQL= rsEmployeeList.getSQLText
   newSQL = oldSQL & " ORDER BY firstName"
   rsEmployeeList.setSQLText(newSQL)
   rsEmployeeList.Open   ' Executes the new query
   rsEmployeeList.moveFirst
   ' Restores the original query
   rsEmployeeList.setSQLText(oldSQL)
End Sub

You can determine when the query has finished by writing a handler for the ondatasetcomplete event. For example, you might wait until the query has finished before you display data from it and before you enable the controls on the page. A handler to do that might look like this:

Sub rsEmployeeList_ondatasetcomplete()
   ' Enables controls
   btnNext.disabled = False
   btnPrevious.disabled = False
   btnSave.disabled = False
   btnNew.disabled = False
   ' Refreshes the fields on the page
   txtLastName.value = rsEmployeeList.fields.getValue("au_lname")
   txtFirstName.value = rsEmployeeList.fields.getValue("au_fname")
End sub