asked on

How do I correctly code a results line so it will include an apostrophe when there is one in a person's name?

I have created an ASP project that grabs data from an MS Access database. Unfortunately, I am getting an Syntax error (missing operator) in query expression when the person's last name has an apostrophe in the name.

How do I fix this line of code? It is the LastName line. Thanks in advance.
Kurt

<td style="width:90px; font-family:Arial; font-size:10pt"><b><%= mRS.Fields("LastName").Value %></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"><%= formatCurrency(mRS.fields("Salary").Value,2) %></td>
      <td style="width:129px; font-family:Arial; font-size:9pt"><%= formatCurrency(mRS.fields("PositionBudget").Value,2) %></td>
    </tr>

Open in new window

Wayne Barron

Replace yours with this
<%= replace(mRS("LastName),"'", "''")%>

Carrzkiss

kfranck

ASKER

Carrzkiss:

I did this:

<%= replace(mRS("LastName),"'", "''")%>
<%= mRS.fields("FirstName").Value %></td>

Now, I am getting an (HTTP 500 Internal Server Error) means that the website you are visiting had a server problem which prevented the webpage from displaying

Suggestions?

Wayne Barron

Is this from a search page?
Or from what?

I think I provided the wrong code for you there.
Need to know what it is being used for.
As displaying records should be ok.

Wayne Barron

the code I supplied should do it.
Just let me know what this is for, and I will help you to trouble shot it.

Carrzkiss

Wayne Barron

Sorry, left out something

<%= replace(rsMem("MyLN"),"'", "''")%>

Try this.
But this will add in an addition '' in the name.
Usually you do not get an error from reading a record to the page.
Usually it happens when you write to the database does the single quotes and apostrophes cause issues??

Would like to see this issue in action.

Anyway.
Please your code with this
<%= replace(rsMem("MyLN"),"'", "''")%>
And you should be good to go....

Carrzkiss

Wayne Barron

<%= replace(rsMem("LastName"),"'", "''")%>

kfranck

ASKER

Now I am getting this error:
Last Name First Name Title Department Description Annual Salary Position Budget
Microsoft VBScript runtime error '800a000d'

Type mismatch: 'rsMem'

/ut/search.asp, line 99

I am confused because I am using "mRS.fields" not "rsMem". And to answer your question, this is the search.asp page. I am including the page as a code snippet. Thanks.

<!--#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")))
 
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='" & request.form("LastName") & "'"
end if
 
if len(trim(request.form("FirstName"))) > 0 then
  mSQL = mSQL & " and FirstName='" & trim(request.form("FirstName")) & "'"
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='" & request.form("LastName") & "' OR FirstName='" & trim(request.form("FirstName")) & "' ORDER BY FirstName ASC"
 
   mRs.Open mSQL, mConn
End if
%>
<html>
 
 
<head>
<title>University of Toledo Employee Database ~ Your SOURCE</title>
 
</head>
<body> 
<div align="center">
	&nbsp;<table border="0" cellspacing="0" width="393" id="table1">
		<tr>
		  <td align="center" style="border-top: 2px solid #002E89; ">
			<p align="left">
			<img border="0" src="images2/UTtitle.gif" width="345" height="22"><br>
			<br>
&nbsp;</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">&gt;&gt;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">
&nbsp;<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><%= replace(rsMem("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"><%= formatCurrency(mRS.fields("Salary").Value,2) %></td>
      <td style="width:129px; font-family:Arial; font-size:9pt"><%= formatCurrency(mRS.fields("PositionBudget").Value,2) %></td>
    </tr>
      <%       
    mRS.Movenext
loop
      %>
</table>
</div>
</body>
</html>

Open in new window

ASKER CERTIFIED SOLUTION

Wayne Barron

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kfranck

ASKER

Works like a charm. I thank you.

Wayne Barron

Good.
I did that without any testing, so glad that it worked for you.

Carrzkiss

Author Comments:
Works like a charm. I thank you.