article

Search Form tutorial and sample code

Email
Submitted on: 1/5/2015 6:50:00 AM
By: Glenn Cook (from psc cd)  
Level: Intermediate
User Rating: By 9 Users
Compatibility: ASP (Active Server Pages), VbScript (browser/client side)
Views: 9062
 
     This tutorial is thorough and provides working code that will have you on your way to searching databases with ASP!

This article has accompanying files
 
				This tutorial creates a search utility designed to show you the basics behind doing a simple
database search using ASP. If you just want to download it, change the data in
the Access database, make a DSN called "Employees," and use it....go
for it!

There are three files in the "searchcode.zip" file that make this work: The database (employees.mdb); The HTML file that posts to the ASP file (ASPSearch.htm); The ASP file that does all the work(DaEngine.asp). I have not documented the Search.htm file because it just does a simple posting using a drop-down menu and an input box. Although it's worth opening the other two files to understand the code completely, I decided not to annotate the contents because there is nothing even the slightest bit complex about them.

ASPSearch!
Purple= HTML Code
Black= HTML Text
Green= Server-side ASP code
Red= My Comments

<%@ LANGUAGE="VBSCRIPT" %>
<html>
<head>
<title>Da Search Results!</title>
</head>
<body>
Here is the simple stuff! Line one tells the IIS server to expect some VBScript ahead and the rest of it just builds the HTML file that will be displayed.
<%
Dim SqlJunk

Set dbGlobalWeb = Server.CreateObject("ADODB.Connection")
dbGlobalWeb.Open("Employees")

SqlJunk = "SELECT * FROM Employees"

If Request.Form("TypeSearch") = "FirstName" Then
SqlJunk = SqlJunk & " WHERE FirstName LIKE '%" &_
Request.Form("DaInBox") & "%'"
End If

If Request.Form("TypeSearch") = "LastName" Then
SqlJunk = SqlJunk & " WHERE LastName LIKE '%" & _
Request.Form("DaInBox") & "%'"
End If

If Request.Form("TypeSearch") = "Title" Then
SqlJunk = SqlJunk & " WHERE Title LIKE '%" &
_
Request.Form("DaInBox") & "%'"
End If

If Request.Form("TypeSearch") = "Division" Then
SqlJunk = SqlJunk & " WHERE Division LIKE '%" & _
Request.Form("DaInBox") & "%'"
End If

If Request.Form("TypeSearch") = "Phone" Then
SqlJunk = SqlJunk & " WHERE Phone LIKE '%" & _
Request.Form("DaInBox") & "%'"
End If

If Request.Form("TypeSearch") = "Email" Then
SqlJunk = SqlJunk & " WHERE EMail LIKE '%" &_
Request.Form("DaInBox") & "%'"
End If

***************************

We start off the ASP code by defining a variable called "SqlJunk" which I will use later on to build a string which I will plug into a SQL statement that defines the Recordset.

The next paragraph "Set dbGlobalWeb...." creates an ADODB connection. After I create an object, I have to open the database that I will be using. I tell it to open a connection I have called Employees.

Here is where you have to do a little server administration: Make a DSN through the ODBC icon in the Control Panel called "Employees." If you're saying, "huh?" right now, just follow directions and you'll understand as you go along. The DSN tab is the second tab from the left and you will need to "Add" a new DSN to an Access database. Find the Access MDB file I called "Employees" and point your "Employees" DSN to the "employees.mdb" file I have supplied for you. (By the way, you could have named your DSN "KrustyTheClown" and it would work just fine as long as you change the "dbGlobalWeb.Open("Employees") to dbGlobalWeb.Open("KrustyTheClown"))

The next paragraph fills the variable("SqlJunk") with the first part of your SQL statement. The statement says, "SELECT every field in the "Employees" table. (When I reread this I realized that many people might be getting confused here because I named my DSN "Employees", my database "Employees" and the table within the database "Employees." Force yourself to look at the source code closely to figure out which "Employees" I'm talking about.) I could claim that I intentionally did this to force you to learn but for some reason I have a tendency to code subsets of things using the same names. It works for me!

The next six "If...Then" statements could have been written as a "Select...Case"- oh well. Initially I only wrote it for two fields and then someone said, "you should make it so they can search all the the fields so I just cut and pasted the rest. Hey, it works, alright?! (The &_ just continues the code on a new line like VB)

Anyway, the first line of every If statement Requests the Form field I call "TypeSearch" and asks if it's equal to a value- "FirstName", "LastName","Title","Division", etc.

The second line of every "If" statement says, "If the above line was true, Then add the rest of the appropriate SQL string code onto the end of the existing "SqlJunk" string." The second half of my SQL statement that I'm sticking into the "SqlJunk" variable completes the SQL statement that I will use to find the records that match your search criteria. The SQL statement now says, "SELECT/Find every record in the Employees table WHERE the "such&such" field has data that is similar/LIKE the input data the user has put in the box called "DaInBox." You'll notice that the Request.Form("DaInBox") is surrounded by &,%,",'. Ok here's what they're for: The % is used by SQL as wildcards like DOS used the *. The & is used to paste the two separate strings to make one long string in the SqlJunk variable. The ' holds the wildcards and the " holds the statement.

Each "If" statement concludes with an "End If."

Set rsGlobalWeb = Server.CreateObject("ADODB.Recordset")
rsGlobalWeb.Open SqlJunk, dbGlobalWeb, 3,3
%>
Here is where I create the ADODB.Recordset! It seems like I've done a lot at this point but really all I've done is made a connection to a database and stick pieces of an SQL statement into a variable I call SqlJunk. In the process of defining what my recordset is going to be, I'm also going create and name my recordset variable(rsGlobalWeb) that I will use to extract the field data within my recordset.

The second line on this page defines the attributes of your ADODB.Recordset object that I have named rsGlobalWeb. This line says, "Open the rsGlobalWeb recordset with the following SQL statement contained within the SqlJunk variable using the connection object we established earlier called dbGobalWeb." The 3 stuff just opens the database with "optimistic" options- meaning it allows multiple connections and assumes no cruddy people are going to try to hack your data.

<% If rsGlobalWeb.BOF and rsGlobalWeb.EOF Then %>
<h2 align="center">We did not find a match!</h2>
<%Else%>
What this code says is, "If the recordset(rsGlobalWeb) is at the beginning(BOF) and end(EOF) of the recordset at the same time Then logically there is no recordset and logically there is no match! If there is something Else other than nothing then go to the next line.
<%If Not rsGlobalWeb.BOF Then%>

<h2>Here are the results of your search:</h2>

<table BORDER="0" width="100%" cellpadding="3">

<tr>

<th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Name </font></th>
<th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Title
</font></th>
<th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Division </font></th>
<th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Phone </font></th>
<th bgcolor="#800000"><font face="Arial" color="#FFFFFF">E-Mail </font></th>
</tr>
<% Do While Not rsGlobalWeb.EOF %>
<tr>

<td><%=rsGlobalWeb("FirstName")%>
<%=rsGlobalWeb("LastName")%>

</td>

<td><%=rsGlobalWeb("Title")%>

</td>

<td><%=rsGlobalWeb("Division")%>

</td>

<td><%=rsGlobalWeb("Phone")%>

</td>

<td><a href="mailto:<%=rsGlobalWeb("Email")%>">
<%=rsGlobalWeb("Email")%>
</a> </td>

</tr>

<%
rsGlobalWeb.MoveNext
Loop
%>

</table>

<%End If%>

<%End If%>

The first line here basically says, "If the recordset is not at the BOF or "Beginning Of File" Then execute this HTML code which defines the header information for the query results."

We make a pretty table with field headers that corresponds to the fields in the database.

<%Do While Not....%>Now we tell it to execute the following code as long as we are not at the end of the recordset(EOF). Now the ASP code starts sticking records underneath the field headers and extracts the field data from the recordset until it reaches the end of the records it has found. I extract the field data within the database using <%=rsGlobalWeb("FieldName")%>

The Loop statement just tells ASP to keep going back up to the Do While statement until it is finished.

Finally, remember to End your If's.

<%
rsGlobalWeb.Close
dbGlobalWeb.Close
%>

</body>
</html>
I close the recordset and the connection to the database and I finish the HTML coding for the results page.

Questions, Suggestions, Ideas? Email me!

winzip iconDownload article

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. Afterdownloading it, you will need a program like Winzip to decompress it.Virus note:All files are scanned once-a-day by Planet Source Code for viruses, but new viruses come out every day, so no prevention program can catch 100% of them. For your own safety, please:
  1. Re-scan downloaded files using your personal virus checker before using it.
  2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

If you don't have a virus scanner, you can get one at many places on the net including:McAfee.com


Other 7 submission(s) by this author

 


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this article (in the Intermediate category)?
(The article with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments


 There are no comments on this submission.
 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular article, please click here instead.)
 

To post feedback, first please login.