bschwarting
asked on
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/se arch_emp_n ame.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"
%>
########
HTML CODE
########
<b>Search by Employee Name</b>
<form method="post" action="/relyco/cgi-bin/se
<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
rs.open "select * from SalesLead where EmpName LIKE '" & Request.Form("EmpName") & "%' ORDER BY ID", "DSN=relyco"
%>
Can you post the error you are getting because your code looks fine
try replacing :
EmpName = Request( "EmpName" )
EmpName = Replace( EmpName,"'","''" )
to :
EmpName = CStr(trim(Request( "EmpName" )))
EmpName = CStr(trim(Replace(EmpName, "'", "''")))
EmpName = Request( "EmpName" )
EmpName = Replace( EmpName,"'","''" )
to :
EmpName = CStr(trim(Request( "EmpName" )))
EmpName = CStr(trim(Replace(EmpName,
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"
%>
<%
EmpName = Request( "EmpName" )
EmpName = Replace( EmpName,"'","''" )
Dim rs
Set rs = Server.CreateObject("ADODB
rs.open "select * from SalesLead where EmpName LIKE '" & EmpName & "%' ORDER BY ID", "DSN=relyco"
%>
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"
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"
there must be %% and you forgot to write the second %
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"
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"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
hit the nail on the head DireOrbAnt!!! thanks!
Others have posted a similar response as I was typing mine. I hope they get credit from it.
ASKER
DireOrbAnt, yours was the perfect syntax. the others were all off just a bit.