Search Form tutorial and sample code

DOWNLOAD IT, check out my site at: http://move.to/iNfOsWorld

THIS WEBSITE WONT LET ME UPLOAD IT, SO GET IT FROM MY WEBSITE, THEN VOTE FOR ME =], GET IT AT:
http://members.xoom.com/infosworld2/C-ChatExample.zip

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!</font></small></p>
<p><small><font face="Verdana">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.</font></small></p>
<p><font face="Verdana"><strong><font color="#408080"><big><big>ASPSearch!</big></big></font><small><br>
<font color="#800080">Purple= HTML Code</font><br>
Black= HTML Text<br>
<font color="#008000">Green= Server-side ASP code</font><br>
<font color="#ff0000">Red= My Comments</font></small></strong></font></p>
<table border="1" cellPadding="3" width="100%">
 <tbody>
 <tr>
  <td vAlign="top" width="51%"><font face="Verdana"><font color="#000000" size="1"><</font><font color="#ff00ff" size="1">%</font><font color="#000000" size="1">@
  LANGUAGE="VBSCRIPT" </font><font color="#ff00ff" size="1">%</font><font color="#000000" size="1">><br>
  <<font color="#800080" size="1">html</font>><br>
  <<font color="#800080" size="1">head</font>><br>
  <<font color="#800080" size="1">title</font>>Da Search
  Results!<<font color="#800080" size="1">/title</font>><br>
  <<font color="#800080" size="1">/head</font>><br>
  <<font color="#800080" size="1">body</font>></font></font></td>
  <td vAlign="top" width="49%"><small><font color="#ff0000" face="Verdana">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.</font></small></td>
 </tr>
 <tr>
  <td vAlign="top" width="51%"><font face="Verdana"><font color="#008000"><font size="1"><%</font><br>
  </font><font color="#008000" size="1">Dim SqlJunk<br>
  <br>
  Set dbGlobalWeb = Server.CreateObject("ADODB.Connection")<br>
  dbGlobalWeb.Open("Employees")</font></font><font size="1">
  <p></font><font color="#008000" size="1" face="Verdana">SqlJunk =
  "SELECT * FROM Employees"</font><font size="1"></p>
  <p></font><font color="#008000" size="1" face="Verdana">If
  Request.Form("TypeSearch") = "FirstName" Then<br>
  SqlJunk = SqlJunk & " WHERE FirstName LIKE '%"
         &_<br>
  Request.Form("DaInBox") & "%'"<br>
  End If</font><font size="1"></p>
  <p></font><font color="#008000" size="1" face="Verdana">If
  Request.Form("TypeSearch") = "LastName" Then<br>
  SqlJunk = SqlJunk & " WHERE LastName LIKE '%"
         & _<br>
  Request.Form("DaInBox") & "%'"<br>
  End If</font><font size="1"></p>
  <p></font><font color="#008000" size="1" face="Verdana">If
  Request.Form("TypeSearch") = "Title" Then<br>
  SqlJunk = SqlJunk & " WHERE Title LIKE '%"
             &</font><font size="1"><font color="#008000" face="Verdana">
  _<br>
  Request.Form("DaInBox") & "%'"<br>
  End If</font></p>
  <p></font><font color="#008000" size="1" face="Verdana">If
  Request.Form("TypeSearch") = "Division" Then<br>
  SqlJunk = SqlJunk & " WHERE Division LIKE '%"
           & _<br>
  Request.Form("DaInBox") & "%'"<br>
  End If</font><font size="1"></p>
  <p></font><font color="#008000" size="1" face="Verdana">If
  Request.Form("TypeSearch") = "Phone" Then<br>
  SqlJunk = SqlJunk & " WHERE Phone LIKE '%"
             & _<br>
  Request.Form("DaInBox") & "%'"<br>
  End If</font><font size="1"></p>
  <p></font><font color="#008000" size="1" face="Verdana">If
  Request.Form("TypeSearch") = "Email" Then<br>
  SqlJunk = SqlJunk & " WHERE EMail LIKE '%"
             &_<br>
  Request.Form("DaInBox") & "%'"<br>
  End If</font>
  <p><font face="Verdana"> </font></p>
  <p align="center"><font color="#008000" size="1" face="Verdana">*****************<img src="http://www.aspalliance.com/glenncook/images/dinahacker.jpg" width="104" height="128">**********</font></p>
  </td>
  <td vAlign="top" width="49%"><small><font color="#ff0000" face="Verdana">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.</font></small>
  <p><small><font color="#ff0000" face="Verdana">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.<br>
  </font></small><font color="#ff0000" face="Verdana"><br>
  <small><em><strong>Here is where you have to do a little server
  administration:</strong> 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"))</em></small></font></p>
  <p><small><font color="#ff0000" face="Verdana">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!</font></small></p>
  <p><small><font color="#ff0000" face="Verdana">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)</font></small></p>
  <p><small><font color="#ff0000" face="Verdana">Anyway, the first line of
  every <strong>If</strong> statement <strong>Request</strong>s the <strong>Form</strong>
  field I call <strong>"TypeSearch"</strong> and asks if it's
  equal to a value- <strong>"FirstName"</strong>, <strong>"LastName"</strong>,<strong>"Title"</strong>,<strong>"Division"</strong>,
  etc.</font></small></p>
  <p><small><font color="#ff0000" face="Verdana">The second line of every
  "If" statement says, "If the above line was true, <strong>Then
  </strong>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, "<strong>SELECT</strong>/Find
  every record in the <strong>Employees</strong> table <strong>WHERE</strong>
  the <strong>"such&such"</strong> field has data that is
  similar/<strong>LIKE</strong> the input data the user has put in the box
  called <strong>"DaInBox." </strong>You'll notice that the
  Request.Form("DaInBox") is surrounded by </font></small><font color="#ff0000" face="Verdana"><strong>&</strong><small>,</small><strong>%</strong><small>,</small><strong>"</strong><small>,</small><strong>'</strong><small>.
    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.</small></font></p>
  <p><small><font color="#ff0000" face="Verdana">Each "If"
  statement concludes with an "End If."</font></small></p>
  </td>
 </tr>
 <tr>
  <td vAlign="top" width="51%"><font color="#008000" size="1" face="Verdana">Set
  rsGlobalWeb = Server.CreateObject("ADODB.Recordset")<br>
  rsGlobalWeb.Open SqlJunk, dbGlobalWeb, 3,3<br>
  %></font></td>
  <td vAlign="top" width="49%"><small><font color="#ff0000" face="Verdana">Here
  is where I create the <strong>ADODB.Recordset</strong>!  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.</font></small>
  <p><small><font color="#ff0000" face="Verdana">The second line on this
  page defines the attributes of your <strong>ADODB.Recordset</strong>
  object that I have named <strong>rsGlobalWeb</strong>. This line says,
  "Open the r<strong>sGlobalWeb</strong> recordset with the following
  SQL statement contained within the <strong>SqlJunk</strong> variable
  using the connection object we established earlier called <strong>dbGobalWeb</strong>." 
  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.</font></small></p>
  </td>
 </tr>
 <tr>
  <td vAlign="top" width="51%"><font face="Verdana"><font color="#008000"><font size="1"><%</font>
  <font size="1">If rsGlobalWeb.BOF and rsGlobalWeb.EOF Then</font> <font size="1">%></font></font><font color="#000000" size="1"><br>
  <<font color="#800080" size="1">h2 </font><font color="#ff0000" size="1">align="</font><font color="#0000ff" size="1">center</font><font color="#ff0000" size="1">"</font>>We
  did not find a match!<<font color="#800080" size="1">/h2</font>><br>
  </font><font color="#008000" size="1"><%Else%></font></font></td>
  <td vAlign="top" width="49%"><small><font color="#ff0000" face="Verdana">What
  this code says is, "If the recordset(<strong>rsGlobalWeb</strong>)
  is at the beginning(<strong>BOF</strong>) and end(<strong>EOF</strong>)
  of the recordset at the same time <strong>Then</strong> logically there
  is no recordset and logically there is no match!  If there is
  something <strong>Else </strong>other than nothing then go to the next
  line.</font></small></td>
 </tr>
 <tr>
  <td vAlign="top" width="51%"><font color="#008000" size="1" face="Verdana"><%If
  Not rsGlobalWeb.BOF Then%></font><font color="#000000" size="1">
  <p><font face="Verdana"><<font color="#800080" size="1">h2</font>>Here
  are the results of your search:<<font color="#800080" size="1">/h2</font>></font></p>
  <p><font face="Verdana"><<font color="#800080" size="1">table </font><font color="#ff0000" size="1">BORDER="</font><font color="#0000ff" size="1">0</font><font color="#ff0000" size="1">"
  width="</font><font color="#0000ff" size="1">100%</font><font color="#ff0000" size="1">"
  cellpadding="</font><font color="#0000ff" size="1">3</font><font color="#ff0000" size="1">"</font>></font></p>
  <p><font face="Verdana"><<font color="#800080" size="1">tr</font>></font></p>
  <p><font face="Verdana"><<font color="#800080" size="1">th </font><font color="#ff0000" size="1">bgcolor="</font><font color="#0000ff" size="1">#800000</font><font color="#ff0000" size="1">"</font>><<font color="#800080" size="1">font
  </font><font color="#ff0000" size="1">face="</font><font color="#0000ff" size="1">Arial</font><font color="#ff0000" size="1">"
  color="</font><font color="#0000ff" size="1">#FFFFFF</font><font color="#ff0000" size="1">"</font>>Name
  <<font color="#800080" size="1">/font</font>><<font color="#800080" size="1">/th</font>><br>
  <<font color="#800080" size="1">th </font><font color="#ff0000" size="1">bgcolor="</font><font color="#0000ff" size="1">#800000</font><font color="#ff0000" size="1">"</font>><<font color="#800080" size="1">font
  </font><font color="#ff0000" size="1">face="</font><font color="#0000ff" size="1">Arial</font><font color="#ff0000" size="1">"
  color="</font><font color="#0000ff" size="1">#FFFFFF</font><font color="#ff0000" size="1">"</font>>Title
  <br>
  <<font color="#800080" size="1">/font</font>><<font color="#800080" size="1">/th</font>><br>
  <<font color="#800080" size="1">th </font><font color="#ff0000" size="1">bgcolor="</font><font color="#0000ff" size="1">#800000</font><font color="#ff0000" size="1">"</font>><<font color="#800080" size="1">font
  </font><font color="#ff0000" size="1">face="</font><font color="#0000ff" size="1">Arial</font><font color="#ff0000" size="1">"
  color="</font><font color="#0000ff" size="1">#FFFFFF</font><font color="#ff0000" size="1">"</font>>Division
  <<font color="#800080" size="1">/font</font>><<font color="#800080" size="1">/th</font>><br>
  <<font color="#800080" size="1">th </font><font color="#ff0000" size="1">bgcolor="</font><font color="#0000ff" size="1">#800000</font><font color="#ff0000" size="1">"</font>><<font color="#800080" size="1">font
  </font><font color="#ff0000" size="1">face="</font><font color="#0000ff" size="1">Arial</font><font color="#ff0000" size="1">"
  color="</font><font color="#0000ff" size="1">#FFFFFF</font><font color="#ff0000" size="1">"</font>>Phone
  <<font color="#800080" size="1">/font</font>><<font color="#800080" size="1">/th</font>><br>
  <<font color="#800080" size="1">th </font><font color="#ff0000" size="1">bgcolor="</font><font color="#0000ff" size="1">#800000</font><font color="#ff0000" size="1">"</font>><<font color="#800080" size="1">font
  </font><font color="#ff0000" size="1">face="</font><font color="#0000ff" size="1">Arial</font><font color="#ff0000" size="1">"
  color="</font><font color="#0000ff" size="1">#FFFFFF</font><font color="#ff0000" size="1">"</font>>E-Mail
  <<font color="#800080" size="1">/font</font>><<font color="#800080" size="1">/th</font>><br>
  <<font color="#800080" size="1">/tr</font>><br>
  <font color="#008000" size="1"><% Do While Not rsGlobalWeb.EOF %></font><br>
  <<font color="#800080" size="1">tr</font>></font></p>
  <p><font face="Verdana"><<font color="#800080" size="1">td</font>><font color="#008000" size="1"><%=rsGlobalWeb("FirstName")%><br>
  &#32 <%=rsGlobalWeb("LastName")%></font></font></p>
  <p><font face="Verdana"><<font color="#800080" size="1">/td</font>></font></p>
  <p><font face="Verdana"><<font color="#800080" size="1">td</font>><font color="#008000" size="1"><%=rsGlobalWeb("Title")%></font></font></p>
  <p><font face="Verdana"><<font color="#800080" size="1">/td</font>></font></p>
  <p><font face="Verdana"><<font color="#800080" size="1">td</font>><font color="#008000" size="1"><%=rsGlobalWeb("Division")%></font></font></p>
  <p><font face="Verdana"><<font color="#800080" size="1">/td</font>></font></p>
  <p><font face="Verdana"><<font color="#800080" size="1">td</font>><font color="#008000" size="1"><%=rsGlobalWeb("Phone")%></font></font></p>
  <p><font face="Verdana"><<font color="#800080" size="1">/td</font>></font></p>
  <p><font face="Verdana"><<font color="#800080" size="1">td</font>><<font color="#800080" size="1">a
  </font><font color="#ff0000" size="1">href="</font><font color="#0000ff" size="1">mailto:</font><font color="#008000" size="1"><%=rsGlobalWeb("Email")%>"><br>
  <%=rsGlobalWeb("Email")%></font><<font color="#800080" size="1">/a</font>>
  <<font color="#800080" size="1">/td</font>></font></p>
  <p><font face="Verdana"><<font color="#800080" size="1">/tr</font>></font></p>
  <p></font><font color="#008000" size="1" face="Verdana"><%<br>
  rsGlobalWeb.MoveNext<br>
  Loop<br>
  %></font><font color="#000000" size="1"></p>
  <p><font face="Verdana"><<font color="#800080" size="1">/table</font>></font></p>
  <p></font><font size="1"><font color="#008000" face="Verdana"><%End
  If%></font></p>
  <p><font color="#008000" face="Verdana"><%End If%></font></font></p>
  </td>
  <td vAlign="top" width="49%"><small><font color="#ff0000" face="Verdana">The
  first line here basically says, "<strong>If</strong> the recordset
  is not at the <strong>BOF</strong> or "Beginning Of File" <strong>Then</strong>
  execute this HTML code which defines the header information for the
  query results."</font></small>
  <p><small><font color="#ff0000" face="Verdana">We make a pretty table
  with field headers that corresponds to the fields in the database.</font></small></p>
  <p><small><strong><font color="#ff0000" face="Verdana"><%Do While
  Not....%></font></strong><font color="#ff0000" face="Verdana">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 <strong><%=rsGlobalWeb("FieldName")%></strong></font></small></p>
  <p><small><font color="#ff0000" face="Verdana">The<strong> Loop </strong>statement
  just tells ASP to keep going back up to the <strong>Do While</strong>
  statement until it is finished.</font></small></p>
  <p><font face="Verdana"> </font></p>
  <p><small><font color="#ff0000" face="Verdana">Finally, remember to <strong>End</strong>
  your <strong>If</strong>'s.</font></small></p>
  </td>
 </tr>
 <tr>
  <td vAlign="top" width="51%"><font face="Verdana"><font color="#008000"><font size="1"><%</font><br>
  <font size="1">rsGlobalWeb.Close<br>
  dbGlobalWeb.Close<br>
  %></font></font><font color="#000000" size="1"><br>
  <<font color="#800080" size="1">/body</font>><br>
  <<font color="#800080" size="1">/html</font>></font></font></td>
  <td vAlign="top" width="49%"><small><font color="#ff0000" face="Verdana">I
  close the recordset and the connection to the database and I finish the
  HTML coding for the results page.</font></small></td>
 </tr>
 </tbody>
</table>
<p><small><font face="Verdana">Questions, Suggestions, Ideas? <a href="mailto:%[email protected]%22"><font color="mediumblue">Email
me!</font></a></font></small></p>