[thelist] ASP & SQL (full page code attached)

Erik Bennett ebennett at ecasd.k12.wi.us
Fri Sep 13 14:39:01 CDT 2002


<snip>
Add this to your form <input type="hidden" name="action" value="lookup" />

Then, wrap the query and output in this conditional:

If Request.Form("action") = "lookup" Then
  ' Do the query
End If

Then later:

If Request.Form("action") = "lookup" Then
  ' Do the display
End If
</snip>

Thanks joshua for the speedy reply. :-) I can't for the life of me make this
work. Where am I supposed to put this stuff? Also, I have a little snippet
in the table that's being displayed for color coding every-other row. And
when I think I've got your code in the right place it pukes out on that.

I aplogize if the code is messy. Like I said, ASP and SQL newbie. :-)

Any ideas?

Erik

Here's the code (watch for wrapping)

<%
on  error resume next
last_name=request.form("last_name")
first_name=request.form("first_name")
username=request.form("username")
location=request.form("location")

Set Conn = Server.CreateObject("ADODB.Connection")
conn.open "DSN=staff"

strSQL = "SELECT last_name, first_name, title, username, location, email "
strSQL = strSQL & " FROM staff"
strSQL = strSQL & " WHERE 1 = 1 "
If last_name <> "" Then
  strSQL = strSQL & " AND last_name = '" & last_name & "'"
End If
If first_name <> "" Then
  strSQL = strSQL & " AND first_name = '" & first_name & "'"
End If
If username <> "" Then
  strSQL = strSQL & " AND username = '" & username & "'"
End If
If location <> "" Then
  strSQL = strSQL & " AND location = '" & location & "'"
End If

Set RS = Conn.Execute(strSQL)
%>

<table width="640" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td width="640">
    <p>
      <b><font size="1" face="Arial">You Are Here: <a
href="http://www.ecasd.k12.wi.us/">ECASD
      Home</a> &gt; <a href="index.html"><u>Directory</u></a>
      &gt; <u>ECASD District Employee Search</u></font></b>
    <p>
      <img border="0" src="../images/lines/directory.gif">
    <table border="0" width="620">
      <tr>
        <td width="100%">
    <p>
      <font size="1" face="Arial">You can also browse the Central Office
Directory by
      Department <b><a href="index.html">Here</a></b>.
      </font>
    <p>
      <font size="1" face="Arial">Our new search page now allows searching
on all of the fields you enter. If you search for John Doe, it will return
only the entries pertaining to John Doe.<br>
      </font>
	<form METHOD="POST" ACTION="search2.asp">
	<input type="hidden" name="action" value="lookup">
      <table BORDER="0" width="90%">
        <tr>
          <td><b><font face="Arial" size="2">Last Name:</font></b></td>
          <td><font face="Arial" size="2"><input name="last_name"
size="20"></font></td>
          <td><b><font face="Arial" size="2">First Name:</font></b></td>
          <td><font face="Arial" size="2"><input name="first_name"
size="20"></font></td>
        </tr>
        <tr>
          <td><b><font face="Arial" size="2">Username:</font></b></td>
          <td><font face="Arial" size="2"><input name="username"
size="20"></font></td>
          <td><b><font face="Arial" size="2">Location:</font></b></td>
          <td><font size="2" face="Arial"><Select name="location" size="1">
                <option selected value="">Select One</option>
                <option value="">--------------------</option>
                <option value="Central Office">Central Office</option>
                <option value="Cleghorn">Cleghorn</option>
                <option value="Delong">Delong</option>
                <option value="Flynn">Flynn</option>
                <option value="Lakeshore">Lakeshore</option>
                <option value="Lincoln">Lincoln</option>
                <option value="Little Red">Little Red</option>
                <option value="Locust Lane">Locust Lane</option>
                <option value="Longfellow">Longfellow</option>
                <option value="Lowes Creek">Lowes Creek</option>
                <option value="Manz">Manz</option>
                <option value="McKinley Charter">McKinley Charter</option>
                <option value="Meadowview">Meadowview</option>
                <option value="Memorial">Memorial</option>
                <option value="North">North</option>
                <option value="Northstar">Northstar</option>
                <option value="Northwoods">Northwoods</option>
                <option value="Putnam Heights">Putnam Heights</option>
                <option value="Robbins">Robbins</option>
                <option value="Roosevelt">Roosevelt</option>
                <option value="Sam Davey">Sam Davey</option>
                <option value="Service Center">Service Center</option>
                <option value="Sherman">Sherman</option>
                <option value="South">South</option>
                <option>--------------------</option>
                <option value="Academy">Academy</option>
                <option value="CDC">CDC</option>
                <option value="Detention Center">Detention Center</option>
                <option value="Mobile Classroom">Mobile Classroom</option>
              </select></font></td>
        </tr>
      </table>
      <br>
      <input TYPE="Submit"><input TYPE="Reset">
    </form>
    <table width="612" border="1">
      <thead>
        <tr>
          <td width="105"><b><font face="Arial" size="1"><u>Last
Name</u></font></b></td>
          <td width="83"><b><font face="Arial" size="1"><u>First
Name</u></font></b></td>
          <td width="177"><b><font face="Arial" size="1"><u>Job
Title</u></font></b></td>
          <td width="111"><b><font face="Arial"
size="1"><u>Location</u></font></b></td>
          <td width="87"><b><font face="Arial"
size="1"><u>Username</u></font></b></td>
          <td width="87"><b><font face="Arial" size="1"><u>Email
Address</u></font></b></td>
        </tr>
      </thead>
      <tbody>
<% Do While Not RS.EOF %>
<%
if intRowColor = 0 Then
    	Response.Write "<tr bgcolor=""#00ffcc"">"
    	intRowColor = 1
    Else
    	Response.Write "<tr bgcolor=""#ffffff"">"
    	intRowColor = 0
    End if
%>
          <td width="105"><font face="Arial" size="1"><%=RS("last_name")
%></font></td>
          <td width="83"><font face="Arial" size="1"><%=RS("first_name")
%></font></td>
          <td width="177"><font face="Arial" size="1"><%=RS("title")
%></font></td>
          <td width="111"><font face="Arial" size="1"><%=RS("location")
%></font></td>
          <td width="87"><font face="Arial" size="1"><%=RS("username")
%></font></td>
          <td width="87"><font face="Arial" size="1"><%=RS("email")
%></font></td>
        </tr>
<%
	RS.MoveNext
	Loop
%>
    </table>
    <p>
      <br>
        </td>
      </tr>
    </table>
    </td>
  </tr>

<%
'Closes the variables and db connection
RS.Close
Conn.Close
%>
<!--End -->
</table>
    </td>
  </tr>
</table>




More information about the thelist mailing list