How can I correct the code for Last Name with punctuation and another search field for any word in that field?
I have created an ASP/MS Access project that searches by Last Name and Department. In the Last Name search, I need to be able to search for last names that have punctuation (such as an apostrophe).
In the department search, I want to be able to return any word the user may enter. For example, if a user enters "athletics," results should be returned even though the data entered is "athletics admin". Or if I enter "Orthopedics", I want to get results when the data in fact is listed as "Resid Prgm-Orthopedics"?
With the help of EE, I was able to get the Last Name search with apoostrophe to work, and later the department element to work.
But now, when I search by Last Name (such as Jacobs), Jacobs does not show up.
Could you give suggestions on where my coding is flawed? Thanks in advance.
kfranck
In the department search, I want to be able to return any word the user may enter. For example, if a user enters "athletics," results should be returned even though the data entered is "athletics admin". Or if I enter "Orthopedics", I want to get results when the data in fact is listed as "Resid Prgm-Orthopedics"?
With the help of EE, I was able to get the Last Name search with apoostrophe to work, and later the department element to work.
But now, when I search by Last Name (such as Jacobs), Jacobs does not show up.
Could you give suggestions on where my coding is flawed? Thanks in advance.
kfranck
Here is the code:
<%
response.write trim(UCASE(request.form("LastName")))
strLastName = request.form("LastName")
if len(trim(request.form("LastName"))) > 0 then
mSQL = "select projects.LastName, projects.FirstName, projects.Department, projects.Title, projects.Salary, projects.PositionBudget, projects.ID FROM projects from projects where LastName='" & replace(strLastName,"'", "''") & "'"
end if
if len(trim(request.form("Department"))) > 0 then
mSQL = mSQL & " and Department='" & trim(request.form("Department")) & "'"
end if
if mSQL = "" Then
Response.Write("")
' or whatever error you want to do so you do NOT run the OPEN query
Response.End() ' so nothing else is processed
Else
mSQL = "SELECT projects.LastName, projects.FirstName, projects.Department, projects.Title, projects.Salary, projects.PositionBudget, projects.ID FROM projects WHERE LastName='" & replace(strLastName,"'", "''") & "' OR Department like('%" & trim(request.form("Department")) & "%') ORDER BY FirstName ASC"
mRs.Open mSQL, mConn
End if
%>
ASKER
Carrzkiss:
I still have a problem. If I enter a Last Name in that field, I get all the positions that are VACANT. Only VACANT positions show up. Plus, I get this error at the bottom of the file:
Microsoft VBScript runtime error '800a005e'
Invalid use of Null: 'replace'
/ut/search.asp, line 100
Line 100 is <%= replace(mRS("LastName"),"'
The Department search field works Fine.
Suggestions.
I still have a problem. If I enter a Last Name in that field, I get all the positions that are VACANT. Only VACANT positions show up. Plus, I get this error at the bottom of the file:
Microsoft VBScript runtime error '800a005e'
Invalid use of Null: 'replace'
/ut/search.asp, line 100
Line 100 is <%= replace(mRS("LastName"),"'
The Department search field works Fine.
Suggestions.
send me over a link so I can see what you are referring too.
With details on how to reproduce what you are seeing.
With details on how to reproduce what you are seeing.
ASKER
Carrzkiss:
I am sending the search and data pages as text documents and the database. I did not send the form page. It just has a form field for Last Name and another form field for Department.
kfranck
search.txt
data.txt
UTemployees.mdb
I am sending the search and data pages as text documents and the database. I did not send the form page. It just has a form field for Last Name and another form field for Department.
kfranck
search.txt
data.txt
UTemployees.mdb
I need the actual search page that you type in your Query into?
never mind
ASKER
Give this a shot.
The Apostophe is fixed
The Search Query is fixed for short (or) long name
The Currency Format is fixed.
Have a good one.
Carrzkiss
The Apostophe is fixed
The Search Query is fixed for short (or) long name
The Currency Format is fixed.
Have a good one.
Carrzkiss
<!--#include file="data.asp"-->
<!--
A { text-decoration:none }
-->
<STYLE TYPE="text/css">
<!--
A:link {text-decoration: none}
A:visited{text-decoration:none}
A:active{text-decoration:none}
a:hover{color: #CA0002;text-decoration:underline}
-->
</STYLE>
<head>
<style type="text/css">
.oddRow{background-color:#FFFFFF;}
.evenRow{background-color:#C0C0C0;}
</style>
<%
'response.write trim(UCASE(request.form("LastName")))
'strLastName = request.form("LastName")
if request.form("LastName") = "" then
strLastName = "%"
else
strLastName = request.form("LastName")
strLastName = replace(strLastName, " ", "%' and LastName like '%", 1)
strLastName = "%" & strLastName & "%"
if len(trim(request.form("LastName"))) > 0 then
mSQL = "select projects.LastName, projects.FirstName, projects.Department, projects.Title, projects.Salary, projects.PositionBudget, projects.ID FROM projects where LastName like '" & replace(strLastName,"'","''") & "'"
end if
if len(trim(request.form("Department"))) > 0 then
mSQL = mSQL & " and Department='" & trim(request.form("Department")) & "'"
end if
if mSQL = "" Then
Response.Write("")
' or whatever error you want to do so you do NOT run the OPEN query
Response.End() ' so nothing else is processed
Else
mSQL = "SELECT projects.LastName, projects.FirstName, projects.Department, projects.Title, projects.Salary, projects.PositionBudget, projects.ID FROM projects WHERE LastName like '" & replace(strLastName,"'","''") & "' OR Department like('" & trim(request.form("Department")) & "') ORDER BY FirstName ASC"
mRs.Open mSQL, mConn
End if
End if
%>
<html>
<head>
<title>University of Toledo Employee Database ~ Your SOURCE</title>
</head>
<body>
<div align="center">
<table border="0" cellspacing="0" width="359" id="table1">
<tr>
<td align="center" style="border-top: 2px solid #002E89; ">
<p align="left">
<br>
<img border="0" src="images2/UTtitleSearch.gif" width="354" height="62"><br>
</td>
</tr>
<tr>
<td align="center" style="border-bottom: 2px solid #002E89; ">
<p align="right"><b><font size="2" face="Arial">
<a href="http://yoursource/UT/UTEmployeeSearch.asp">>>Make another search</a></font></b></td>
</tr>
</table>
</div>
<style type="text/css">
#MainTR{
color:#FFFFFF;
font-family:Arial, Helvetica, sans-serif;
font-size:8pt;
background-color:#9d0014;
border: 1px solid #C0C0C0;
text-align:left;
}
</style><div align="center">
<table BORDER="0" width="859" cellspacing="1">
<tr id="MainTR">
<th style="background-color: #002E89"><font size="2">Last Name</font></th>
<th style="background-color: #002E89"><font size="2">First Name</font></th>
<th style="background-color: #002E89"><font size="2">Title</font></th>
<th style="background-color: #002E89"><font size="2">Department Description</font></th>
<th style="background-color: #002E89"><font size="2">Annual Salary</font></th>
<th style="background-color: #002E89"><font size="2">Position Budget</font></th>
</tr>
<%
'cycle through result set
Dim counter,theClass
counter=0
do while not mRS.EOF
counter = counter + 1
theClass="oddRow"
If (counter Mod 2 ) = 0 Then
theClass="evenRow"
End If
%>
<tr class="<%=theClass%>">
<td style="width:90px; font-family:Arial; font-size:11pt"><b><%= mRS("LastName")%></b></td>
<td style="width:115px; font-family:Arial; font-size:9pt"><%= mRS.fields("FirstName").Value %></td>
<td style="width:175px; font-family:Arial; font-size:9pt"><%= mRS.fields("Title").Value %></td>
<td style="width:207px; font-family:Arial; font-size:9pt"><%= mRS.fields("Department").Value %></td>
<td style="width:119px; font-family:Arial; font-size:9pt"><%= formatnumber(mRS.fields("Salary").Value,2) %></td>
<td style="width:129px; font-family:Arial; font-size:9pt"><%= formatnumber(mRS.fields("PositionBudget").Value,2) %></td>
</tr>
<%
mRS.Movenext
loop
%>
</table>
</div>
</body>
</html>
ASKER
The Last Name field works great!
However, the Department field does not. When I enter "swimming" or "athletics" in the Department field, I get this error:
ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.
/UT/search.asp, line 102
Line 102 is "do while not mRS.EOF"
Suggestions?
However, the Department field does not. When I enter "swimming" or "athletics" in the Department field, I get this error:
ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.
/UT/search.asp, line 102
Line 102 is "do while not mRS.EOF"
Suggestions?
checking in on it.
be back with you in a few.
be back with you in a few.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Absolutely perfect! Just what we needed. I went through the code and learn some things. Thanks for the speed, the fixes and the extra work. Very appreciated.
kfranck
kfranck
Carrzkiss
Open in new window