Passing SQL Parameters to an IDC File on IIS

You can construct more powerful database queries by passing parameters to the .idc file. Parameters are the names and values of HTML-form controls, such as the Submit button, and names specified directly in URLs. These names and values are sent by Web browsers, and can be used in SQL statements on the server.

Note

When passing parameters, spaces or escape characters such as ?, &, and % can often be problematic. When specifying a field name that will be used as a parameter in another IDC query, use the "%z" escape sequence to specify a field name as: <% "%z" ,FieldName %>

Example Code

The following example shows you how to use SQL query language in an IDC file to return only the authors whose year-to-date sales exceeded 5000:

+SELECT au_lname, ytd_sales  
+ from pubs.dbo.titleview 
+ where ytd_sales>5000 

By using a parameter, you can build a Web page that asks the user to decide what number to use, instead of 5000. The Web page must prompt the user for the year-to-date sales figure and then name the associated variable to sales. The following example shows you how to use an HTML form with an input field used to obtain the number:

<FORM METHOD="POST" ACTION="/scripts/sample2.idc"> 
<P> 
Enter YTD sales amount: <INPUT NAME="sales" VALUE="5000" > 
<P> 
<INPUT TYPE="SUBMIT" VALUE="Run Query"> 
</FORM> 

In the IDC file (Sample2.idc), you use sales in place of the number 5000. Here the parameter name must be sales so that it corresponds to the <INPUT NAME= "sales"> on the Web page. Parameters must be enclosed with percent characters (%) to distinguish them from a normal identifier in SQL. When the Internet Database Connector encounters the parameter in the .idc file, it substitutes the value sent by the Web browser and then sends the SQL statement to the ODBC driver.

SQLStatement: 
+SELECT au_lname, ytd_sales  
+ from pubs.dbo.titleview 
+ where ytd_sales > %sales% 

The percent character (%) is also a wildcard character in SQL. You can use wildcards in an SQL query to search for an element in a table that contains certain characters. To insert a single percent character for a SQL wildcard, use %%. This prevents IDC from trying to use the percent character as a parameter marker. For example:

SQLStatement: 
+SELECT au_lname, ytd_sales, title  
+ from pubs.dbo.titleview  
+ where title like '%%%title%%%' 

For a percent sign to be recognized as an SQL wildcard, you must double it and then add the percent characters around the parameter to distinguish the string as a parameter. In the example, the query searches for all entries in the title column with the word title in them. The preceding query returns the following:

title title and deed main title page author and title

To return all entries with the word title as the first five letters, you would format the query as follows:

SQLStatement: 
+SELECT au_lname, ytd_sales, title  
+ from pubs.dbo.titleview  
+ where title like '%title%%%' 

In the preceding example, the following results are returned:

title title and deed

To return all entries with the word title as the last five letters, you format the query as follows:

SQLStatement: 
+SELECT au_lname, ytd_sales, title  
+ from pubs.dbo.titleview  
+ where title like '%%%title%' 

In the preceding example, the following results are returned:

title author and title