Adhoc ASP Search page

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

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."

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.

<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%>

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.