Mike Pope
Microsoft Corporation
November 1998
Summary: Describes how to use the Microsoft® Visual InterDev™ Web development system to create parameterized and dynamic database queries. (8 printed pages) Covers:
When you are designing a Web page that involves data binding, you frequently tailor the database query with information available only at run time. For example, you might create a Web page that displays the names of employees in a given department. When the user requests the page, he or she might enter (or select) the department in question. You can then use that response to create the correct database query and display the results.
Doing this usually involves a parameterized query. These can take two forms:
SELECT emp_id, emp_lname FROM employees WHERE dept_id = ?
Parameterized queries are supported by the Recordset design-time control (DTC) in Visual InterDev version 6.0 for many databases, but not all (for example, not for Microsoft Access). They're also not supported for client-side data binding.
Therefore, a third way of tailoring the query to run-time conditions is to create queries on the fly (which we will call dynamic queries). For example, you could create a skeleton SQL statement such as this:
SELECT emp_id, emp_lname FROM employees
Then, based on input from the user, you could append a WHERE clause and execute the query.
To create parameterized and dynamic queries, you use the Recordset DTC. Let's review.
To begin with, you add a Recordset DTC and bind it to either a stored procedure or query. Drag the Recordset DTC onto the page, right-click it, and then choose Properties.
You use the General tab for data binding. To bind to a stored procedure, choose Database object as the source of your data and find the stored procedure in the Object name list. If you're binding to an SQL statement, choose SQL statement and enter the statement in the text area, as follows:
SELECT au_id, au_lname, au_fname FROM authors WHERE au_id = ?
Note the question mark as the parameter placeholder.
Note When binding a parameterized SQL statement to the Recordset DTC in Visual InterDev 6.0, you can't use named parameters. If you are using the Query Designer to create your query, use only a question mark, not parameters delimited with parameter delimiter characters.
When you bind to either a stored procedure or an SQL statement, the Recordset DTC figures out whether parameters are involved. Switch to the Parameters tab of the Properties dialog box. The Parameters tab allows you to enter a literal value (not common, but it does come up), a variable, or an expression such as an object-property value. In many cases, you can also leave the Value column blank, because you'll be providing parameters in script. In the sections to follow I'll tell you when it's appropriate and what to fill in for parameter values.
The preceding examples assume stored procedures and SQL statements that have formal parameters. If you want to create dynamic SQL statements, you perform the same steps as if you were binding to an SQL statement with parameters, except that you don't have any parameter placeholders. Instead you enter the skeleton SQL statement in the General tab. In this scenario, you'll be manipulating the SQL statement as a simple string. In practice, you might not even enter a skeleton statement at all; instead, you might build the entire SQL statement in script.
By default, a Recordset DTC is set to execute its query immediately when the page is requested. This is useful if the record set consists of the entire contents of a table, for example. However, for most parameterized and dynamic queries, you usually don't want to open the record set right away—after all, you're planning on getting some of the required information only after the page has been displayed. (One exception is if you're passing a parameter value to the page, as explained later.) You can disable the auto-open feature by clearing it on the Implementation tab of the Recordset DTC Properties dialog box.
You can pass parameters to a query in a variety of ways, depending on a couple of factors: when you have the information to be used for the parameters, and what else you want to do with the information before using it in a query.
If you know the parameter value at design time, pass a fixed literal value. To prompt the user for a value, or to calculate the value, pass run-time values as parameters. You can also pass parameters to another page. If you're working with client-side queries, can't use a parameterized query, or just want to create SQL on the fly, you can do that, too.
You might know the value to be passed at the time you are creating your Web page. This isn't a common scenario, but comes up occasionally. For example, perhaps you are binding to a stored procedure that displays the employees for a department based on a department number parameter. However, you are building a Web page for your department in particular, so you already know what the department is. In that case, you can pass the department number as a fixed value.
To pass a fixed literal value, open the Parameters tab of the Recordset DTC's Properties window. In the Value column, enter a literal value for the parameter. If you are entering a string value, put single quotation marks around the literal value. When the page is displayed, the Recordset DTC reads the fixed value and passes it to the stored procedure or query.
The most common scenario is probably that in which you prompt the user for a parameter value or you calculate the value based on run-time information.
Because the page must already be displayed before you can prompt the user or perform a calculation, you definitely do not want the record set to be opened automatically. Instead, you gather the parameter information, and then manually open the record set with the Recordset object's Open method.
You can pass a run-time value as a parameter in these ways:
Both options require at least a small amount of scripting. The first option involves slightly less scripting, but the second option allows you to keep everything in one place—your choice!
Note You use this technique also if you are passing a value from one page (where you collect information) to another (where you run the query).
To bind the Recordset DTC to a run-time value, open the Parameters tab of the Recordset DTC's Properties window. In the Value column, enter an expression that will resolve to a property value (such as txtName.value) or the name of a variable (for example, dHireDate).
Note Be careful to use correct capitalization, even if you intend to use VBScript in your page.
If you bind to a variable, you have to set the variable's value in script before opening the record set. The variable must be global, or the Recordset script object won't be able to read it at run time. After setting the variable, call the Recordset object's Open method to execute the stored procedure or query. If the record set might have been opened previously, call the Close method first.
A script that passes a value using the bound variable dHireDate might look like this:
<SCRIPT LANGUAGE="VBScript" RUNAT="Server">
dim dHireDate ' Must be declared globally
Function btnDone_onclick()
Recordset1.close()
dHireDate = "01-01-92"
Recordset1.open()
End Function
If you bind the parameter to a property value (such as txtName.value), you don't need to explicitly set any variables or other values, because the Recordset script object will be able to read the property value when needed. But you still need to open the record set. A script to do that is therefore even simpler than one that sets a variable first:
<SCRIPT LANGUAGE="VBScript" RUNAT="Server">
Function btnDone_onclick()
' The Recordset object will get the property value itself
Recordset1.close()
Recordset1.open()
End Function
If you didn't bind the DTC to a variable or property, you need to assign a value to the parameter before opening the record set. Do that by calling the setParameter method. The syntax is:
Recordset1.setParameter parameterNumber, parameterValue
You need to be careful about the parameterNumber value. In Microsoft SQL Server™ stored procedures, parameter number zero is reserved for the procedure's return value. In that case, the first parameter that you can pass to the procedure is parameter number 1. On the other hand, if you're executing a parameterized SQL statement, the parameter count starts at zero (no return value). Test carefully.
The following example shows how to pass two variables as parameters to an SQL statement. If the Recordset DTC is not bound to a variable, the variable does not have to be global:
<SCRIPT LANGUAGE="VBScript" RUNAT="Server">
Function btnDone_onclick()
hireDate = getHireDate() ' Dynamically calculated value
name = txtFname.value & ", " txtFname.value
Recordset1.setParameter 0, hireDate ' 0=1st parameter in SQL query
Recordset1.setParameter 1, name
Recordset1.open()
End Function
The following is similar, but passes property values instead of variables:
<SCRIPT LANGUAGE="VBScript" RUNAT="Server">
Function btnDone_onclick()
Recordset1.setParameter 0, txtHireDate.value
Recordset1.setParameter 1, txtName.value
Recordset1.open()
End Function
Often you will gather information on one page—perhaps on a form—and then call another page, passing it the values you've collected. The called page then runs a stored procedure or query based on information passed to it. In Visual InterDev 6.0, you usually create the called page as an Active Server Pages (ASP) page, which allows you to use the Request object to extract the information forwarded from the calling page.
When creating the called page, you put a Recordset DTC on it as usual. You can set the Recordset DTC (actually, leave it at its default setting) to automatically open the record set when the page is displayed. You can do this because even though you are deriving the parameter value at run time (on the calling page), it is available before the record set is opened on the called page.
Imagine that you have created a page containing a form. In the form, users enter a department name into a text box called txtDept. When the user submits the form, you call a second page that has a Recordset DTC bound to a stored procedure.
On the second page, the user's input is available in the ASP Request object. In this case, it's in the Request.Form(txtDept) object. You can bind the DTC directly to this value in the Parameters tab of the DTC's Properties window. Or, if you prefer, you can set the parameter value in script. Before the record set is opened, the Recordset object's onbeforeopen event is fired. Therefore, to set a parameter value before the record set is opened, write a handler for the onbeforeopen event that looks something like this:
<SCRIPT LANGUAGE="VBScript" RUNAT="Server">
Function Recordset1_onbeforeopen()
dept = Request.Form("txtDept")
Recordset1.setParameter 1, dept
End Function
</SCRIPT>
In some cases it isn't practical to simply pass parameters to an existing stored procedure or SQL query:
For these scenarios, you can build the SQL statement dynamically and then execute it. To create or change the SQL text of a query, set the value of the Recordset object's setSQLText property. You can either build the statement from scratch, or you can get the text of an existing statement and modify it.
If you're going to build the statement from scratch, disable automatic open of the record set. If the DTC is bound to a statement that you are going to modify at run time, set the automatic open option according to whether you want to execute the existing statement as soon as the user requests the page.
To execute a new query, call the Recordset object's Open method. If you've changed a query that has already been executed, call the object's Requery method first.
Here's a simple example that displays author IDs and names. If the user has entered the last name of a particular author in a text box called txtWhere, the script dynamically builds a WHERE clause that finds only that last name. In this example, the query is executed when the user clicks a button. If you are using a server-side DTC, the script is the same, but the <SCRIPT> tag has to include the attribute RUNAT=SERVER.
<SCRIPT LANGUAGE="JavaScript">
function Button1_onclick(){
sqlStatement = "SELECT au_id, au_fname, au_lname FROM authors "
lName = txtWhere.value
if(lName != ""){
whereClause = " WHERE (au_lname = '" + lName + "')"
sqlStatement = sqlStatement + whereClause
}
Recordset1.setSQLText(sqlStatement)
Recordset1.requery();
Recordset1.open()
}
</SCRIPT>
By the way, you can set the setSQLText property to any SQL command, not just queries. For example, you can set it to a string that includes the name of a stored procedure plus some command-line parameter values. Here's a quick example:
Recordset1.setSQLText('"byroyalty" ' + Textbox1.value);
You should be aware that the query (and requery) is executed differently depending on your scripting platform. If your scripting platform is Server (if you are using a server-side Recordset DTC), the Open method performs the query synchronously—the script pauses until the query is complete, however long that takes. Immediately following the Open method, you can extract information from the record set or otherwise proceed with the assurance that you have all the data at your disposal.
However, if your scripting platform is Client, queries are performed asynchronously. In other words, when you call the Recordset object's Open method, the script sets off the query and then goes on to the next line. The only reliable way to know that the query is complete and the record set is available is to write a handler for the Recordset object's ondatasetcomplete event.
Incidentally, if you're interested in why this difference exists, it's because server and client data binding use different technology. In the current version of Visual InterDev, server data binding is based around Microsoft ActiveX® Data Objects (ADO), whereas client data binding uses the Remote Data Service (RDS) capabilities exposed in dynamic HTML (DHTML). As a practical matter, this difference is not apparent except in a few small situations like this.
Notice that in the preceding example, the Open method is the last line of the script. You could certainly add more lines to that script, but you should not add any that attempt to access the record set. Instead, write a handler such as the following, which uses a DHTML property to display the record count and then calls a separate display routine:
<SCRIPT LANGUAGE="JavaScript">
function Recordset1_ondatasetcomplete(){
p1.innerHTML = "Total records that meet your criteria = " +
Recordset1.getCount();
displayRecords();
}
</SCRIPT>
You've seen the details of executing a parameterized or dynamic query. That's usually only the beginning—there are all sorts of interesting things to do with the data after you've gotten it out of the database. For example, you can display it in a Grid DTC, which offers many options for dynamically binding and displaying data. You may also want to allow users to update the database after displaying information from it.
You can find information about these types of tasks in the Visual InterDev documentation available on the MSDN Library CD and the MSDN Library Online (see http://msdn.microsoft.com/default.asp). And be sure to visit the Visual InterDev Web site at http://msdn.microsoft.com/vinterdev/ for more in-depth information about how to use and manage databases using Visual InterDev 6.0.