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

  • Thread starter Thread starter James Hubers
  • Start date Start date
J

James Hubers

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
(e-mail address removed)
 
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)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================
 
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)

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 said:
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
(e-mail address removed)
Click to expand...


.
Click to expand...
 
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)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================


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)

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 said:
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
(e-mail address removed)
Click to expand...


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Zip Code Search 7
FrontPage-Data Results 1
Database results Error (with criteria) 1
Syntax error in UPDATE statement. 5
Dabase Results Page 14
UPDATE SQL 5
Database wizard problems 2
SQL Select search on integer and text columns 1

Back
Top