• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1140
  • Last Modified:

How to replace ' (apostrophe) on a database search.

Right now, when a user try's to search with an ' they get an error.  Normally, i can use the below syntax to replace the ' so it won't think it's an end of statement.  That's not working here.  What can I do in this instance?

########
HTML CODE
########

<b>Search by Employee Name</b>
<form method="post" action="/relyco/cgi-bin/search_emp_name.asp" name="form2">
<input type="text" name="EmpName" size="40"></form>

################
search_emp_name.asp
################
<%

EmpName                              = Request( "EmpName" )
EmpName                              = Replace(  EmpName,"'","''" )

Dim rs
Set rs = Server.CreateObject("ADODB.recordset")
rs.open "select * from SalesLead where EmpName LIKE '" & Request.Form("EmpName") & "%' ORDER BY ID", "DSN=relyco"

%>
0
bschwarting
Asked:
bschwarting
  • 4
  • 2
  • 2
  • +1
1 Solution
 
inviserCommented:
Can you post the error you are getting because your code looks fine
0
 
GhasanoCommented:
try replacing :
EmpName                         = Request( "EmpName" )
EmpName                         = Replace(  EmpName,"'","''" )

to :

EmpName = CStr(trim(Request( "EmpName" )))
EmpName = CStr(trim(Replace(EmpName, "'", "''")))



0
 
inviserCommented:
Nevermind, I found the problem, do this, you forgot the use EmpName in the query

<%
EmpName                         = Request( "EmpName" )
EmpName                         = Replace(  EmpName,"'","''" )

Dim rs
Set rs = Server.CreateObject("ADODB.recordset")
rs.open "select * from SalesLead where EmpName LIKE '" & EmpName & "%' ORDER BY ID", "DSN=relyco"
%>
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
GhasanoCommented:
change :
rs.open "select * from SalesLead where EmpName LIKE '" & Request.Form("EmpName") & "%' ORDER BY ID", "DSN=relyco"

to :

rs.open "select * from SalesLead where EmpName LIKE '%" & EmpName & "% ORDER BY ID", "DSN=relyco"
0
 
GhasanoCommented:
there must be %% and you forgot to write the second %
0
 
GhasanoCommented:
sorry i forgot ...
change
rs.open "select * from SalesLead where EmpName LIKE '%" & EmpName & "% ORDER BY ID", "DSN=relyco"

to

rs.open "select * from SalesLead where EmpName LIKE '%" & EmpName & "%' ORDER BY ID", "DSN=relyco"
0
 
DireOrbAntCommented:
bschwarting,

You forgot to use your formatted variable in the SQL call.

Instead of:
rs.open "select * from SalesLead where EmpName LIKE '" & Request.Form("EmpName") & "%' ORDER BY ID", "DSN=relyco"

Use:
rs.open "select * from SalesLead where EmpName LIKE '" & EmpName & "%' ORDER BY ID", "DSN=relyco"

That will fix your issue. However, you probably want to use Stored Procedure instead of calling a SELECT *. This will help in many ways. For one, you won't need to parse the quote, but it will also help for security (injection attacks), optimization and so on.
0
 
bschwartingAuthor Commented:
hit the nail on the head DireOrbAnt!!!  thanks!
0
 
DireOrbAntCommented:
Others have posted a similar response as I was typing mine. I hope they get credit from it.
0
 
bschwartingAuthor Commented:
DireOrbAnt, yours was the perfect syntax.  the others were all off just a bit.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now