Home arrow ASP arrow Page 5 - Grabbing Data On The Fly
ASP

Grabbing Data On The Fly


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.

Author Info:
By: Phanix Chen
Rating: 5 stars5 stars5 stars5 stars5 stars / 8
December 08, 2001
TABLE OF CONTENTS:
  1. · Grabbing Data On The Fly
  2. · Some methods for passing data around
  3. · Using frames to solve the refresh problem
  4. · Query and return
  5. · The processframe.asp page
  6. · Conclusion

print this article
SEARCH DEVARTICLES

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.

Querying our authors table and returning results

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.

Our add book form when no results are returned
blog comments powered by Disqus
ASP ARTICLES

- Central Scoreboard with Flash and ASP
- Calorie Counter Using WAP and ASP
- Creating PGP-Encrypted E-Mails Using ASP
- Be My Guest in ASP
- Session Replacement in ASP
- Securing ASP Data Access Credentials Using t...
- The Not So Ordinary Address Book
- Adding and Displaying Data Easily via ASP an...
- Sending Email From a Form in ASP
- Adding Member Services in ASP
- Removing Unconfirmed Members
- Trapping HTTP 500.100 - Internal Server Error
- So Many Rows, So Little Time! - Case Study
- XDO: An XML Engine Class for Classic ASP
- Credit Card Fraud Prevention Using ASP and C...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials