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:

sub GetQueryResults(strKeyword)

The strKeyword parameter is collected from the Request.QueryString variable, which is passed to the page from the “MainFrame” frame, as shown below:

dim authorQuery

authorQuery = Request.QueryString("authorQuery")



if authorQuery <> "" then

GetQueryResults(authorQuery)

end if

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:

sub GetQueryResults(strKeyword)



dim objConn

dim objRS

dim counter



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 & "%'"

We’re now ready to create our JavaScript function, which will add the details of each author found to the authors drop down box in the “MainFrame” frame:

function ShowResults()

{

var numItems = top.MainFrame.form1.bookAuthor.length;



for(i = numItems - 1; i >= 0; i--)

top.MainFrame.form1.bookAuthor.options[i] = null;

Our ShowResults() JavaScript function starts by removing all of the items in the main frames author drop down list.

If no records in the authors’ table matched our keyword(s), then the JavaScript function will add an option to the main frames author drop down list telling the user that no records were found:

<%

if objRS.EOF then

%>

top.MainFrame.form1.bookAuthor.options[0] = new Option();

top.MainFrame.form1.bookAuthor.options[0].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();

top.MainFrame.form1.bookAuthor.options[<%=counter%>].text = "<%=objRS("au_name")%>";

top.MainFrame.form1.bookAuthor.options[<%=counter%>].value = "<%=objRS("au_id")%>";

<%

counter = counter + 1

objRS.MoveNext

wend

%>

If, for example, we enter “e” in the query text box and click on the query button, 18 results will be added to our author drop down list.



On the other hand, if we entered “blahblah” into the query text box and clicked on the query button, then no results would be returned.

Next: Conclusion >>
 

More ASP Articles
More By Phanix Chen

Comments On: Grabbing Data On The Fly

>>> Be the FIRST to comment on this article!