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

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

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
ggs54
Asked:
ggs54
  • 19
  • 14
1 Solution
 
hhammashCommented:
Hi,

First your SQL has two Brackets open and one closed.

I'll see about the other problem.

hhammash
0
 
ggs54Author Commented:
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
 
hhammashCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

 
hhammashCommented:
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
 
hhammashCommented:
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
 
hhammashCommented:
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
 
hhammashCommented:
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
 
ggs54Author Commented:
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
 
ggs54Author Commented:
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
 
ggs54Author Commented:
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
 
hhammashCommented:
Hi,

My last post will search Contain.

Regards
hhammash
0
 
ggs54Author Commented:
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
 
ggs54Author Commented:
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
 
ggs54Author Commented:
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
 
hhammashCommented:
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
 
hhammashCommented:
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
 
ggs54Author Commented:
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
 
hhammashCommented:
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
 
hhammashCommented:
Hi again,

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

hhammash
0
 
ggs54Author Commented:
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
 
hhammashCommented:
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
 
hhammashCommented:
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
 
ggs54Author Commented:
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
 
hhammashCommented:
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
 
hhammashCommented:
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
 
ggs54Author Commented:
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
 
hhammashCommented:
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
 
ggs54Author Commented:
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
 
hhammashCommented:
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
 
hhammashCommented:
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
 
ggs54Author Commented:
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
 
ggs54Author Commented:
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
 
hhammashCommented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 19
  • 14
Tackle projects and never again get stuck behind a technical roadblock.
Join Now