<% if request("dept") <> "" then
response.write " Search Results for """ & request("dept") & " "" " &vbcrlf
end if
dim cnn ' Connection
dim rst ' Recordset
dim sqltext ' SQL Statement
dim deptname ' Temporary Dept ID
dim count ' Count of Departments
Set cnn = Server.CreateObject("ADODB.Connection")
Set rst = Server.CreateObject("ADODB.Recordset")
'open your connection using a connection string
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e:\Databases\DepartmentsTest2.mdb"
if Request("Dept") <> "" then
sqltext = "SELECT * FROM [DepartmentInfo Query2] WHERE DepartmentName LIKE '%%" & Request("dept") & "%%' ORDER BY DepartmentName"
else
sqltext = "SELECT * FROM [DepartmentInfo Query2] WHERE DepartmentId = " & Request("DeptId") & " ORDER BY DepartmentName"
end if
' sqltext = "SELECT * FROM [DepartmentInfo Query2] ORDER BY DepartmentName"
rst.Open sqltext,cnn,3,3
count = 0
do while not rst.eof
%>
<%
deptname = rst("DepartmentName")
rst.MoveNext
count = count + 1
loop
rst.Close
%>
<%
if (count = 1) and (count <> 0) then
%>
Staff
<%
sqltext = "SELECT * FROM [DepartmentInfo Query1] WHERE DepartmentName = '" & deptname & "' ORDER BY LastName"
rst.Open sqltext,cnn,3,3
do while not rst.eof
%>
Staff Name |
<%=rst("FirstName") & " " & rst("LastName") %> |
Title |
<%=rst("Title") %>  |
Office Location |
<%=rst("Office") %>  |
Phone |
ext. <%=rst("PhoneNumber") %>  |
E-Mail Address |
<%=rst("E-Mail1") %> |
<%=rst("E-Mail2") %> |
<%=rst("E-Mail3") %> |
Web Site |
"><%=rst("WebPage")%>  |
<%
rst.MoveNext
loop
rst.close
%>
|