Apostraphe Issues (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

stephenXedge -> Apostraphe Issues (12/11/2004 6:31:21)

Hey everyone, I know this has been brought up before... in fact I spent the last 15 minutes reading through posts on the issue, but to no avail. My question is this:

I've setup a form submit to a DRW, everything works except when you type in a search text that has an apostrophe in it (i.e. O'Keefe). You can test what I'm complaining about by visiting the site: http://www.carrp.org/memsearch.asp. Make sure the pull down box has Last Name selected and enter O'Keefe into the search text field. What happens next has been killing me for the past 2 days. If anyone knows how to get past this issue please drop me a line as soon as possible. Thanks tons.

-Stephen



BeTheBall -> RE: Apostraphe Issues (12/11/2004 14:01:16)

You will need to put the code on the Spooky Diet then modify the code. I would probably do this. Before the first line of the DRW (the red portion), insert something like this:

varlstname = Replace(Request.Form("lstname"),"'","''")

Then change your SQL to:

lstname LIKE '%"& lstname &"%'



stephenXedge -> RE: Apostraphe Issues (12/11/2004 15:03:39)

tried that, doesn't seem to work... btw, the textbox is called 'textvalue' not 'lstname'. I'm sure that makes a difference on how it's coded... but here is the actual code, maybe you can tell me. 

                  <!--webbot bot="DatabaseRegionStart" s-columnnames="IDNumber,lstname,fstname,mi,goesby,haddress,hcity,hstate,hzipcode,hzipend4,hareacode,hphone,dob,sexstatus,cname,caddress,ccity,cstate,czipcode,czipend4,ccountycode,cgcode,careacode,cphone,cfaxarea,cfaxnumb,email,web,lblcode,mtcode,joindate,CatCode,paidto,instplan,certif#,jtcode,jtdescri,degcode,awarddate,degmajor,institution,transcripts,application,newsletter,mlabels,mflag,mflagcheck,appdate,Web Password" s-columntypes="3,202,202,202,202,202,202,202,202,202,202,202,135,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,135,3,135,202,3,202,202,202,135,202,202,202,202,202,202,202,11,135,202" s-dataconnection="carrpmembershipmain" b-tableformat="FALSE" b-menuformat="FALSE" s-menuchoice s-menuvalue b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="FALSE" b-listseparator="FALSE" i-listformat="0" b-makeform="TRUE" s-recordsource="member" s-displaycolumns="fstname,lstname,mi,cname,caddress,ccity,cstate,czipcode,czipend4,careacode,cphone,cfaxarea,cfaxnumb,email,web,mtcode,degcode,degmajor,institution" s-criteria s-order="[lstname] +" s-sql="SELECT * FROM member WHERE (::type:: LIKE '%::textvalue::%') ORDER BY lstname ASC" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields s-norecordsfound="No member's information matched your search criteria.  Please change your search criteria and try again." i-maxrecords="0" i-groupsize="0" botid="0" u-dblib="_fpclass/fpdblib.inc" u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="BODY" preview="<table border=0 width="100%"><tr><td bgcolor="#FFFF00"><font color="#000000">This is the start of a Database Results region.</font></td></tr></table>" startspan --><!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM member WHERE (::type:: LIKE '%::textvalue::%') ORDER BY lstname ASC"
fp_sDefault=""
fp_sNoRecords="No member's information matched your search criteria.  Please change your search criteria and try again."
fp_sDataConn="carrpmembershipmain"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&IDNumber=3&lstname=202&fstname=202&mi=202&goesby=202&haddress=202&hcity=202&hstate=202&hzipcode=202&hzipend4=202&hareacode=202&hphone=202&dob=135&sexstatus=202&cname=202&caddress=202&ccity=202&cstate=202&czipcode=202&czipend4=202&ccountycode=202&cgcode=202&careacode=202&cphone=202&cfaxarea=202&cfaxnumb=202&email=202&web=202&lblcode=202&mtcode=202&joindate=135&CatCode=3&paidto=135&instplan=202&certif#=3&jtcode=202&jtdescri=202°code=202&awarddate=135°major=202&institution=202&transcripts=202&application=202&newsletter=202&mlabels=202&mflag=202&mflagcheck=11&appdate=135&Web Password=202&"
fp_iDisplayCols=19
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>



I can't seem to figure out exactly where each piece of your spooky diet would go. Please let me know, I'm dyin here! :).



BeTheBall -> RE: Apostraphe Issues (12/11/2004 16:13:15)

The Spooky Diet essentially gets rid of all the gray code except for the three include files. They look like this:

<!--#include file="_fpclass/fpdblib.inc"-->
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<!--#include file="_fpclass/fpdbrgn2.inc"-->

So, delete all the gray except those three pieces. When done, save the page. Then change the red to this:


<%
varlstname = Replace(Request.Form("textvalue"),"'","''")
fp_sQry="SELECT * FROM member WHERE (::type:: LIKE '%"&varlstname&"%') ORDER BY lstname ASC"
fp_sDefault=""
fp_sNoRecords="No member's information matched your search criteria. Please change your search criteria and try again."
fp_sDataConn="carrpmembershipmain"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&IDNumber=3&lstname=202&fstname=202&mi=202&goesby=202&haddress=202&hcity=202&hstate=202&hzipcode=202&hzipend4=202&hareacode=202&hphone=202&dob=135&sexstatus=202&cname=202&caddress=202&ccity=202&cstate=202&czipcode=202&czipend4=202&ccountycode=202&cgcode=202&careacode=202&cphone=202&cfaxarea=202&cfaxnumb=202&email=202&web=202&lblcode=202&mtcode=202&joindate=135&CatCode=3&paidto=135&instplan=202&certif#=3&jtcode=202&jtdescri=202°code=202&awarddate=135°major=202&institution=202&transcripts=202&application=202&newsletter=202&mlabels=202&mflag=202&mflagcheck=11&appdate=135&Web Password=202&"
fp_iDisplayCols=19
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>

Hope that explains it better.



stephenXedge -> RE: Apostraphe Issues (12/11/2004 16:26:47)

Now I'm getting this:

Database Results Error: mismatched parameter delimiters

:\

here's the code now:

quote:


<!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
varlstname = Replace(Request.Form("textvalue"),"'","''")
fp_sQry="SELECT * FROM member WHERE (::type:: LIKE '%"&varlstname&"%') ORDER BY lstname ASC"
fp_sDefault=""
fp_sNoRecords="No member's information matched your search criteria. Please change your search criteria and try again."
fp_sDataConn="carrpmembershipmain"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&IDNumber=3&lstname=202&fstname=202&mi=202&goesby=202&haddress=202&hcity=202&hstate=202&hzipcode=202&hzipend4=202&hareacode=202&hphone=202&dob=135&sexstatus=202&cname=202&caddress=202&ccity=202&cstate=202&czipcode=202&czipend4=202&ccountycode=202&cgcode=202&careacode=202&cphone=202&cfaxarea=202&cfaxnumb=202&email=202&web=202&lblcode=202&mtcode=202&joindate=135&CatCode=3&paidto=135&instplan=202&certif#=3&jtcode=202&jtdescri=202°code=202&awarddate=135°major=202&institution=202&transcripts=202&application=202&newsletter=202&mlabels=202&mflag=202&mflagcheck=11&appdate=135&Web Password=202&"
fp_iDisplayCols=19
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->



BeTheBall -> RE: Apostraphe Issues (12/11/2004 21:03:22)

What if you change the SQL to:

fp_sQry="SELECT * FROM member WHERE (lstname LIKE '%"&varlstname&"%') ORDER BY lstname ASC"



stephenXedge -> RE: Apostraphe Issues (12/11/2004 22:32:42)

wouldn't this have issues with the drop down box in the first portion where you select what method you want to search by (i.e. last name, company, zip code, or city)? That's the main problem I'm having here. Please review www.carrp.org/memsearch.asp one more time to see what I'm talking about.



BeTheBall -> RE: Apostraphe Issues (12/11/2004 23:04:51)

You are right. I forgot that the field to be searched was a variable, not fixed. I'll have to look at it further as the above should work. I am using the following two lines with no trouble:

varlstname = Replace(Request.Form("LName"),"'","''")
fp_sQry="SELECT * FROM Results WHERE (LName LIKE '%"&varlstname&"%') ORDER BY LName ASC"



stephenXedge -> RE: Apostraphe Issues (12/11/2004 23:07:00)

so are you saying those two lines should work as is right now, or should I wait for the variables to be put into play and then use the new method?



BeTheBall -> RE: Apostraphe Issues (12/11/2004 23:26:33)

Yes, I am saying I don't see why it isn't working. Edit the red code as shown below so we can see what SQL is actually generating:

<%
varlstname = Replace(Request.Form("textvalue"),"'","''")
fp_sQry="SELECT * FROM member WHERE (::type:: LIKE '%"&varlstname&"%') ORDER BY lstname ASC"
fp_sDefault=""
fp_sNoRecords="No member's information matched your search criteria. Please change your search criteria and try again."
fp_sDataConn="carrpmembershipmain"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&IDNumber=3&lstname=202&fstname=202&mi=202&goesby=202&haddress=202&hcity=202&hstate=202&hzipcode=202&hzipend4=202&hareacode=202&hphone=202&dob=135&sexstatus=202&cname=202&caddress=202&ccity=202&cstate=202&czipcode=202&czipend4=202&ccountycode=202&cgcode=202&careacode=202&cphone=202&cfaxarea=202&cfaxnumb=202&email=202&web=202&lblcode=202&mtcode=202&joindate=135&CatCode=3&paidto=135&instplan=202&certif#=3&jtcode=202&jtdescri=202°code=202&awarddate=135°major=202&institution=202&transcripts=202&application=202&newsletter=202&mlabels=202&mflag=202&mflagcheck=11&appdate=135&Web Password=202&"
fp_iDisplayCols=19
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
Response.write fp_sQry
%>



stephenXedge -> RE: Apostraphe Issues (12/11/2004 23:32:13)

SELECT * FROM member WHERE (::type:: LIKE '%%') ORDER BY lstname ASCDatabase Results Error: mismatched parameter delimiters

the new error message that I'm getting with your recent addition.

Give it a try and see if you get the same thing.



BeTheBall -> RE: Apostraphe Issues (12/11/2004 23:37:51)

See if this helps any:

<%
vartype = Request.Form("type")
varlstname = Replace(Request.Form("textvalue"),"'","''")
fp_sQry="SELECT * FROM member WHERE ("&vartype&" LIKE '%"&varlstname&"%') ORDER BY lstname ASC"
fp_sDefault=""
fp_sNoRecords="No member's information matched your search criteria. Please change your search criteria and try again."
fp_sDataConn="carrpmembershipmain"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&IDNumber=3&lstname=202&fstname=202&mi=202&goesby=202&haddress=202&hcity=202&hstate=202&hzipcode=202&hzipend4=202&hareacode=202&hphone=202&dob=135&sexstatus=202&cname=202&caddress=202&ccity=202&cstate=202&czipcode=202&czipend4=202&ccountycode=202&cgcode=202&careacode=202&cphone=202&cfaxarea=202&cfaxnumb=202&email=202&web=202&lblcode=202&mtcode=202&joindate=135&CatCode=3&paidto=135&instplan=202&certif#=3&jtcode=202&jtdescri=202°code=202&awarddate=135°major=202&institution=202&transcripts=202&application=202&newsletter=202&mlabels=202&mflag=202&mflagcheck=11&appdate=135&Web Password=202&"
fp_iDisplayCols=19
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
Response.write fp_sQry
%>



stephenXedge -> RE: Apostraphe Issues (12/11/2004 23:40:02)

Microsoft VBScript runtime error '800a01f5'

Illegal assignment: 'vartype'

/memsearchlist.asp, line 69



BeTheBall -> RE: Apostraphe Issues (12/11/2004 23:53:39)

Guess vartype is not a good idea. Try:


varsearchfield = Request.Form("type")

instead of

vartype = Request.Form("type")



stephenXedge -> RE: Apostraphe Issues (12/11/2004 23:57:08)

gives me this

quote:

Microsoft VBScript runtime error '800a01c2'

Wrong number of arguments or invalid property assignment: 'vartype'

/memsearchlist.asp, line 71


here is how the code looks.





quote:

<%
varsearchfield = Request.Form("type")
varlstname = Replace(Request.Form("textvalue"),"'","''")
fp_sQry="SELECT * FROM member WHERE ("&vartype&" LIKE '%"&varlstname&"%') ORDER BY lstname ASC"
fp_sDefault=""
fp_sNoRecords="No member's information matched your search criteria. Please change your search criteria and try again."
fp_sDataConn="carrpmembershipmain"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&IDNumber=3&lstname=202&fstname=202&mi=202&goesby=202&haddress=202&hcity=202&hstate=202&hzipcode=202&hzipend4=202&hareacode=202&hphone=202&dob=135&sexstatus=202&cname=202&caddress=202&ccity=202&cstate=202&czipcode=202&czipend4=202&ccountycode=202&cgcode=202&careacode=202&cphone=202&cfaxarea=202&cfaxnumb=202&email=202&web=202&lblcode=202&mtcode=202&joindate=135&CatCode=3&paidto=135&instplan=202&certif#=3&jtcode=202&jtdescri=202°code=202&awarddate=135°major=202&institution=202&transcripts=202&application=202&newsletter=202&mlabels=202&mflag=202&mflagcheck=11&appdate=135&Web Password=202&"
fp_iDisplayCols=19
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
Response.write fp_sQry
%>



BeTheBall -> RE: Apostraphe Issues (12/12/2004 0:00:19)

Sorry, you need to change the variable in the SQL as well:

fp_sQry="SELECT * FROM member WHERE ("&searchfield&" LIKE '%"&varlstname&"%') ORDER BY lstname ASC"



stephenXedge -> RE: Apostraphe Issues (12/12/2004 0:02:43)

back to this one:

SELECT * FROM member WHERE ( LIKE '%%') ORDER BY lstname ASCDatabase Results Error: mismatched parameter delimiters



BeTheBall -> RE: Apostraphe Issues (12/12/2004 0:11:15)

OK, running out of ideas. See what this does:

<%
varsearchfield = Request("type")
varlstname = Replace(Request("textvalue"),"'","''")
fp_sQry="SELECT * FROM member WHERE ("&vartype&" LIKE '%"&varlstname&"%') ORDER BY lstname ASC"
fp_sDefault=""
fp_sNoRecords="No member's information matched your search criteria. Please change your search criteria and try again."
fp_sDataConn="carrpmembershipmain"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&IDNumber=3&lstname=202&fstname=202&mi=202&goesby=202&haddress=202&hcity=202&hstate=202&hzipcode=202&hzipend4=202&hareacode=202&hphone=202&dob=135&sexstatus=202&cname=202&caddress=202&ccity=202&cstate=202&czipcode=202&czipend4=202&ccountycode=202&cgcode=202&careacode=202&cphone=202&cfaxarea=202&cfaxnumb=202&email=202&web=202&lblcode=202&mtcode=202&joindate=135&CatCode=3&paidto=135&instplan=202&certif#=3&jtcode=202&jtdescri=202°code=202&awarddate=135°major=202&institution=202&transcripts=202&application=202&newsletter=202&mlabels=202&mflag=202&mflagcheck=11&appdate=135&Web Password=202&"
fp_iDisplayCols=19
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
Response.write fp_sQry&"<br>"&varsearchfield&"<br>"&varlstname
%>



stephenXedge -> RE: Apostraphe Issues (12/12/2004 0:16:44)

Microsoft VBScript runtime error '800a01c2'

Wrong number of arguments or invalid property assignment: 'vartype'

/memsearchlist.asp, line 71



BeTheBall -> RE: Apostraphe Issues (12/12/2004 0:19:33)

Sorry, I cut and pasted from an earlier post and forgot to fix the SQL

fp_sQry="SELECT * FROM member WHERE ("&varsearchfield&" LIKE '%"&varlstname&"%') ORDER BY lstname ASC" [/quote]



stephenXedge -> RE: Apostraphe Issues (12/12/2004 0:24:34)

I love you :) lol, in a purely plutonic way of course. I took out the bottom write text and it looks perfect now. Thanks SO MUCH Duane you have no idea what a huge help this was.



BeTheBall -> RE: Apostraphe Issues (12/12/2004 0:27:37)

I think I learned something too. I think since your form used the GET method instead of POST, causes Request.Form to not be recognized. That's why Request instead of Request.Form seemed to do the trick.



stephenXedge -> RE: Apostraphe Issues (12/12/2004 0:29:08)

Well I'm glad we both benefitted from this in some way. Thanks again for all of your help over the last 10 hours man :)



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.09375