When we're querying a database, we will usually need to press a submit button on a form to pass data to another ASP page, which would generate the query, and return the results. In some situations, this page refresh is just plain annoying and takes too long to complete. In this article, Phanix will describe another way to query a database using a combination of web technologies.
Grabbing Data On The Fly - The processframe.asp page (Page 5 of 6 )
Our processframe.asp page contains a fairly large amount of code, so I will just describe the function that will generate our query results, GetQueryResults(). You can, of course download the source code for this entire article from the last page.
GetQueryResults() is an ASP function that takes one parameter. Its prototype is shown below:
The strKeyword parameter is collected from the Request.QueryString variable, which is passed to the page from the “MainFrame” frame, as shown below:
authorQuery = Request.QueryString("authorQuery")
if authorQuery <> "" then
As you can see, if there is no query passed to the page, then it will do nothing. This is handy, because when our frameset is loaded initially, the processquery.asp page won’t have any authorQuery value passed in the query string.
Our GetQueryResults() function starts by declaring a new ADO command and a new ADO recordset object. Then, a connection to the SQL server is established. In this example, I am assuming that SQL Server resides on the same machine as IIS:
set objConn = Server.CreateObject("ADODB.Connection")
set objRS = Server.CreateObject("ADODB.Recordset")
counter = 0
objConn.Open "Provider=SQLOLEDB; Data Source=(local); Initial Catalog=pubs; User Id=sa; Password="
objRS.ActiveConnection = objConn
Next, we open our recordset object with a query. The query uses the LIKE function to test whether each author’s last name (the au_lname field) is like the strKeyword variable:
objRS.Open "SELECT au_id, au_fname+' '+au_lname AS au_name FROM authors WHERE au_lname LIKE '%" & strKeyword & "%'"
var numItems = top.MainFrame.form1.bookAuthor.length;
for(i = numItems - 1; i >= 0; i--)
top.MainFrame.form1.bookAuthor.options[i] = null;
if objRS.EOF then
top.MainFrame.form1.bookAuthor.options = new Option();
top.MainFrame.form1.bookAuthor.options.text = "-- No Authors Found --"
On the other hand, if at least one author matched our keyword(s), then we add the details of each author to the main frames author drop down list:
while NOT objRS.EOF
top.MainFrame.form1.bookAuthor.options[<%=counter%>] = new Option();