?
Solved

How to delimit a single-quote on an input form when using the Database Results component

Posted on 2003-03-27
33
Medium Priority
?
241 Views
Last Modified: 2013-12-24
I am querying a Microsoft Access database on an Active Server Page using FrontPage 2000 as my authoring software.  Using the Database Results component, I have set everything up and all is working well, with one exception.  Using the component wizard, I created an input form to allow users to query the database.

First, here's the SQL statement that I'm using.

SELECT * FROM Available WHERE ((BotanicName LIKE '%::BotanicName::%' OR CommonName LIKE '%::CommonName::%') AND Size LIKE '%::Size::%') ORDER BY BotanicName ASC,Size ASC,CommonName ASC

I am new to SQL and it appears that this statement uses the single-quote character as the delimiter.  Therein lies my problem, because my data contains this character, as well as double-quotes.  My application queries data from a horticultural database.  The industry norm for botanic name descriptions of plant material uses single quotes and size description uses both single and double-quotes.
Here are some examples:
Botanic:  ACER PLATANOIDES 'CRIMSON KING'
Size   :  #5  or  12'  or 20-24"  

My question is this.  Can I construct an SQL statement, that will retrieve this information?  Right now I get this error, when using a single quote in the search.

Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '((BotanicName LIKE '%'CRIMSON KING'%' OR CommonName LIKE '%~%') AND Size LIKE '%.%')'.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers

As I mentioned, other that this specific problem, everything is working exactly as desired.

I did pose this same question in another part of EE and the suggestion was made to use a function that replaces the single-quote with two single-quote characters.  I wasn't able to make this work.  I first tried to add the function code inside of the ASP code generated by the Database Results component and FrontPage won't allow this.  I then placed the function outside of the component code and it wasn't recognized when the component code executed.  However, if I manually enter two single-quotes on my input form, the search executes correctly.

So, I need to be able to delimit a single-quote, which is entered by the user on an input form created by the FrontPage 2000 Database Results wizard.

Thanks for your help.
0
Comment
Question by:ggs54
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 19
  • 14
33 Comments
 
LVL 14

Expert Comment

by:hhammash
ID: 8218536
Hi,

First your SQL has two Brackets open and one closed.

I'll see about the other problem.

hhammash
0
 
LVL 1

Author Comment

by:ggs54
ID: 8218754
I believe the SQL statement is bracketed correctly.  It verifies properly when connecting to the database and has two open and two closed.

SELECT * FROM Available WHERE  ( (  BotanicName LIKE '%::BotanicName::%' OR CommonName LIKE '%::CommonName::%'  )   AND Size LIKE '%::Size::%'  ) ORDER BY BotanicName ASC,Size ASC,CommonName ASC
0
 
LVL 14

Expert Comment

by:hhammash
ID: 8218802
Hi,

So,  now you want to look for names like:

O'Neil
O'Brien

I mean words with Apostrophe in you data.

I did it before,  I am just looking in my files.


Regards
hhammash
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 14

Expert Comment

by:hhammash
ID: 8218887
Hi,

Use:

SELECT * FROM Names WHERE (Name LIKE '::Name::%')

Do not use '%::FieldName::%'

just the text begins with.

Your cose should be:
((BotanicName LIKE '%::CRIMSON KING::%' OR CommonName LIKE '~%') AND Size LIKE '.%')

Why are you using ~ and . in the search in CommonName Field.

But to find a word with and apostrophe you can use the '::FieldName::%'

Regards
hhammash
0
 
LVL 14

Expert Comment

by:hhammash
ID: 8218896
Hi,

Check this:

SELECT * FROM Available WHERE ((BotanicName LIKE '::BotanicName::%' OR CommonName LIKE '::CommonName::%') AND Size LIKE '::Size::%') ORDER BY BotanicName ASC,Size ASC,CommonName ASC

You can use the %::FieldName::% (Contains) with fields that do not have the apostrohpe in the data,  but with fields like text fields where you suspect to have something like O'Brien or O'Neil  then you the Begins with,  not the Containt.  I.e  '::FieldName::%'

Regards
hhammash

0
 
LVL 14

Expert Comment

by:hhammash
ID: 8218985
Hi,

If the word with the apostrophe is not at the beginning of the field,  then we have to put some code on the Dieted DRW before the fp_sQry= ...


Here it is.

fp_sQry="SELECT * FROM Names WHERE Name LIKE '%" & Replace(Request.Form("Name"),"'","''") & "%'"

If you search for O'Brien it will find,  
O'Brien James
Thomas O'Brien
Cecil O'Brien G


Best regards
hhammash
0
 
LVL 14

Expert Comment

by:hhammash
ID: 8218999
Hi,

Using your SQL:


fp_sQry="SELECT * FROM Available WHERE BotanicName LIKE '%" & Replace(Request.Form("BotanicName"),"'","''") & "%'"

You can continue for the rest of the fields.

Hhammash
0
 
LVL 1

Author Comment

by:ggs54
ID: 8219154
Your suggestion does work.  All three of my search fields could contain the single-quote and I need to be able to give my users the ability to put in part of the name, not necessarily the beginning, because some of these botanic names can be tricky.

For example, ACER PLATANOIDES 'ROYAL RED'
It's not likely that a user will know     ACER PLATANOIDES 'ROYAL , but they will know 'ROYAL .  I know that you could just say, omit the single-quote, but the single-quote is how they recognize the item and therefore will use it.

I am using the "~" as the default for Common Name because my data does not contain this character and, if they leave this field blank, I don't want it to find anything.

On the otherhand, I am using the "." for Size because my data does contain that character and I want ALL sizes to be found in the event that they leave the Size field blank.

As I mentioned, it would be really helpful to be able to embed a function like  replace(text,"'","''") in the component ASP code.  I'm open to anything.  However, I need to be able seach with the Contains option.  Otherwize, the search will not be of much use to my customers.

Thanks.
0
 
LVL 1

Author Comment

by:ggs54
ID: 8219197
Your suggestion does work.  All three of my search fields could contain the single-quote and I need to be able to give my users the ability to put in part of the name, not necessarily the beginning, because some of these botanic names can be tricky.

For example, ACER PLATANOIDES 'ROYAL RED'
It's not likely that a user will know     ACER PLATANOIDES 'ROYAL , but they will know 'ROYAL .  I know that you could just say, omit the single-quote, but the single-quote is how they recognize the item and therefore will use it.

I am using the "~" as the default for Common Name because my data does not contain this character and, if they leave this field blank, I don't want it to find anything.

On the otherhand, I am using the "." for Size because my data does contain that character and I want ALL sizes to be found in the event that they leave the Size field blank.

As I mentioned, it would be really helpful to be able to embed a function like  replace(text,"'","''") in the component ASP code.  I'm open to anything.  However, I need to be able seach with the Contains option.  Otherwize, the search will not be of much use to my customers.

Thanks.
0
 
LVL 1

Author Comment

by:ggs54
ID: 8219321
Your suggestion does work.  All three of my search fields could contain the single-quote and I need to be able to give my users the ability to put in part of the name, not necessarily the beginning, because some of these botanic names can be tricky.

For example, ACER PLATANOIDES 'ROYAL RED'
It's not likely that a user will know     ACER PLATANOIDES 'ROYAL , but they will know 'ROYAL .  I know that you could just say, omit the single-quote, but the single-quote is how they recognize the item and therefore will use it.

I am using the "~" as the default for Common Name because my data does not contain this character and, if they leave this field blank, I don't want it to find anything.

On the otherhand, I am using the "." for Size because my data does contain that character and I want ALL sizes to be found in the event that they leave the Size field blank.

As I mentioned, it would be really helpful to be able to embed a function like  replace(text,"'","''") in the component ASP code.  I'm open to anything.  However, I need to be able seach with the Contains option.  Otherwize, the search will not be of much use to my customers.

Thanks.
0
 
LVL 14

Expert Comment

by:hhammash
ID: 8219379
Hi,

My last post will search Contain.

Regards
hhammash
0
 
LVL 1

Author Comment

by:ggs54
ID: 8219396
If I try to modify the fp_sQry line in the component ASP code, I get the following message when returning the the WYSIWYG tab or trying to save the page,

The contents of a FrontPage component have been modified.
These contents will be overwritten when you save this page.

When you go back into the code, the change has, in fact, been removed and the original line appears.
 
I did play with several combinations within the Edit Query section of the component.  It appears that the Replace function has to be defined.

This statement:

SELECT * FROM Available WHERE ((BotanicName LIKE Replace('%::BotanicName::%',"'","''") OR CommonName LIKE '%::CommonName::%') AND Size LIKE '%::Size::%') ORDER BY BotanicName ASC,Size ASC,CommonName ASC

produces this error when trying to verify the query:

[Microsoft][ODBC Microsoft Access Driver] Undefined function 'replace' in expression.

0
 
LVL 1

Author Comment

by:ggs54
ID: 8219406
If I try to modify the fp_sQry line in the component ASP code, I get the following message when returning the the WYSIWYG tab or trying to save the page,

The contents of a FrontPage component have been modified.
These contents will be overwritten when you save this page.

When you go back into the code, the change has, in fact, been removed and the original line appears.
 
I did play with several combinations within the Edit Query section of the component.  It appears that the Replace function has to be defined.

This statement:

SELECT * FROM Available WHERE ((BotanicName LIKE Replace('%::BotanicName::%',"'","''") OR CommonName LIKE '%::CommonName::%') AND Size LIKE '%::Size::%') ORDER BY BotanicName ASC,Size ASC,CommonName ASC

produces this error when trying to verify the query:

[Microsoft][ODBC Microsoft Access Driver] Undefined function 'replace' in expression.

0
 
LVL 1

Author Comment

by:ggs54
ID: 8219407
If I try to modify the fp_sQry line in the component ASP code, I get the following message when returning the the WYSIWYG tab or trying to save the page,

The contents of a FrontPage component have been modified.
These contents will be overwritten when you save this page.

When you go back into the code, the change has, in fact, been removed and the original line appears.
 
I did play with several combinations within the Edit Query section of the component.  It appears that the Replace function has to be defined.

This statement:

SELECT * FROM Available WHERE ((BotanicName LIKE Replace('%::BotanicName::%',"'","''") OR CommonName LIKE '%::CommonName::%') AND Size LIKE '%::Size::%') ORDER BY BotanicName ASC,Size ASC,CommonName ASC

produces this error when trying to verify the query:

[Microsoft][ODBC Microsoft Access Driver] Undefined function 'replace' in expression.

0
 
LVL 14

Accepted Solution

by:
hhammash earned 1000 total points
ID: 8219422
Ok ggs54,

You have to put your DRW code on Diet in order to be able to change the query.

Try this before:

Go to the first Grey Line in your DRW and change the SQL there then save.  If it did not work then you will have to  put your code on diet.

Here is how to put the code on Diet.

http://www.outfront.net/spooky/adv_drw.htm

Regards
hhammash
0
 
LVL 14

Expert Comment

by:hhammash
ID: 8223869
Hi,

You have to create the DRW,  then save it,  after saving it you have to put it on diet.  Do not put it on diet before saving.

Regards
hhammash
0
 
LVL 1

Author Comment

by:ggs54
ID: 8255488
hi, hhammash

I am still working on this problem.  I did put the DRW code on a diet.  That then gives me the ability to make changes to the ASP code.  The suggestions that you have given haven't worked and it still appears that the modification to the variable passed in the SQL statement isn't beging recognized.

For example,
fp_sQry="SELECT * FROM Available WHERE BotanicName LIKE '%" & Replace(Request.Form("BotanicName"),"'","''") & "%'"

The Replace function is not being recognized in the above statement.  However, if I place code outside of the ASP block of code that contains the SQL statement, the function works.

I don't have time to work on this at this point, but, I wanted to let you know that I haven't forgotten about it.
Once I find the answer, I'll let you know, or if you have any additional suggestions, I welcome them.

Thanks.
0
 
LVL 14

Expert Comment

by:hhammash
ID: 8257723
Hi ggs54,

It is working fine with me.  

1- Do not put the replace command before you put your DRW on diet.  Create the DRW as usual keep the SQL as it is.  Then save it.  After saving the Page which has the DRW put it on diet and put the code.  This is how my dieted page looks.  I copied the code from <--Include ...> to show this part which is important.


<!--#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 Names WHERE Name LIKE '%" & Replace(Request.Form("Name"),"'","''") & "%'"
fp_sDefault="Name="
fp_sNoRecords="<tr><td colspan=5 align=left width=""100%"">No records returned.</td></tr>"
fp_sDataConn="Customers"

Look at the fp_sQry here.  When I  put on the search form the word O'brien I am getting
O'Brien Melvin
Thomas O'Brien
M J O'Brien Jr.

See teh occurance of the word O'Brien,  it is at the beginning, middle and the end,  which means the IN and Contains work fine.

Note: The search form and the results page are on separate pages in my example.  I am posting the search form to the results page.

Regards
hhammash

0
 
LVL 14

Expert Comment

by:hhammash
ID: 8257731
Hi again,

I also tried it in one page and it is working fine.

hhammash
0
 
LVL 1

Author Comment

by:ggs54
ID: 8272427
Okay, hhammash.
To make a long story short, your suggestion does work for single quotes as long as no field is left blank.  Even though I set the defaults to non-blank values, the user can still clear the field and perform the search, which results in an error.  To get around this problem, I created the following code, which works exactly like it should.

<!--#include file="_fpclass/fpdblib.inc"-->
<%
Dim strBName
Dim strCName
Dim strSize

strBName=Replace(Request.Form("BotanicName"),"'","''")
strCName=Replace(Request.Form("CommonName"),"'","''")
strSize=Replace(Request.Form("Size"),"'","''")
If Trim(strBName)="" then strBName="~"
If Trim(strCName)="" then strCName="~"
If Trim(strSize)="" then strSize=" "

fp_sQry="SELECT * FROM Available WHERE ((BotanicName LIKE '%" & strBName & "%' OR CommonName LIKE '%" & strCName & "%') AND Size LIKE '%" & strSize & "%')"

The above statement works.

However, I have one more hurdle to clear.  My Size field contains the '#' character e.g. #1    If I enter #1, no results are returned even though their are many items with this size.  I am assuming that the # character is being interpreted in some way, other than a literal string.  I did some reading in my SQL manual and it describes the ESCAPE keyword for forcing the search to look at the character following the ESCAPE character as a literal.  Consequently, I modified my above SQL state as follows:  


fp_sQry="SELECT * FROM Available WHERE ((BotanicName LIKE '%" & strBName & "%' OR CommonName LIKE '%" & strCName & "%') AND Size LIKE '%" & strSize & "%' ESCAPE '\' )"

I have defined the "\" character as my ESCAPE character.  It is my understanding the the search string \#1 would be interpreted literally as  #1

Unfortunately, I get the following error:

Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '((BotanicName LIKE '%''FLAME''%' OR CommonName LIKE '%OAK%') AND Size LIKE '%\#1%' ESCAPE '\' )'.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers

As you can see, the Botanic and Common names are working perfectly.  It also appears that the Size should work, but doesn't and I don't know why.  The syntax looks right, but it obviously isn't.

Any other words of wisdom?

0
 
LVL 14

Expert Comment

by:hhammash
ID: 8272640
Hi,

Try putting :

fp_sQry="SELECT * FROM Available WHERE ((BotanicName LIKE '%" & strBName & "%' OR CommonName LIKE '%" & strCName & "%') AND Size LIKE '%" & strSize & "%' ESCAPE '/' )"

or

fp_sQry="SELECT * FROM Available WHERE ((BotanicName LIKE '%" & strBName & "%' OR CommonName LIKE '%" & strCName & "%') AND Size LIKE '%" & #strSize & "%' ESCAPE '#' )"

hhammash

0
 
LVL 14

Expert Comment

by:hhammash
ID: 8272654
Hi again ggs54,

About the single quotes,  sorry, I did not take into consideration the empty fields and also I presented the code for one field only.

It was clever adding the ~ if the field is empty.

Regards
hhammash
0
 
LVL 1

Author Comment

by:ggs54
ID: 8272822
Hi.

I've tried several characters  \/~  and they all produce the same error.  I don't think the problem is with the character, but rather the delimiting of the actual statement.  Consequently, the 'missing operator' error is generated.

Your second option I didn't try because the # character could be located in different positions e.g.  #1 or 5-6#

Thanks.
0
 
LVL 14

Expert Comment

by:hhammash
ID: 8316247
Hi,

I don't know why,  but it works fine with the actual field ::fieldName:: but it does not with the string replacement & stringName &.

In the actual field it does not even need an escape character,  it finds the ' # and everything.

Wonder why.

hhammash
0
 
LVL 14

Expert Comment

by:hhammash
ID: 8316281
Hi,

Try creating a DRW with the criteria Contains and have it normally without creating strings or replacing anything.

Just normal DRW with Contains in the criteria and make the search.

It will find
1#
#5
O'Brien
...etc.

Try it.

hhammash
0
 
LVL 1

Author Comment

by:ggs54
ID: 8327168
Hi, hhammash.

You are correct.  The normal SQL statement generated by DRW works for the # sign.  However, this field, which designates size, not only has the # sign, but also the single quote, as we discussed before.  Consequently, if I use the DRW code, I can successfully search for #, but the single-quote character will no longer work.  I have to be able to do both.

Thanks.
0
 
LVL 14

Expert Comment

by:hhammash
ID: 8344227
Hi ggs54,

Take this working solution:

Dim fName
fName = Request.Form("Name")
If InStr(fName, "#") Then
fName = Replace(fName,"#","#")
Else
fName = Replace(fName,"'","''")
End If

fp_sQry="SELECT * FROM Names WHERE Name LIKE '%" & fName & "%'"

Regards
hhammash

With special thanks to my friend Bud who supplied this solution.I had to make a little change to find #.  Bud is not a member in this site.

0
 
LVL 1

Author Comment

by:ggs54
ID: 8344405
Hi, hhammash.

I tried this solution with no luck.  Are you really replacing the pound sign with another pound sign?

I did try to make a conditional in my code.  However, regardless of my syntax, the query never seems to recognize the # character.

Thanks for all of the time that you are taking with this!

0
 
LVL 14

Expert Comment

by:hhammash
ID: 8344756
Hi ggs54,

What are you searching for? when I try the search and put # I get
#1
5#
45#

It is working with me.

regards
hhammash
0
 
LVL 14

Expert Comment

by:hhammash
ID: 8344826
Try this:

Dim fName
fName = Request.Form("Name")
If InStr(fName, "#")Then
fp_sQry="SELECT * FROM Names WHERE (Name LIKE '%::Name::%')"
Else
fName = Replace(fName,"'","''")
fp_sQry="SELECT * FROM Names WHERE Name LIKE '%" & fName & "%'"
End If

Regards
hhammash
0
 
LVL 1

Author Comment

by:ggs54
ID: 8345783
Here's my code:

strBotanicName = Request.Form("BotanicName)
strCommonName = Request.Form("CommonName")
strSize = Request.Form("Size")

strBotanicName = Replace(strBotanicName,"'","''")
strCommonName = Replace(strCommonName,"'","''")
strSize = Replace(strSize,"'","''")

If InStr(Size,"#") then
Response.Write "Pound Sign Found<BR>" 'THIS IS A TEST LINE
SELECT * FROM Available WHERE ((BotanicName LIKE '%" & strBotanicName & "%' OR CommonName LIKE '%" & strCommonName & "%') AND Size LIKE '%::Size::%')

Else

SELECT * FROM Available WHERE ((BotanicName LIKE '%" & strBotanicName & "%' OR CommonName LIKE '%" & strCommonName & "%') AND Size LIKE '%" & strSize & "%')

End If

All searches work as expected.  The Botanic and Common Name fields will not contain the # sign and the Replace function does the job.  I put the TEST LINE in just so that I could confirm that the # condition was being met and the first SELECT statement was executed.  It is.

If I enter oak in the common name and 5 in the size field, I get all oak trees that have a 5 in the size e.g.
2.25"
4-5'
#5

If I enter oak in the common name and #5 in the size field, no records are returned.  

If I enter oak in the common name and only the # sign in size, I get the first 100 records of oak, regardless of what is contained in the size field.  It's as if it is ignoring the # sign completely.

By the way, thanks for the tip on putting the DRW code on a diet.  It is much faster to use the wizard to generate the initial code and then modify it.  It also allows you to strip repetitive code, which makes the resulting page load faster.  Before the diet, a 100 record result was about 577k, now it less than 50k!

Thanks, again, for all of your help.
0
 
LVL 1

Author Comment

by:ggs54
ID: 8348683
Hi, hhammash.

I was able to find the solution.  I'm giving you an 'A' for all of the time that you have spent and all of the information that you provided.  I really appreciate it.

Here's the code that does the job and works for any combination of single-quote and # sign input:

<%
Dim strBName
Dim strCName
Dim strSize

'delimit special characters e.g. ' or #
strBName=Replace(Request.Form("BotanicName"),"'","''")
strCName=Replace(Request.Form("CommonName"),"'","''")
strSize=Replace(Request.Form("Size"),"'","''")
strSize=Replace(strSize,"#","[#]")

'null user input, assign default values
If Trim(strBName)="" then strBName="~"
If Trim(strCName)="" then strCName="~"
If Trim(strSize)="" then strSize=" "

'execute the search
fp_sQry="SELECT * FROM Available WHERE ((BotanicName LIKE '%" & strBName & "%' OR CommonName LIKE '%" & strCName & "%') AND Size LIKE '%" & strSize & "%')"

The Replace function handles the single-quote and the # sign and any other character that may create a problem.  As you can see, when a # sign is entered, it needs to be enclosed in brackets.  So # becomes [#]

I found this looking through by VB manual.  The # sign represents any single digit.  Therefore, it has to be delimited with the [], which forces the search to look for the literal # sign.

Thanks, again, for all of your help.

ggs54
0
 
LVL 14

Expert Comment

by:hhammash
ID: 8349568
Thank you ggs54,

I am glad that you found the solution.  But believe me,  I came quickly and opened the computer to write to you that you have to put the # between []. I showed the code to a colleagu and he said so. When I saw it in your code I knew that you got it right.

Thank you for your appreciation,  it was nice to exchange posts with you.

Regards
hhammash
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Now that Expression Web 4.0 (http://www.microsoft.com/expression/products/Upgrade.aspx) is free if you buy or have the full version of Expression Web 3.0, now is the best time to  migrate from FrontPage to Expression Web (http://www.frontpage-to-exp…
Turn A Profile Picture Into A Cartoon Using Photoshop And Illustrator This tutorial will teach you how to make a cartoon style image out of a regular picture. I have tried to keep the tutorial as simple as possible. I used Adobe CS4 for this tuto…
The purpose of this video is to demonstrate how to exclude a particular blog category from the main blog page. This is can be used when a category already has its own tab, or you simply want certain types of posts not to show up on the main blog. …
The purpose of this video is to demonstrate how to set up the permalinks on a WordPress Website. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php : Go t…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question