Data | Database One, description
Most end users require that their web scenario be able to provide some kind of reporting capability. With HTML and ASP, you can easily provide the ability to list records in a table and browse through them individually. But users often require the ability to select a category of records through a Drop-down list box (or other option), or they want to be able to use some sort of "query interface" to build and execute queries without having to use SQL directly. This paper demonstrates a method of using ASP technology to establish a selectable recording filter interface. Although the solution discussed here is fairly straightforward, using similar techniques can provide complex query capabilities on any Web page.
Before encoding any solution, you should fully understand and document the end-user's needs. For this example, a user requires the ability to get a set of records of a specified table by using a method of a specified class name, and he can select all categories or select a particular category. To achieve this, we use a "recursive" ASP page that provides a selection form containing a list of categories from the database, extracts the result recordset based on the data sent by the form, and the resulting records are displayed in tabular form for easy viewing by the user.
Ii. Construction of filtration conditions
For such a database application, you should first understand the database table structure involved and the connection parameters necessary to connect to the database system. The method of specifying the connection parameters is described in detail in Microsoft's ASP online documentation and is no longer detailed in this article. The two tables used in this example are: Document_category, which contains the unique category name category_name;documents for the user to select, which contains other data corresponding to the category name, fields including Category_name, File_ Name, Comments, date_submitted and so on.
Select the Category list in the form from the Category_name field in the Category table Document_category, the code for the constructed category Drop-down list box is as follows, please note that the VBScript ADO include file is used here:
<!--#Include file = "Adovbs. INC "-->
<%
connstring = "dsn=localupload;"
Set conn = Server.CreateObject ("ADODB. Connection ")
Conn. Open connstring,,, adOpenForwardOnly
Set rs = SERVER. CreateObject ("ADODB.") Recordset ")
' first get the category record used to populate the Drop-down list box
' Set up SQL statements
strSQL = "SELECT * from Document_category ORDER by Category_name"
Rs. Open Strsql,conn, adOpenStatic
%>
<form name= "Formcategory" >
<select name= "Qrycategory"
>
<option selected> Please select a category:
<% do, not Rs. Eof
Response.Write ("<option value= '" & RS ("Category_name") & "' >" _
& RS ("Category_name"))
Rs. MoveNext
Loop
Rs. Close
Set rs=nothing
%>
</OPTION>
</SELECT><BR>
</FORM>
The onchange in the select tag is used to submit HTML forms. The value of the Drop-down list box changes, and the JavaScript function GetFilter executes. Using a similar approach, you can describe the records of a database table with multiple different form elements (such as a second Drop-down list box, a radio button, or a check box, and so on). The category value that the user selects will be used to construct the SQL SELECT statement that extracts records from the database.
Iii. Submission of Forms
Here we will use two JavaScript functions to complete the form Information submission task. The GetFilter function is used to submit the user's selected category value to the page itself, while the Getlistvalue function is used to get the user's selected category value. Using JavaScript to implement these two functions allows the script to run on different browsers:
<script language= "JavaScript" >
function GetFilter (ListItem) {
var object = "";
var listvalue = Getlistvalue (ListItem);
Document.formCategory.submit (ListValue);
}
function Getlistvalue (list) {
var listvalue= "";
if (List.selectedindex!=-1) {
ListValue = List.options[list.selectedindex].value;
}
return (ListValue);
}
</SCRIPT>
Iv. Display of results
The last thing to do is construct the query and display the results of the query. First, the query's construction relies on the name of the category that the user chooses in the Drop-down list box, which requires a simple SQL SELECT statement that specifies the category that the user chooses in the WHERE clause. After you construct the query, you can execute it, and if there are no records that meet the requirements, a message is displayed to the user, otherwise the records are displayed. Here is the VBScript code that completes the above task:
<%
If request.querystring ("qrycategory") = "" Then
Response.Write ("Please select a category from the list.")
Else
' Construct query
Set rs = SERVER. CreateObject ("ADODB.") Recordset ")
' Set up SQL statements
strSQL = "Select Documents.file_name, Documents.comments,"
strSQL = strSQL & "documents.date_submitted"
strSQL = strSQL & "from Documents"
strSQL = strSQL & "WHERE ((documents.category_name) = '" _&
Request.QueryString ("Qrycategory") & "')"
' Open the Recordset object
Rs. Open strsql,conn,adopenstatic
If Rs. RecordCount = 0 Then
Response.Write ("No records are found for the specified category" _ &
Request.QueryString ("Qrycategory"))
Else
Response.Write ("<H3> given category contains the following records:" _ &
Request.QueryString ("Qrycategory") & "</H3>")
' Show query results in table
Response.Write ("<table cellspacing=2 cols=3 width=500>")
Response.Write ("<TR>")
Response.Write ("<td align=center valign=top width=20%")
Response.Write ("bgcolor= #C8C8FF ><B> name </B></TD>")
Response.Write ("<td align=center valign=top width=65%")
Response.Write ("bgcolor= #C8C8FF ><B> description </B></TD>")
Response.Write ("<td align=center valign=top width=15%")
Response.Write ("bgcolor= #C8C8FF ><B> date </B></TD>")
Response.Write ("</TR>")
Do but not Rs. Eof
Response.Write ("<TR>")
Response.Write ("<TD>" & RS ("file_name") & "</TD>")
Response.Write ("<TD>" & RS ("Comments") & "</TD>")
Response.Write ("<TD>" & RS ("date_submitted") & "</TD>")
Response.Write ("</TR>")
Rs. MoveNext
Loop
Rs. Close
Response.Write ("</TABLE>")
End If
End If
Set rs=nothing
Conn. Close
Set conn=nothing
%>
Although we use only a simple SQL SELECT with a WHERE clause in this example, you can construct complex interfaces and queries in a similar way, as long as you increase the form elements used to specify the criteria and the scripts that construct the appropriate SQL statements based on the user selection results.
V. Summary
ASP technology provides a scalable development environment for the different data query interfaces required by end users. The comprehensive use of scripts and HTML can provide a flexible interface for viewing and interpreting data, which provides a simple but efficient way to filter data according to user requirements.
