Database Search

> Learn Visual Basic immersed in a virtual comic book! Simple explanations and fun!
>FREE ASP SOURCE CODE: Free ASP Database Application! Read and write cookies, query and write to databases, master complex search routines, killer SQL, redirect jonk!

Return to Main Column Page

Want to Search with ASP and SQL?
Download, read, scrutinize, test, edit, add, and improve.

Here is the download file that you will need to get the most out of this tutorial. It is a simple 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")%>&#32 <%=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.

Return to main column page!