Front Page - Error in Frontpage when an ' is used in a search box

Author

Error in Frontpage when an ' is used in a search box

James Hubers

2004-03-30, 10:32 pm

I have a web site that uses a search box to find
information for the search page from 3 columns of a
database. (This search is generated in Front Page 2000
for me with me writing the select statement on my search
page.) This works find until you put an ' apostrophe in
the search box and hit enter. How can I write into the
SQL statement or what do I need to put on my page in ASP
that will add another ' to the string? How can I get
rid of the error below that I get on my ASP search
page. I have the select statement from my search below
also. Thank you in advance

SELECT * FROM Products
WHERE description like '%::T1::%'
OR type like '%::T1::%'
OR ItemNumber like '%::T1::%'
OR BrandName like '%::T1::%'
ORDER BY item ASC

Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver]
Syntax error in query expression 'description like '%'%'
OR type like '%'%' OR ItemNumber like '%'%' OR BrandName
like '%'%''.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers

James Hubers
info@webitsystems.com

Thomas A. Rowe

2004-03-30, 10:32 pm

Try something like the following:

T1A = Replace(Request.Form("T1"), "'", "''")

SELECT * FROM Products
WHERE description like '%::T1A::%'
OR type like '%::T1A::%'
OR ItemNumber like '%::T1A::%'
OR BrandName like '%::T1A::%'
ORDER BY item ASC

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)
http://www.ycoln-resources.com
FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================
To assist you in getting the best answers for FrontPage support see:
http://www.net-sites.com/sitebuilder/newsgroups.asp

"James Hubers" <info@webitsystems.com> wrote in message
news:1601f01c416bf$24bbd270$a401280a@phx.gbl...
> I have a web site that uses a search box to find
> information for the search page from 3 columns of a
> database. (This search is generated in Front Page 2000
> for me with me writing the select statement on my search
> page.) This works find until you put an ' apostrophe in
> the search box and hit enter. How can I write into the
> SQL statement or what do I need to put on my page in ASP
> that will add another ' to the string? How can I get
> rid of the error below that I get on my ASP search
> page. I have the select statement from my search below
> also. Thank you in advance
>
>
> SELECT * FROM Products
> WHERE description like '%::T1::%'
> OR type like '%::T1::%'
> OR ItemNumber like '%::T1::%'
> OR BrandName like '%::T1::%'
> ORDER BY item ASC
>
>
> Database Results Error
> Description: [Microsoft][ODBC Microsoft Access Driver]
> Syntax error in query expression 'description like '%'%'
> OR type like '%'%' OR ItemNumber like '%'%' OR BrandName
> like '%'%''.
> Number: -2147217900 (0x80040E14)
> Source: Microsoft OLE DB Provider for ODBC Drivers
>
>
> James Hubers
> info@webitsystems.com
>

2004-03-31, 1:29 am

Thanks

I tried this but the problem now is I lost my search
capabilities. Now everything in the database is
displayed at the same time. I no longer can search just
for one brand or item. It did take care of the error
message.

I guess I do not know ASP well enough to figure out what
to do. The search worked great with the SQL as it was
unless you put an apostrophe in the search string.

I am open for any suggestions

>-----Original Message-----
>Try something like the following:
>
>T1A = Replace(Request.Form("T1"), "'", "''")
>
>
>SELECT * FROM Products
>WHERE description like '%::T1A::%'
>OR type like '%::T1A::%'
>OR ItemNumber like '%::T1A::%'
>OR BrandName like '%::T1A::%'
>ORDER BY item ASC
>
>--
>==============================================
>Thomas A. Rowe (Microsoft MVP - FrontPage)
>WEBMASTER Resources(tm)
>http://www.ycoln-resources.com
>FrontPage Resources, WebCircle, MS KB Quick Links, etc.
>==============================================
>To assist you in getting the best answers for FrontPage
support see:
>http://www.net-sites.com/sitebuilder/newsgroups.asp
>
>"James Hubers" <info@webitsystems.com> wrote in message
>news:1601f01c416bf$24bbd270$a401280a@phx.gbl...
search[color=darkred]
in[color=darkred]
the[color=darkred]
ASP[color=darkred]
below[color=darkred]
like '%'%'[color=darkred]
BrandName[color=darkred]
>
>
>.
>

Thomas A. Rowe

2004-03-31, 1:37 pm

Sorry,

I do not use the FP DRW. Here is how I actually write the query, you will need to follow the
concept/adjust to work with the FP DRW:

Dim Find
Find = Cstr(Replace(Request.Form("Search"),"'", "''"))

Dim DSN_Name
DSN_Name = Application("documents_ConnectionString")

set Conn = Server.CreateObject("ADODB.Connection")
set objRS = server.CreateObject("ADODB.Recordset")
Conn.Open DSN_Name

'Create the recordset object
Set objRS = Server.CreateObject("ADODB.Recordset")
sql = "SELECT * FROM Documents WHERE (Active = TRUE) and ((VolIssue LIKE '%" & Find & "%') OR (Title
LIKE '%" & Find & "%') OR (Author LIKE '%" & Find & "%') OR (Summary LIKE '%" & Find & "%') OR
(Keyword LIKE '%" & Find & "%')) ORDER BY Title, ReleaseDate"
objRS.Open SQL, DSN_Name

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WEBMASTER Resources(tm)
http://www.ycoln-resources.com
FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================
To assist you in getting the best answers for FrontPage support see:
http://www.net-sites.com/sitebuilder/newsgroups.asp

<anonymous@discussions.microsoft.com> wrote in message
news:1180301c416d9$c5e107f0$a001280a@phx.gbl...[color=darkred]
> Thanks
>
>
> I tried this but the problem now is I lost my search
> capabilities. Now everything in the database is
> displayed at the same time. I no longer can search just
> for one brand or item. It did take care of the error
> message.
>
> I guess I do not know ASP well enough to figure out what
> to do. The search worked great with the SQL as it was
> unless you put an apostrophe in the search string.
>
> I am open for any suggestions
>
>
> support see:
> search
> in
> the
> ASP
> below
> like '%'%'
> BrandName