«

I'm certainly no expert but IMHO.......

Suggestion #1. Use a regular expression to do the replacing for you

This makes for much cleaner, and easier to read code. If you'd rather not
use a RegExp for whatever reason, stick to a max of 2 "Replace's" per
line....

Suggestion #2. Use a function that contains suggestion #1

By using a function to do the replacing, it allows you to simply use;

Response.Write ReplaceThis(TheString)

.... instead of ...

Response.Write Replace(Replace(Replace.... etc etc etc

Hint:

Function ReplaceThis(sWhat)
'// RegExp or whatever, code goes here.....
End Function

Suggestion #3. NEVER use wysywig app's such as DW, FP etc etc, to write the
code for you....

As has been mentioned by Bob, they screw up virtually everything.... tis
much easier (and quicker) to learn how to hand code it yourself (hint:
aspfaq.com ;o)) using Notepad or some other text editor (just make sure you
stay away from editors such as MS Word etc, if going this route as they're a
royal pain in the backside....).

--

Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!


"shank" <> wrote in message
news:...
> It was and it wasn't related to the other post. There were 2 issues and if
I
> could have gotten through either, I probably could have gotten through
both.
> The reason I don't post everything is it looks like a huge mess of code
> coming through the newsgroup. And I just figured it would make things more
> confusing. Here is the entire recordset created in dreamweaver. It works
> fine as it is here. I know you code heads may not like dreamweaver but
it's
> a huge crutch for those of us that don't have the knowledge to hand code
> things. It works very well for what I want to do, up until I want to give
an
> extra effort to make things better. For this project I have to use Access
> 2002. I don't like it, but that's not my choice. I have a music database
> that users can search. I get titles and artists from many many sources.
They
> all choose to spell titles and artists their own way. That's a problem. In
> an effort to make it easier on the end users, I want to remove common
words
> and punctuation that varies. That includes "'", """, ",", "/", " ", "&",
> "the", and "and". If I remove those characters from both terms, it creates
a
> much better chance of relevant search results. I've done this with SQL
> stored procedures and it works great. Now I have to do this with SQL in
ASP
> and I'm having a lot of syntax problems. I appreciate everyone's help!!! I
> thought I was trying to make it easier on everyone and I guess I made it
> more confusing. Sorry!
>
> I only have to remove those characters from titles and artists fields. The
> other fields I have control over the data. OK... now how do I accomplish
> this feat?
> thanks!
>
> <%
> Dim rsResults__T
> rsResults__T = "%"
> If (Request("title") <> "") Then
> rsResults__T = Request("title")
> End If
> %>
> <%
> Dim rsResults__A
> rsResults__A = "%"
> If (Request("artist") <> "") Then
> rsResults__A = Request("artist")
> End If
> %>
> <%
> Dim rsResults__C
> rsResults__C = "%"
> If (Request("category") <> "") Then
> rsResults__C = Request("category")
> End If
> %>
> <%
> Dim rsResults__TY
> rsResults__TY = "%"
> If (Request("type") <> "") Then
> rsResults__TY = Request("type")
> End If
> %>
> <%
> Dim rsResults__M
> rsResults__M = "%"
> If (Request("manuf") <> "") Then
> rsResults__M = Request("manuf")
> End If
> %>
> <%
> Dim rsResults__SA
> rsResults__SA = "%"
> If (Request("singleartist") <> "") Then
> rsResults__SA = Request("singleartist")
> End If
> %>
> <%
> Dim rsResults
> Dim rsResults_numRows
>
> Set rsResults = Server.CreateObject("ADODB.Recordset")
> rsResults.ActiveConnection = MM_GenKAccess_STRING
> rsResults.Source = "SELECT GenKStock.OrderNo, GenKStock.SingleArtist,
> GenKStock.SoftHard, GenKStock.Category, GenKStock.Type, GenKStock.Label,
> GenKStock.Description, GenKStock.Manuf, GenKTitles.ItemNumber,
> GenKTitles.Title, GenKTitles.Artist, GenKTitles.mp3Files FROM GenKStock
> INNER JOIN GenKTitles ON GenKStock.OrderNo = GenKTitles.ItemNumber WHERE
> (GenKTitles.Title LIKE '%' + '" + Replace(rsResults__T, "'", "''") + "' +
> '%') AND (GenKTitles.Artist LIKE '%' + '" + Replace(rsResults__A, "'",
> "''") + "' + '%') AND (GenKStock.Category LIKE '" +
> Replace(rsResults__C, "'", "''") + "') AND (GenKStock.Manuf LIKE '" +
> Replace(rsResults__M, "'", "''") + "') AND (GenKStock.Type LIKE '" +
> Replace(rsResults__TY, "'", "''") + "') AND (GenKStock.SingleArtist LIKE
> '" + Replace(rsResults__SA, "'", "''") + "')"
> rsResults.CursorType = 0
> rsResults.CursorLocation = 2
> rsResults.LockType = 1
> rsResults.Open()
>
> rsResults_numRows = 0
> %>
>
> "Chris Hohmann" <> wrote in message
> news:%...
> > "shank" <> wrote in message
> > news:...
> > > 1) I'm getting this error: Syntax error (missing operator) in query
> > > expression on the below statement. Can I get some advice.
> > >
> > > 2) I searched ASPFAQ and came up blank. Where can find the "rules" for
> > when
> > > and how to use single quotes and double quotes in ASP?
> > >
> > > thanks!
> > > ----------------------
> > > SQL = SQL & "WHERE '" &
> > >
> >
>
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RE PLACE(REPLACE("GenKTitles.
> > > Title","'",""),",",""),".",""),"
> ",""),"the",""),"and",""),"&",""),"/","")
> > &
> > > "' LIKE '%' '" & T & "' '%' "
> > >
> >
> > This appears to be related to your other "syntax error" post. If so, it
> may
> > have been better to post a follow-up to that thread. Given that context,
> it
> > appears that you're attempting to eliminate false positives from your
> > searches by discluding common words and punctuations. If this is the
case,
> > perhaps it would be easier to remove those items from the search term
> > instead of attempting to remove them from the GenKTitles.Title column in
> > your database. If you took this approach you could make use of a regular
> > expression to "scrub" the search term prior to constructing the SQL
> > statement. Also, please consider using a stored procedure (or
> parameterized
> > query depending on your database environment) instead of dynamically
> > constructing the SQL statement. Also, in the future, please provide
> > database, version, DDL, sample data and desired output/results. Here's
are
> > some guidelines
> >
> > http://aspfaq.com/5000
> >
> >
>
>

Thanks to all, but if I can't get past a few syntax issues, I'll never get
past creating a function.

"Steven Burn" <> wrote in message
news:...
> I'm certainly no expert but IMHO.......
>
> Suggestion #1. Use a regular expression to do the replacing for you
>
> This makes for much cleaner, and easier to read code. If you'd rather not
> use a RegExp for whatever reason, stick to a max of 2 "Replace's" per
> line....
>
> Suggestion #2. Use a function that contains suggestion #1
>
> By using a function to do the replacing, it allows you to simply use;
>
> Response.Write ReplaceThis(TheString)
>
> ... instead of ...
>
> Response.Write Replace(Replace(Replace.... etc etc etc
>
> Hint:
>
> Function ReplaceThis(sWhat)
> '// RegExp or whatever, code goes here.....
> End Function
>
> Suggestion #3. NEVER use wysywig app's such as DW, FP etc etc, to write
the
> code for you....
>
> As has been mentioned by Bob, they screw up virtually everything.... tis
> much easier (and quicker) to learn how to hand code it yourself (hint:
> aspfaq.com ;o)) using Notepad or some other text editor (just make sure
you
> stay away from editors such as MS Word etc, if going this route as they're
a
> royal pain in the backside....).
>
> --
>
> Regards
>
> Steven Burn
> Ur I.T. Mate Group
> www.it-mate.co.uk
>
> Keeping it FREE!
>
>
> "shank" <> wrote in message
> news:...
> > It was and it wasn't related to the other post. There were 2 issues and
if
> I
> > could have gotten through either, I probably could have gotten through
> both.
> > The reason I don't post everything is it looks like a huge mess of code
> > coming through the newsgroup. And I just figured it would make things
more
> > confusing. Here is the entire recordset created in dreamweaver. It works
> > fine as it is here. I know you code heads may not like dreamweaver but
> it's
> > a huge crutch for those of us that don't have the knowledge to hand code
> > things. It works very well for what I want to do, up until I want to
give
> an
> > extra effort to make things better. For this project I have to use
Access
> > 2002. I don't like it, but that's not my choice. I have a music database
> > that users can search. I get titles and artists from many many sources.
> They
> > all choose to spell titles and artists their own way. That's a problem.
In
> > an effort to make it easier on the end users, I want to remove common
> words
> > and punctuation that varies. That includes "'", """, ",", "/", " ", "&",
> > "the", and "and". If I remove those characters from both terms, it
creates
> a
> > much better chance of relevant search results. I've done this with SQL
> > stored procedures and it works great. Now I have to do this with SQL in
> ASP
> > and I'm having a lot of syntax problems. I appreciate everyone's help!!!
I
> > thought I was trying to make it easier on everyone and I guess I made it
> > more confusing. Sorry!
> >
> > I only have to remove those characters from titles and artists fields.
The
> > other fields I have control over the data. OK... now how do I accomplish
> > this feat?
> > thanks!
> >
> > <%
> > Dim rsResults__T
> > rsResults__T = "%"
> > If (Request("title") <> "") Then
> > rsResults__T = Request("title")
> > End If
> > %>
> > <%
> > Dim rsResults__A
> > rsResults__A = "%"
> > If (Request("artist") <> "") Then
> > rsResults__A = Request("artist")
> > End If
> > %>
> > <%
> > Dim rsResults__C
> > rsResults__C = "%"
> > If (Request("category") <> "") Then
> > rsResults__C = Request("category")
> > End If
> > %>
> > <%
> > Dim rsResults__TY
> > rsResults__TY = "%"
> > If (Request("type") <> "") Then
> > rsResults__TY = Request("type")
> > End If
> > %>
> > <%
> > Dim rsResults__M
> > rsResults__M = "%"
> > If (Request("manuf") <> "") Then
> > rsResults__M = Request("manuf")
> > End If
> > %>
> > <%
> > Dim rsResults__SA
> > rsResults__SA = "%"
> > If (Request("singleartist") <> "") Then
> > rsResults__SA = Request("singleartist")
> > End If
> > %>
> > <%
> > Dim rsResults
> > Dim rsResults_numRows
> >
> > Set rsResults = Server.CreateObject("ADODB.Recordset")
> > rsResults.ActiveConnection = MM_GenKAccess_STRING
> > rsResults.Source = "SELECT GenKStock.OrderNo, GenKStock.SingleArtist,
> > GenKStock.SoftHard, GenKStock.Category, GenKStock.Type, GenKStock.Label,
> > GenKStock.Description, GenKStock.Manuf, GenKTitles.ItemNumber,
> > GenKTitles.Title, GenKTitles.Artist, GenKTitles.mp3Files FROM GenKStock
> > INNER JOIN GenKTitles ON GenKStock.OrderNo = GenKTitles.ItemNumber
WHERE
> > (GenKTitles.Title LIKE '%' + '" + Replace(rsResults__T, "'", "''") + "'
+
> > '%') AND (GenKTitles.Artist LIKE '%' + '" + Replace(rsResults__A, "'",
> > "''") + "' + '%') AND (GenKStock.Category LIKE '" +
> > Replace(rsResults__C, "'", "''") + "') AND (GenKStock.Manuf LIKE '" +
> > Replace(rsResults__M, "'", "''") + "') AND (GenKStock.Type LIKE '" +
> > Replace(rsResults__TY, "'", "''") + "') AND (GenKStock.SingleArtist
LIKE
> > '" + Replace(rsResults__SA, "'", "''") + "')"
> > rsResults.CursorType = 0
> > rsResults.CursorLocation = 2
> > rsResults.LockType = 1
> > rsResults.Open()
> >
> > rsResults_numRows = 0
> > %>
> >
> > "Chris Hohmann" <> wrote in message
> > news:%...
> > > "shank" <> wrote in message
> > > news:...
> > > > 1) I'm getting this error: Syntax error (missing operator) in query
> > > > expression on the below statement. Can I get some advice.
> > > >
> > > > 2) I searched ASPFAQ and came up blank. Where can find the "rules"
for
> > > when
> > > > and how to use single quotes and double quotes in ASP?
> > > >
> > > > thanks!
> > > > ----------------------
> > > > SQL = SQL & "WHERE '" &
> > > >
> > >
> >
>
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RE PLACE(REPLACE("GenKTitles.
> > > > Title","'",""),",",""),".",""),"
> > ",""),"the",""),"and",""),"&",""),"/","")
> > > &
> > > > "' LIKE '%' '" & T & "' '%' "
> > > >
> > >
> > > This appears to be related to your other "syntax error" post. If so,
it
> > may
> > > have been better to post a follow-up to that thread. Given that
context,
> > it
> > > appears that you're attempting to eliminate false positives from your
> > > searches by discluding common words and punctuations. If this is the
> case,
> > > perhaps it would be easier to remove those items from the search term
> > > instead of attempting to remove them from the GenKTitles.Title column
in
> > > your database. If you took this approach you could make use of a
regular
> > > expression to "scrub" the search term prior to constructing the SQL
> > > statement. Also, please consider using a stored procedure (or
> > parameterized
> > > query depending on your database environment) instead of dynamically
> > > constructing the SQL statement. Also, in the future, please provide
> > > database, version, DDL, sample data and desired output/results. Here's
> are
> > > some guidelines
> > >
> > > http://aspfaq.com/5000
> > >
> > >
> >
> >
>
>

Writing a function is fairly simple......

Function ReplaceThis(sWhat)
'// Though not necessary, I tend to lcase
'// the string before doing anything with it
'// as it makes it easier to work with when
'// you want to replace the text, regardless
'// of whether it has upper or lowercase text

ReplaceThis = Replace(lcase(sWhat), "the dog", "some text")
End Function

Then in your page, you'd simply use;

Response.Write ReplaceThis("The dog went over the hill")

--

Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!


"shank" <> wrote in message
news:...
> Thanks to all, but if I can't get past a few syntax issues, I'll never get
> past creating a function.
>
> "Steven Burn" <> wrote in message
> news:...
> > I'm certainly no expert but IMHO.......
> >
> > Suggestion #1. Use a regular expression to do the replacing for you
> >
> > This makes for much cleaner, and easier to read code. If you'd rather
not
> > use a RegExp for whatever reason, stick to a max of 2 "Replace's" per
> > line....
> >
> > Suggestion #2. Use a function that contains suggestion #1
> >
> > By using a function to do the replacing, it allows you to simply use;
> >
> > Response.Write ReplaceThis(TheString)
> >
> > ... instead of ...
> >
> > Response.Write Replace(Replace(Replace.... etc etc etc
> >
> > Hint:
> >
> > Function ReplaceThis(sWhat)
> > '// RegExp or whatever, code goes here.....
> > End Function
> >
> > Suggestion #3. NEVER use wysywig app's such as DW, FP etc etc, to write
> the
> > code for you....
> >
> > As has been mentioned by Bob, they screw up virtually everything.... tis
> > much easier (and quicker) to learn how to hand code it yourself (hint:
> > aspfaq.com ;o)) using Notepad or some other text editor (just make sure
> you
> > stay away from editors such as MS Word etc, if going this route as
they're
> a
> > royal pain in the backside....).
> >
> > --
> >
> > Regards
> >
> > Steven Burn
> > Ur I.T. Mate Group
> > www.it-mate.co.uk
> >
> > Keeping it FREE!
> >
> >
> > "shank" <> wrote in message
> > news:...
> > > It was and it wasn't related to the other post. There were 2 issues
and
> if
> > I
> > > could have gotten through either, I probably could have gotten through
> > both.
> > > The reason I don't post everything is it looks like a huge mess of
code
> > > coming through the newsgroup. And I just figured it would make things
> more
> > > confusing. Here is the entire recordset created in dreamweaver. It
works
> > > fine as it is here. I know you code heads may not like dreamweaver but
> > it's
> > > a huge crutch for those of us that don't have the knowledge to hand
code
> > > things. It works very well for what I want to do, up until I want to
> give
> > an
> > > extra effort to make things better. For this project I have to use
> Access
> > > 2002. I don't like it, but that's not my choice. I have a music
database
> > > that users can search. I get titles and artists from many many
sources.
> > They
> > > all choose to spell titles and artists their own way. That's a
problem.
> In
> > > an effort to make it easier on the end users, I want to remove common
> > words
> > > and punctuation that varies. That includes "'", """, ",", "/", " ",
"&",
> > > "the", and "and". If I remove those characters from both terms, it
> creates
> > a
> > > much better chance of relevant search results. I've done this with SQL
> > > stored procedures and it works great. Now I have to do this with SQL
in
> > ASP
> > > and I'm having a lot of syntax problems. I appreciate everyone's
help!!!
> I
> > > thought I was trying to make it easier on everyone and I guess I made
it
> > > more confusing. Sorry!
> > >
> > > I only have to remove those characters from titles and artists fields.
> The
> > > other fields I have control over the data. OK... now how do I
accomplish
> > > this feat?
> > > thanks!
> > >
> > > <%
> > > Dim rsResults__T
> > > rsResults__T = "%"
> > > If (Request("title") <> "") Then
> > > rsResults__T = Request("title")
> > > End If
> > > %>
> > > <%
> > > Dim rsResults__A
> > > rsResults__A = "%"
> > > If (Request("artist") <> "") Then
> > > rsResults__A = Request("artist")
> > > End If
> > > %>
> > > <%
> > > Dim rsResults__C
> > > rsResults__C = "%"
> > > If (Request("category") <> "") Then
> > > rsResults__C = Request("category")
> > > End If
> > > %>
> > > <%
> > > Dim rsResults__TY
> > > rsResults__TY = "%"
> > > If (Request("type") <> "") Then
> > > rsResults__TY = Request("type")
> > > End If
> > > %>
> > > <%
> > > Dim rsResults__M
> > > rsResults__M = "%"
> > > If (Request("manuf") <> "") Then
> > > rsResults__M = Request("manuf")
> > > End If
> > > %>
> > > <%
> > > Dim rsResults__SA
> > > rsResults__SA = "%"
> > > If (Request("singleartist") <> "") Then
> > > rsResults__SA = Request("singleartist")
> > > End If
> > > %>
> > > <%
> > > Dim rsResults
> > > Dim rsResults_numRows
> > >
> > > Set rsResults = Server.CreateObject("ADODB.Recordset")
> > > rsResults.ActiveConnection = MM_GenKAccess_STRING
> > > rsResults.Source = "SELECT GenKStock.OrderNo, GenKStock.SingleArtist,
> > > GenKStock.SoftHard, GenKStock.Category, GenKStock.Type,
GenKStock.Label,
> > > GenKStock.Description, GenKStock.Manuf, GenKTitles.ItemNumber,
> > > GenKTitles.Title, GenKTitles.Artist, GenKTitles.mp3Files FROM
GenKStock
> > > INNER JOIN GenKTitles ON GenKStock.OrderNo = GenKTitles.ItemNumber
> WHERE
> > > (GenKTitles.Title LIKE '%' + '" + Replace(rsResults__T, "'", "''") +
"'
> +
> > > '%') AND (GenKTitles.Artist LIKE '%' + '" + Replace(rsResults__A,
"'",
> > > "''") + "' + '%') AND (GenKStock.Category LIKE '" +
> > > Replace(rsResults__C, "'", "''") + "') AND (GenKStock.Manuf LIKE '"
+
> > > Replace(rsResults__M, "'", "''") + "') AND (GenKStock.Type LIKE '" +
> > > Replace(rsResults__TY, "'", "''") + "') AND (GenKStock.SingleArtist
> LIKE
> > > '" + Replace(rsResults__SA, "'", "''") + "')"
> > > rsResults.CursorType = 0
> > > rsResults.CursorLocation = 2
> > > rsResults.LockType = 1
> > > rsResults.Open()
> > >
> > > rsResults_numRows = 0
> > > %>
> > >
> > > "Chris Hohmann" <> wrote in message
> > > news:%...
> > > > "shank" <> wrote in message
> > > > news:...
> > > > > 1) I'm getting this error: Syntax error (missing operator) in
query
> > > > > expression on the below statement. Can I get some advice.
> > > > >
> > > > > 2) I searched ASPFAQ and came up blank. Where can find the "rules"
> for
> > > > when
> > > > > and how to use single quotes and double quotes in ASP?
> > > > >
> > > > > thanks!
> > > > > ----------------------
> > > > > SQL = SQL & "WHERE '" &
> > > > >
> > > >
> > >
> >
>
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RE PLACE(REPLACE("GenKTitles.
> > > > > Title","'",""),",",""),".",""),"
> > > ",""),"the",""),"and",""),"&",""),"/","")
> > > > &
> > > > > "' LIKE '%' '" & T & "' '%' "
> > > > >
> > > >
> > > > This appears to be related to your other "syntax error" post. If so,
> it
> > > may
> > > > have been better to post a follow-up to that thread. Given that
> context,
> > > it
> > > > appears that you're attempting to eliminate false positives from
your
> > > > searches by discluding common words and punctuations. If this is the
> > case,
> > > > perhaps it would be easier to remove those items from the search
term
> > > > instead of attempting to remove them from the GenKTitles.Title
column
> in
> > > > your database. If you took this approach you could make use of a
> regular
> > > > expression to "scrub" the search term prior to constructing the SQL
> > > > statement. Also, please consider using a stored procedure (or
> > > parameterized
> > > > query depending on your database environment) instead of dynamically
> > > > constructing the SQL statement. Also, in the future, please provide
> > > > database, version, DDL, sample data and desired output/results.
Here's
> > are
> > > > some guidelines
> > > >
> > > > http://aspfaq.com/5000
> > > >
> > > >
> > >
> > >
> >
> >
>
>

I really like the idea of the function, but having problems with syntax
again. I'm starting simple by removing the apostrophes.

<%
Function ReplaceThis(sWhat)
ReplaceThis = Replace(UCase(sWhat), "'", "")
End Function
%>

I'm just including the portion o fthe WHERE clause where I'm experimenting.

WHERE ( " & ReplaceThis("GenKTitles.Title") & " LIKE '%' + '" +
ReplaceThis(rsResults__T) + "' + '%')

The SQL to my page is....
SQL = SELECT GenKStock.OrderNo, GenKStock.SingleArtist, GenKStock.SoftHard,
GenKStock.Category, GenKStock.Type, GenKStock.Label, GenKStock.Description,
GenKStock.Manuf, GenKTitles.ItemNumber, GenKTitles.Title, GenKTitles.Artist,
GenKTitles.mp3Files FROM GenKStock INNER JOIN GenKTitles ON
GenKStock.OrderNo = GenKTitles.ItemNumber WHERE ( GENKTITLES.TITLE LIKE '%'
+ 'AINT' + '%') AND (GenKTitles.Artist LIKE '%' + '%' + '%') AND
(GenKStock.Category LIKE '%') AND (GenKStock.Manuf LIKE '%') AND
(GenKStock.Type LIKE '%') AND (GenKStock.SingleArtist LIKE '%') ORDER BY
OrderNo ASC

The function ReplaceThis is working fine on this: ReplaceThis(rsResults__T)
But not on this: ReplaceThis("GenKTitles.Title")
I'm getting results that match *aint* but not *ain't*
What am I missing?
thanks for the function!!!

"Steven Burn" <> wrote in message
news:...
> Writing a function is fairly simple......
>
> Function ReplaceThis(sWhat)
> '// Though not necessary, I tend to lcase
> '// the string before doing anything with it
> '// as it makes it easier to work with when
> '// you want to replace the text, regardless
> '// of whether it has upper or lowercase text
>
> ReplaceThis = Replace(lcase(sWhat), "the dog", "some text")
> End Function
>
> Then in your page, you'd simply use;
>
> Response.Write ReplaceThis("The dog went over the hill")
>
> --
>
> Regards
>
> Steven Burn
> Ur I.T. Mate Group
> www.it-mate.co.uk
>
> Keeping it FREE!
>
>
> "shank" <> wrote in message
> news:...
> > Thanks to all, but if I can't get past a few syntax issues, I'll never
get
> > past creating a function.
> >
> > "Steven Burn" <> wrote in message
> > news:...
> > > I'm certainly no expert but IMHO.......
> > >
> > > Suggestion #1. Use a regular expression to do the replacing for you
> > >
> > > This makes for much cleaner, and easier to read code. If you'd rather
> not
> > > use a RegExp for whatever reason, stick to a max of 2 "Replace's" per
> > > line....
> > >
> > > Suggestion #2. Use a function that contains suggestion #1
> > >
> > > By using a function to do the replacing, it allows you to simply use;
> > >
> > > Response.Write ReplaceThis(TheString)
> > >
> > > ... instead of ...
> > >
> > > Response.Write Replace(Replace(Replace.... etc etc etc
> > >
> > > Hint:
> > >
> > > Function ReplaceThis(sWhat)
> > > '// RegExp or whatever, code goes here.....
> > > End Function
> > >
> > > Suggestion #3. NEVER use wysywig app's such as DW, FP etc etc, to
write
> > the
> > > code for you....
> > >
> > > As has been mentioned by Bob, they screw up virtually everything....
tis
> > > much easier (and quicker) to learn how to hand code it yourself (hint:
> > > aspfaq.com ;o)) using Notepad or some other text editor (just make
sure
> > you
> > > stay away from editors such as MS Word etc, if going this route as
> they're
> > a
> > > royal pain in the backside....).
> > >
> > > --
> > >
> > > Regards
> > >
> > > Steven Burn
> > > Ur I.T. Mate Group
> > > www.it-mate.co.uk
> > >
> > > Keeping it FREE!
> > >
> > >
> > > "shank" <> wrote in message
> > > news:...
> > > > It was and it wasn't related to the other post. There were 2 issues
> and
> > if
> > > I
> > > > could have gotten through either, I probably could have gotten
through
> > > both.
> > > > The reason I don't post everything is it looks like a huge mess of
> code
> > > > coming through the newsgroup. And I just figured it would make
things
> > more
> > > > confusing. Here is the entire recordset created in dreamweaver. It
> works
> > > > fine as it is here. I know you code heads may not like dreamweaver
but
> > > it's
> > > > a huge crutch for those of us that don't have the knowledge to hand
> code
> > > > things. It works very well for what I want to do, up until I want to
> > give
> > > an
> > > > extra effort to make things better. For this project I have to use
> > Access
> > > > 2002. I don't like it, but that's not my choice. I have a music
> database
> > > > that users can search. I get titles and artists from many many
> sources.
> > > They
> > > > all choose to spell titles and artists their own way. That's a
> problem.
> > In
> > > > an effort to make it easier on the end users, I want to remove
common
> > > words
> > > > and punctuation that varies. That includes "'", """, ",", "/", " ",
> "&",
> > > > "the", and "and". If I remove those characters from both terms, it
> > creates
> > > a
> > > > much better chance of relevant search results. I've done this with
SQL
> > > > stored procedures and it works great. Now I have to do this with SQL
> in
> > > ASP
> > > > and I'm having a lot of syntax problems. I appreciate everyone's
> help!!!
> > I
> > > > thought I was trying to make it easier on everyone and I guess I
made
> it
> > > > more confusing. Sorry!
> > > >
> > > > I only have to remove those characters from titles and artists
fields.
> > The
> > > > other fields I have control over the data. OK... now how do I
> accomplish
> > > > this feat?
> > > > thanks!
> > > >
> > > > <%
> > > > Dim rsResults__T
> > > > rsResults__T = "%"
> > > > If (Request("title") <> "") Then
> > > > rsResults__T = Request("title")
> > > > End If
> > > > %>
> > > > <%
> > > > Dim rsResults__A
> > > > rsResults__A = "%"
> > > > If (Request("artist") <> "") Then
> > > > rsResults__A = Request("artist")
> > > > End If
> > > > %>
> > > > <%
> > > > Dim rsResults__C
> > > > rsResults__C = "%"
> > > > If (Request("category") <> "") Then
> > > > rsResults__C = Request("category")
> > > > End If
> > > > %>
> > > > <%
> > > > Dim rsResults__TY
> > > > rsResults__TY = "%"
> > > > If (Request("type") <> "") Then
> > > > rsResults__TY = Request("type")
> > > > End If
> > > > %>
> > > > <%
> > > > Dim rsResults__M
> > > > rsResults__M = "%"
> > > > If (Request("manuf") <> "") Then
> > > > rsResults__M = Request("manuf")
> > > > End If
> > > > %>
> > > > <%
> > > > Dim rsResults__SA
> > > > rsResults__SA = "%"
> > > > If (Request("singleartist") <> "") Then
> > > > rsResults__SA = Request("singleartist")
> > > > End If
> > > > %>
> > > > <%
> > > > Dim rsResults
> > > > Dim rsResults_numRows
> > > >
> > > > Set rsResults = Server.CreateObject("ADODB.Recordset")
> > > > rsResults.ActiveConnection = MM_GenKAccess_STRING
> > > > rsResults.Source = "SELECT GenKStock.OrderNo,
GenKStock.SingleArtist,
> > > > GenKStock.SoftHard, GenKStock.Category, GenKStock.Type,
> GenKStock.Label,
> > > > GenKStock.Description, GenKStock.Manuf, GenKTitles.ItemNumber,
> > > > GenKTitles.Title, GenKTitles.Artist, GenKTitles.mp3Files FROM
> GenKStock
> > > > INNER JOIN GenKTitles ON GenKStock.OrderNo = GenKTitles.ItemNumber
> > WHERE
> > > > (GenKTitles.Title LIKE '%' + '" + Replace(rsResults__T, "'", "''") +
> "'
> > +
> > > > '%') AND (GenKTitles.Artist LIKE '%' + '" + Replace(rsResults__A,
> "'",
> > > > "''") + "' + '%') AND (GenKStock.Category LIKE '" +
> > > > Replace(rsResults__C, "'", "''") + "') AND (GenKStock.Manuf LIKE
'"
> +
> > > > Replace(rsResults__M, "'", "''") + "') AND (GenKStock.Type LIKE '"
+
> > > > Replace(rsResults__TY, "'", "''") + "') AND
(GenKStock.SingleArtist
> > LIKE
> > > > '" + Replace(rsResults__SA, "'", "''") + "')"
> > > > rsResults.CursorType = 0
> > > > rsResults.CursorLocation = 2
> > > > rsResults.LockType = 1
> > > > rsResults.Open()
> > > >
> > > > rsResults_numRows = 0
> > > > %>
> > > >
> > > > "Chris Hohmann" <> wrote in message
> > > > news:%...
> > > > > "shank" <> wrote in message
> > > > > news:...
> > > > > > 1) I'm getting this error: Syntax error (missing operator) in
> query
> > > > > > expression on the below statement. Can I get some advice.
> > > > > >
> > > > > > 2) I searched ASPFAQ and came up blank. Where can find the
"rules"
> > for
> > > > > when
> > > > > > and how to use single quotes and double quotes in ASP?
> > > > > >
> > > > > > thanks!
> > > > > > ----------------------
> > > > > > SQL = SQL & "WHERE '" &
> > > > > >
> > > > >
> > > >
> > >
> >
>
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RE PLACE(REPLACE("GenKTitles.
> > > > > > Title","'",""),",",""),".",""),"
> > > > ",""),"the",""),"and",""),"&",""),"/","")
> > > > > &
> > > > > > "' LIKE '%' '" & T & "' '%' "
> > > > > >
> > > > >
> > > > > This appears to be related to your other "syntax error" post. If
so,
> > it
> > > > may
> > > > > have been better to post a follow-up to that thread. Given that
> > context,
> > > > it
> > > > > appears that you're attempting to eliminate false positives from
> your
> > > > > searches by discluding common words and punctuations. If this is
the
> > > case,
> > > > > perhaps it would be easier to remove those items from the search
> term
> > > > > instead of attempting to remove them from the GenKTitles.Title
> column
> > in
> > > > > your database. If you took this approach you could make use of a
> > regular
> > > > > expression to "scrub" the search term prior to constructing the
SQL
> > > > > statement. Also, please consider using a stored procedure (or
> > > > parameterized
> > > > > query depending on your database environment) instead of
dynamically
> > > > > constructing the SQL statement. Also, in the future, please
provide
> > > > > database, version, DDL, sample data and desired output/results.
> Here's
> > > are
> > > > > some guidelines
> > > > >
> > > > > http://aspfaq.com/5000
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

shank wrote:
> I really like the idea of the function, but having problems with
>
> The SQL to my page is....
> SQL = SELECT GenKStock.OrderNo, GenKStock.SingleArtist,

No it isn't. This is vbscript code that is supposed to result in a valid sql
statement.

Do this to see the actual sql statement being sent to the database:

Response.Write SQL

We NEED to see the result of this! We have no hope of figuring out what your
problem is without seeing it!

Bob Barrows


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Why are you trying to use the function in the SQL query string?

--

Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!


"shank" <> wrote in message
news:...
> I really like the idea of the function, but having problems with syntax
> again. I'm starting simple by removing the apostrophes.
>
> <%
> Function ReplaceThis(sWhat)
> ReplaceThis = Replace(UCase(sWhat), "'", "")
> End Function
> %>
>
> I'm just including the portion o fthe WHERE clause where I'm
experimenting.
>
> WHERE ( " & ReplaceThis("GenKTitles.Title") & " LIKE '%' + '" +
> ReplaceThis(rsResults__T) + "' + '%')
>
> The SQL to my page is....
> SQL = SELECT GenKStock.OrderNo, GenKStock.SingleArtist,
GenKStock.SoftHard,
> GenKStock.Category, GenKStock.Type, GenKStock.Label,
GenKStock.Description,
> GenKStock.Manuf, GenKTitles.ItemNumber, GenKTitles.Title,
GenKTitles.Artist,
> GenKTitles.mp3Files FROM GenKStock INNER JOIN GenKTitles ON
> GenKStock.OrderNo = GenKTitles.ItemNumber WHERE ( GENKTITLES.TITLE LIKE
'%'
> + 'AINT' + '%') AND (GenKTitles.Artist LIKE '%' + '%' + '%') AND
> (GenKStock.Category LIKE '%') AND (GenKStock.Manuf LIKE '%') AND
> (GenKStock.Type LIKE '%') AND (GenKStock.SingleArtist LIKE '%') ORDER BY
> OrderNo ASC
>
> The function ReplaceThis is working fine on this:
ReplaceThis(rsResults__T)
> But not on this: ReplaceThis("GenKTitles.Title")
> I'm getting results that match *aint* but not *ain't*
> What am I missing?
> thanks for the function!!!
>
> "Steven Burn" <> wrote in message
> news:...
> > Writing a function is fairly simple......
> >
> > Function ReplaceThis(sWhat)
> > '// Though not necessary, I tend to lcase
> > '// the string before doing anything with it
> > '// as it makes it easier to work with when
> > '// you want to replace the text, regardless
> > '// of whether it has upper or lowercase text
> >
> > ReplaceThis = Replace(lcase(sWhat), "the dog", "some text")
> > End Function
> >
> > Then in your page, you'd simply use;
> >
> > Response.Write ReplaceThis("The dog went over the hill")
> >
> > --
> >
> > Regards
> >
> > Steven Burn
> > Ur I.T. Mate Group
> > www.it-mate.co.uk
> >
> > Keeping it FREE!
> >
> >
> > "shank" <> wrote in message
> > news:...
> > > Thanks to all, but if I can't get past a few syntax issues, I'll never
> get
> > > past creating a function.
> > >
> > > "Steven Burn" <> wrote in message
> > > news:...
> > > > I'm certainly no expert but IMHO.......
> > > >
> > > > Suggestion #1. Use a regular expression to do the replacing for you
> > > >
> > > > This makes for much cleaner, and easier to read code. If you'd
rather
> > not
> > > > use a RegExp for whatever reason, stick to a max of 2 "Replace's"
per
> > > > line....
> > > >
> > > > Suggestion #2. Use a function that contains suggestion #1
> > > >
> > > > By using a function to do the replacing, it allows you to simply
use;
> > > >
> > > > Response.Write ReplaceThis(TheString)
> > > >
> > > > ... instead of ...
> > > >
> > > > Response.Write Replace(Replace(Replace.... etc etc etc
> > > >
> > > > Hint:
> > > >
> > > > Function ReplaceThis(sWhat)
> > > > '// RegExp or whatever, code goes here.....
> > > > End Function
> > > >
> > > > Suggestion #3. NEVER use wysywig app's such as DW, FP etc etc, to
> write
> > > the
> > > > code for you....
> > > >
> > > > As has been mentioned by Bob, they screw up virtually everything....
> tis
> > > > much easier (and quicker) to learn how to hand code it yourself
(hint:
> > > > aspfaq.com ;o)) using Notepad or some other text editor (just make
> sure
> > > you
> > > > stay away from editors such as MS Word etc, if going this route as
> > they're
> > > a
> > > > royal pain in the backside....).
> > > >
> > > > --
> > > >
> > > > Regards
> > > >
> > > > Steven Burn
> > > > Ur I.T. Mate Group
> > > > www.it-mate.co.uk
> > > >
> > > > Keeping it FREE!
> > > >
> > > >
> > > > "shank" <> wrote in message
> > > > news:...
> > > > > It was and it wasn't related to the other post. There were 2
issues
> > and
> > > if
> > > > I
> > > > > could have gotten through either, I probably could have gotten
> through
> > > > both.
> > > > > The reason I don't post everything is it looks like a huge mess of
> > code
> > > > > coming through the newsgroup. And I just figured it would make
> things
> > > more
> > > > > confusing. Here is the entire recordset created in dreamweaver. It
> > works
> > > > > fine as it is here. I know you code heads may not like dreamweaver
> but
> > > > it's
> > > > > a huge crutch for those of us that don't have the knowledge to
hand
> > code
> > > > > things. It works very well for what I want to do, up until I want
to
> > > give
> > > > an
> > > > > extra effort to make things better. For this project I have to use
> > > Access
> > > > > 2002. I don't like it, but that's not my choice. I have a music
> > database
> > > > > that users can search. I get titles and artists from many many
> > sources.
> > > > They
> > > > > all choose to spell titles and artists their own way. That's a
> > problem.
> > > In
> > > > > an effort to make it easier on the end users, I want to remove
> common
> > > > words
> > > > > and punctuation that varies. That includes "'", """, ",", "/", "
",
> > "&",
> > > > > "the", and "and". If I remove those characters from both terms, it
> > > creates
> > > > a
> > > > > much better chance of relevant search results. I've done this with
> SQL
> > > > > stored procedures and it works great. Now I have to do this with
SQL
> > in
> > > > ASP
> > > > > and I'm having a lot of syntax problems. I appreciate everyone's
> > help!!!
> > > I
> > > > > thought I was trying to make it easier on everyone and I guess I
> made
> > it
> > > > > more confusing. Sorry!
> > > > >
> > > > > I only have to remove those characters from titles and artists
> fields.
> > > The
> > > > > other fields I have control over the data. OK... now how do I
> > accomplish
> > > > > this feat?
> > > > > thanks!
> > > > >
> > > > > <%
> > > > > Dim rsResults__T
> > > > > rsResults__T = "%"
> > > > > If (Request("title") <> "") Then
> > > > > rsResults__T = Request("title")
> > > > > End If
> > > > > %>
> > > > > <%
> > > > > Dim rsResults__A
> > > > > rsResults__A = "%"
> > > > > If (Request("artist") <> "") Then
> > > > > rsResults__A = Request("artist")
> > > > > End If
> > > > > %>
> > > > > <%
> > > > > Dim rsResults__C
> > > > > rsResults__C = "%"
> > > > > If (Request("category") <> "") Then
> > > > > rsResults__C = Request("category")
> > > > > End If
> > > > > %>
> > > > > <%
> > > > > Dim rsResults__TY
> > > > > rsResults__TY = "%"
> > > > > If (Request("type") <> "") Then
> > > > > rsResults__TY = Request("type")
> > > > > End If
> > > > > %>
> > > > > <%
> > > > > Dim rsResults__M
> > > > > rsResults__M = "%"
> > > > > If (Request("manuf") <> "") Then
> > > > > rsResults__M = Request("manuf")
> > > > > End If
> > > > > %>
> > > > > <%
> > > > > Dim rsResults__SA
> > > > > rsResults__SA = "%"
> > > > > If (Request("singleartist") <> "") Then
> > > > > rsResults__SA = Request("singleartist")
> > > > > End If
> > > > > %>
> > > > > <%
> > > > > Dim rsResults
> > > > > Dim rsResults_numRows
> > > > >
> > > > > Set rsResults = Server.CreateObject("ADODB.Recordset")
> > > > > rsResults.ActiveConnection = MM_GenKAccess_STRING
> > > > > rsResults.Source = "SELECT GenKStock.OrderNo,
> GenKStock.SingleArtist,
> > > > > GenKStock.SoftHard, GenKStock.Category, GenKStock.Type,
> > GenKStock.Label,
> > > > > GenKStock.Description, GenKStock.Manuf, GenKTitles.ItemNumber,
> > > > > GenKTitles.Title, GenKTitles.Artist, GenKTitles.mp3Files FROM
> > GenKStock
> > > > > INNER JOIN GenKTitles ON GenKStock.OrderNo = GenKTitles.ItemNumber
> > > WHERE
> > > > > (GenKTitles.Title LIKE '%' + '" + Replace(rsResults__T, "'", "''")
+
> > "'
> > > +
> > > > > '%') AND (GenKTitles.Artist LIKE '%' + '" +
Replace(rsResults__A,
> > "'",
> > > > > "''") + "' + '%') AND (GenKStock.Category LIKE '" +
> > > > > Replace(rsResults__C, "'", "''") + "') AND (GenKStock.Manuf LIKE
> '"
> > +
> > > > > Replace(rsResults__M, "'", "''") + "') AND (GenKStock.Type LIKE
'"
> +
> > > > > Replace(rsResults__TY, "'", "''") + "') AND
> (GenKStock.SingleArtist
> > > LIKE
> > > > > '" + Replace(rsResults__SA, "'", "''") + "')"
> > > > > rsResults.CursorType = 0
> > > > > rsResults.CursorLocation = 2
> > > > > rsResults.LockType = 1
> > > > > rsResults.Open()
> > > > >
> > > > > rsResults_numRows = 0
> > > > > %>
> > > > >
> > > > > "Chris Hohmann" <> wrote in message
> > > > > news:%...
> > > > > > "shank" <> wrote in message
> > > > > > news:...
> > > > > > > 1) I'm getting this error: Syntax error (missing operator) in
> > query
> > > > > > > expression on the below statement. Can I get some advice.
> > > > > > >
> > > > > > > 2) I searched ASPFAQ and came up blank. Where can find the
> "rules"
> > > for
> > > > > > when
> > > > > > > and how to use single quotes and double quotes in ASP?
> > > > > > >
> > > > > > > thanks!
> > > > > > > ----------------------
> > > > > > > SQL = SQL & "WHERE '" &
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RE PLACE(REPLACE("GenKTitles.
> > > > > > > Title","'",""),",",""),".",""),"
> > > > > ",""),"the",""),"and",""),"&",""),"/","")
> > > > > > &
> > > > > > > "' LIKE '%' '" & T & "' '%' "
> > > > > > >
> > > > > >
> > > > > > This appears to be related to your other "syntax error" post. If
> so,
> > > it
> > > > > may
> > > > > > have been better to post a follow-up to that thread. Given that
> > > context,
> > > > > it
> > > > > > appears that you're attempting to eliminate false positives from
> > your
> > > > > > searches by discluding common words and punctuations. If this is
> the
> > > > case,
> > > > > > perhaps it would be easier to remove those items from the search
> > term
> > > > > > instead of attempting to remove them from the GenKTitles.Title
> > column
> > > in
> > > > > > your database. If you took this approach you could make use of a
> > > regular
> > > > > > expression to "scrub" the search term prior to constructing the
> SQL
> > > > > > statement. Also, please consider using a stored procedure (or
> > > > > parameterized
> > > > > > query depending on your database environment) instead of
> dynamically
> > > > > > constructing the SQL statement. Also, in the future, please
> provide
> > > > > > database, version, DDL, sample data and desired output/results.
> > Here's
> > > > are
> > > > > > some guidelines
> > > > > >
> > > > > > http://aspfaq.com/5000
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

This is my code to build the recordset...
<%
Dim rsResults
Dim rsResults_numRows

Set rsResults = Server.CreateObject("ADODB.Recordset")
rsResults.ActiveConnection = MM_GenKAccess_STRING
rsResults.Source = "SELECT GenKStock.OrderNo, GenKStock.SingleArtist,
GenKStock.SoftHard, GenKStock.Category, GenKStock.Type, GenKStock.Label,
GenKStock.Description, GenKStock.Manuf, GenKTitles.ItemNumber,
GenKTitles.Title, GenKTitles.Artist, GenKTitles.mp3Files FROM GenKStock
INNER JOIN GenKTitles ON GenKStock.OrderNo = GenKTitles.ItemNumber WHERE
( " & ReplaceThis("GenKTitles.Title") & " LIKE '%' + '" +
ReplaceThis(rsResults__T) + "' + '%') AND (GenKTitles.Artist LIKE '%' + '"
+ Replace(rsResults__A, "'", "''") + "' + '%') AND (GenKStock.Category
LIKE '" + Replace(rsResults__C, "'", "''") + "') AND (GenKStock.Manuf LIKE
'" + Replace(rsResults__M, "'", "''") + "') AND (GenKStock.Type LIKE '" +
Replace(rsResults__TY, "'", "''") + "') AND (GenKStock.SingleArtist LIKE
'" + Replace(rsResults__SA, "'", "''") + "') ORDER BY " +
Replace(rsResults__sql_orderby, "'", "''") + ""
rsResults.CursorType = 0
rsResults.CursorLocation = 2
rsResults.LockType = 1
rsResults.Open()

rsResults_numRows = 0
%>

Then, down in the HTML I have this code: SQL = <%=rsResults.Source%>

Which gives me this: SQL = SELECT GenKStock.OrderNo, GenKStock.SingleArtist,
GenKStock.SoftHard, GenKStock.Category, GenKStock.Type, GenKStock.Label,
GenKStock.Description, GenKStock.Manuf, GenKTitles.ItemNumber,
GenKTitles.Title, GenKTitles.Artist, GenKTitles.mp3Files FROM GenKStock
INNER JOIN GenKTitles ON GenKStock.OrderNo = GenKTitles.ItemNumber WHERE (
GENKTITLES.TITLE LIKE '%' + 'AINT' + '%') AND (GenKTitles.Artist LIKE '%' +
'%' + '%') AND (GenKStock.Category LIKE '%') AND (GenKStock.Manuf LIKE '%')
AND (GenKStock.Type LIKE '%') AND (GenKStock.SingleArtist LIKE '%') ORDER BY
OrderNo ASC

Response.write SQL gives me nothing because there is no variable named SQL.
This is response.write SQL: <%=Response.Write(SQL)%> (gives me no results)
Sorry if I don't understand what you want.

"Bob Barrows [MVP]" <> wrote in message
news:...
> shank wrote:
> > I really like the idea of the function, but having problems with
> >
> > The SQL to my page is....
> > SQL = SELECT GenKStock.OrderNo, GenKStock.SingleArtist,
>
> No it isn't. This is vbscript code that is supposed to result in a valid
sql
> statement.
>
> Do this to see the actual sql statement being sent to the database:
>
> Response.Write SQL
>
> We NEED to see the result of this! We have no hope of figuring out what
your
> problem is without seeing it!
>
> Bob Barrows
>
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>

Example: In the data, some variations of the word ain't include: ain't,
aint, aint'
I have no control over what the user searches on, so I have to remove the
apostrophe from what they submit and also the apostrophe that's in the data.
Otherwise I would not get a hit on all 3 variations.

"Steven Burn" <> wrote in message
news:%...
> Why are you trying to use the function in the SQL query string?
>
> --
>
> Regards
>
> Steven Burn
> Ur I.T. Mate Group
> www.it-mate.co.uk
>
> Keeping it FREE!
>
>
> "shank" <> wrote in message
> news:...
> > I really like the idea of the function, but having problems with syntax
> > again. I'm starting simple by removing the apostrophes.
> >
> > <%
> > Function ReplaceThis(sWhat)
> > ReplaceThis = Replace(UCase(sWhat), "'", "")
> > End Function
> > %>
> >
> > I'm just including the portion o fthe WHERE clause where I'm
> experimenting.
> >
> > WHERE ( " & ReplaceThis("GenKTitles.Title") & " LIKE '%' + '" +
> > ReplaceThis(rsResults__T) + "' + '%')
> >
> > The SQL to my page is....
> > SQL = SELECT GenKStock.OrderNo, GenKStock.SingleArtist,
> GenKStock.SoftHard,
> > GenKStock.Category, GenKStock.Type, GenKStock.Label,
> GenKStock.Description,
> > GenKStock.Manuf, GenKTitles.ItemNumber, GenKTitles.Title,
> GenKTitles.Artist,
> > GenKTitles.mp3Files FROM GenKStock INNER JOIN GenKTitles ON
> > GenKStock.OrderNo = GenKTitles.ItemNumber WHERE ( GENKTITLES.TITLE LIKE
> '%'
> > + 'AINT' + '%') AND (GenKTitles.Artist LIKE '%' + '%' + '%') AND
> > (GenKStock.Category LIKE '%') AND (GenKStock.Manuf LIKE '%') AND
> > (GenKStock.Type LIKE '%') AND (GenKStock.SingleArtist LIKE '%') ORDER BY
> > OrderNo ASC
> >
> > The function ReplaceThis is working fine on this:
> ReplaceThis(rsResults__T)
> > But not on this: ReplaceThis("GenKTitles.Title")
> > I'm getting results that match *aint* but not *ain't*
> > What am I missing?
> > thanks for the function!!!
> >
> > "Steven Burn" <> wrote in message
> > news:...
> > > Writing a function is fairly simple......
> > >
> > > Function ReplaceThis(sWhat)
> > > '// Though not necessary, I tend to lcase
> > > '// the string before doing anything with it
> > > '// as it makes it easier to work with when
> > > '// you want to replace the text, regardless
> > > '// of whether it has upper or lowercase text
> > >
> > > ReplaceThis = Replace(lcase(sWhat), "the dog", "some text")
> > > End Function
> > >
> > > Then in your page, you'd simply use;
> > >
> > > Response.Write ReplaceThis("The dog went over the hill")
> > >
> > > --
> > >
> > > Regards
> > >
> > > Steven Burn
> > > Ur I.T. Mate Group
> > > www.it-mate.co.uk
> > >
> > > Keeping it FREE!
> > >
> > >
> > > "shank" <> wrote in message
> > > news:...
> > > > Thanks to all, but if I can't get past a few syntax issues, I'll
never
> > get
> > > > past creating a function.
> > > >
> > > > "Steven Burn" <> wrote in message
> > > > news:...
> > > > > I'm certainly no expert but IMHO.......
> > > > >
> > > > > Suggestion #1. Use a regular expression to do the replacing for
you
> > > > >
> > > > > This makes for much cleaner, and easier to read code. If you'd
> rather
> > > not
> > > > > use a RegExp for whatever reason, stick to a max of 2 "Replace's"
> per
> > > > > line....
> > > > >
> > > > > Suggestion #2. Use a function that contains suggestion #1
> > > > >
> > > > > By using a function to do the replacing, it allows you to simply
> use;
> > > > >
> > > > > Response.Write ReplaceThis(TheString)
> > > > >
> > > > > ... instead of ...
> > > > >
> > > > > Response.Write Replace(Replace(Replace.... etc etc etc
> > > > >
> > > > > Hint:
> > > > >
> > > > > Function ReplaceThis(sWhat)
> > > > > '// RegExp or whatever, code goes here.....
> > > > > End Function
> > > > >
> > > > > Suggestion #3. NEVER use wysywig app's such as DW, FP etc etc, to
> > write
> > > > the
> > > > > code for you....
> > > > >
> > > > > As has been mentioned by Bob, they screw up virtually
everything....
> > tis
> > > > > much easier (and quicker) to learn how to hand code it yourself
> (hint:
> > > > > aspfaq.com ;o)) using Notepad or some other text editor (just make
> > sure
> > > > you
> > > > > stay away from editors such as MS Word etc, if going this route as
> > > they're
> > > > a
> > > > > royal pain in the backside....).
> > > > >
> > > > > --
> > > > >
> > > > > Regards
> > > > >
> > > > > Steven Burn
> > > > > Ur I.T. Mate Group
> > > > > www.it-mate.co.uk
> > > > >
> > > > > Keeping it FREE!
> > > > >
> > > > >
> > > > > "shank" <> wrote in message
> > > > > news:...
> > > > > > It was and it wasn't related to the other post. There were 2
> issues
> > > and
> > > > if
> > > > > I
> > > > > > could have gotten through either, I probably could have gotten
> > through
> > > > > both.
> > > > > > The reason I don't post everything is it looks like a huge mess
of
> > > code
> > > > > > coming through the newsgroup. And I just figured it would make
> > things
> > > > more
> > > > > > confusing. Here is the entire recordset created in dreamweaver.
It
> > > works
> > > > > > fine as it is here. I know you code heads may not like
dreamweaver
> > but
> > > > > it's
> > > > > > a huge crutch for those of us that don't have the knowledge to
> hand
> > > code
> > > > > > things. It works very well for what I want to do, up until I
want
> to
> > > > give
> > > > > an
> > > > > > extra effort to make things better. For this project I have to
use
> > > > Access
> > > > > > 2002. I don't like it, but that's not my choice. I have a music
> > > database
> > > > > > that users can search. I get titles and artists from many many
> > > sources.
> > > > > They
> > > > > > all choose to spell titles and artists their own way. That's a
> > > problem.
> > > > In
> > > > > > an effort to make it easier on the end users, I want to remove
> > common
> > > > > words
> > > > > > and punctuation that varies. That includes "'", """, ",", "/", "
> ",
> > > "&",
> > > > > > "the", and "and". If I remove those characters from both terms,
it
> > > > creates
> > > > > a
> > > > > > much better chance of relevant search results. I've done this
with
> > SQL
> > > > > > stored procedures and it works great. Now I have to do this with
> SQL
> > > in
> > > > > ASP
> > > > > > and I'm having a lot of syntax problems. I appreciate everyone's
> > > help!!!
> > > > I
> > > > > > thought I was trying to make it easier on everyone and I guess I
> > made
> > > it
> > > > > > more confusing. Sorry!
> > > > > >
> > > > > > I only have to remove those characters from titles and artists
> > fields.
> > > > The
> > > > > > other fields I have control over the data. OK... now how do I
> > > accomplish
> > > > > > this feat?
> > > > > > thanks!
> > > > > >
> > > > > > <%
> > > > > > Dim rsResults__T
> > > > > > rsResults__T = "%"
> > > > > > If (Request("title") <> "") Then
> > > > > > rsResults__T = Request("title")
> > > > > > End If
> > > > > > %>
> > > > > > <%
> > > > > > Dim rsResults__A
> > > > > > rsResults__A = "%"
> > > > > > If (Request("artist") <> "") Then
> > > > > > rsResults__A = Request("artist")
> > > > > > End If
> > > > > > %>
> > > > > > <%
> > > > > > Dim rsResults__C
> > > > > > rsResults__C = "%"
> > > > > > If (Request("category") <> "") Then
> > > > > > rsResults__C = Request("category")
> > > > > > End If
> > > > > > %>
> > > > > > <%
> > > > > > Dim rsResults__TY
> > > > > > rsResults__TY = "%"
> > > > > > If (Request("type") <> "") Then
> > > > > > rsResults__TY = Request("type")
> > > > > > End If
> > > > > > %>
> > > > > > <%
> > > > > > Dim rsResults__M
> > > > > > rsResults__M = "%"
> > > > > > If (Request("manuf") <> "") Then
> > > > > > rsResults__M = Request("manuf")
> > > > > > End If
> > > > > > %>
> > > > > > <%
> > > > > > Dim rsResults__SA
> > > > > > rsResults__SA = "%"
> > > > > > If (Request("singleartist") <> "") Then
> > > > > > rsResults__SA = Request("singleartist")
> > > > > > End If
> > > > > > %>
> > > > > > <%
> > > > > > Dim rsResults
> > > > > > Dim rsResults_numRows
> > > > > >
> > > > > > Set rsResults = Server.CreateObject("ADODB.Recordset")
> > > > > > rsResults.ActiveConnection = MM_GenKAccess_STRING
> > > > > > rsResults.Source = "SELECT GenKStock.OrderNo,
> > GenKStock.SingleArtist,
> > > > > > GenKStock.SoftHard, GenKStock.Category, GenKStock.Type,
> > > GenKStock.Label,
> > > > > > GenKStock.Description, GenKStock.Manuf, GenKTitles.ItemNumber,
> > > > > > GenKTitles.Title, GenKTitles.Artist, GenKTitles.mp3Files FROM
> > > GenKStock
> > > > > > INNER JOIN GenKTitles ON GenKStock.OrderNo =
GenKTitles.ItemNumber
> > > > WHERE
> > > > > > (GenKTitles.Title LIKE '%' + '" + Replace(rsResults__T, "'",
"''")
> +
> > > "'
> > > > +
> > > > > > '%') AND (GenKTitles.Artist LIKE '%' + '" +
> Replace(rsResults__A,
> > > "'",
> > > > > > "''") + "' + '%') AND (GenKStock.Category LIKE '" +
> > > > > > Replace(rsResults__C, "'", "''") + "') AND (GenKStock.Manuf
LIKE
> > '"
> > > +
> > > > > > Replace(rsResults__M, "'", "''") + "') AND (GenKStock.Type LIKE
> '"
> > +
> > > > > > Replace(rsResults__TY, "'", "''") + "') AND
> > (GenKStock.SingleArtist
> > > > LIKE
> > > > > > '" + Replace(rsResults__SA, "'", "''") + "')"
> > > > > > rsResults.CursorType = 0
> > > > > > rsResults.CursorLocation = 2
> > > > > > rsResults.LockType = 1
> > > > > > rsResults.Open()
> > > > > >
> > > > > > rsResults_numRows = 0
> > > > > > %>
> > > > > >
> > > > > > "Chris Hohmann" <> wrote in message
> > > > > > news:%...
> > > > > > > "shank" <> wrote in message
> > > > > > > news:...
> > > > > > > > 1) I'm getting this error: Syntax error (missing operator)
in
> > > query
> > > > > > > > expression on the below statement. Can I get some advice.
> > > > > > > >
> > > > > > > > 2) I searched ASPFAQ and came up blank. Where can find the
> > "rules"
> > > > for
> > > > > > > when
> > > > > > > > and how to use single quotes and double quotes in ASP?
> > > > > > > >
> > > > > > > > thanks!
> > > > > > > > ----------------------
> > > > > > > > SQL = SQL & "WHERE '" &
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RE PLACE(REPLACE("GenKTitles.
> > > > > > > > Title","'",""),",",""),".",""),"
> > > > > > ",""),"the",""),"and",""),"&",""),"/","")
> > > > > > > &
> > > > > > > > "' LIKE '%' '" & T & "' '%' "
> > > > > > > >
> > > > > > >
> > > > > > > This appears to be related to your other "syntax error" post.
If
> > so,
> > > > it
> > > > > > may
> > > > > > > have been better to post a follow-up to that thread. Given
that
> > > > context,
> > > > > > it
> > > > > > > appears that you're attempting to eliminate false positives
from
> > > your
> > > > > > > searches by discluding common words and punctuations. If this
is
> > the
> > > > > case,
> > > > > > > perhaps it would be easier to remove those items from the
search
> > > term
> > > > > > > instead of attempting to remove them from the GenKTitles.Title
> > > column
> > > > in
> > > > > > > your database. If you took this approach you could make use of
a
> > > > regular
> > > > > > > expression to "scrub" the search term prior to constructing
the
> > SQL
> > > > > > > statement. Also, please consider using a stored procedure (or
> > > > > > parameterized
> > > > > > > query depending on your database environment) instead of
> > dynamically
> > > > > > > constructing the SQL statement. Also, in the future, please
> > provide
> > > > > > > database, version, DDL, sample data and desired
output/results.
> > > Here's
> > > > > are
> > > > > > > some guidelines
> > > > > > >
> > > > > > > http://aspfaq.com/5000
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

He means do a response.write on SQL where SQL is whatever your writing to
the browser

--

Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!


"shank" <> wrote in message
news:...
> This is my code to build the recordset...
> <%
> Dim rsResults
> Dim rsResults_numRows
>
> Set rsResults = Server.CreateObject("ADODB.Recordset")
> rsResults.ActiveConnection = MM_GenKAccess_STRING
> rsResults.Source = "SELECT GenKStock.OrderNo, GenKStock.SingleArtist,
> GenKStock.SoftHard, GenKStock.Category, GenKStock.Type, GenKStock.Label,
> GenKStock.Description, GenKStock.Manuf, GenKTitles.ItemNumber,
> GenKTitles.Title, GenKTitles.Artist, GenKTitles.mp3Files FROM GenKStock
> INNER JOIN GenKTitles ON GenKStock.OrderNo = GenKTitles.ItemNumber WHERE
> ( " & ReplaceThis("GenKTitles.Title") & " LIKE '%' + '" +
> ReplaceThis(rsResults__T) + "' + '%') AND (GenKTitles.Artist LIKE '%' +
'"
> + Replace(rsResults__A, "'", "''") + "' + '%') AND (GenKStock.Category
> LIKE '" + Replace(rsResults__C, "'", "''") + "') AND (GenKStock.Manuf
LIKE
> '" + Replace(rsResults__M, "'", "''") + "') AND (GenKStock.Type LIKE '" +
> Replace(rsResults__TY, "'", "''") + "') AND (GenKStock.SingleArtist LIKE
> '" + Replace(rsResults__SA, "'", "''") + "') ORDER BY " +
> Replace(rsResults__sql_orderby, "'", "''") + ""
> rsResults.CursorType = 0
> rsResults.CursorLocation = 2
> rsResults.LockType = 1
> rsResults.Open()
>
> rsResults_numRows = 0
> %>
>
> Then, down in the HTML I have this code: SQL = <%=rsResults.Source%>
>
> Which gives me this: SQL = SELECT GenKStock.OrderNo,
GenKStock.SingleArtist,
> GenKStock.SoftHard, GenKStock.Category, GenKStock.Type, GenKStock.Label,
> GenKStock.Description, GenKStock.Manuf, GenKTitles.ItemNumber,
> GenKTitles.Title, GenKTitles.Artist, GenKTitles.mp3Files FROM GenKStock
> INNER JOIN GenKTitles ON GenKStock.OrderNo = GenKTitles.ItemNumber WHERE (
> GENKTITLES.TITLE LIKE '%' + 'AINT' + '%') AND (GenKTitles.Artist LIKE '%'
+
> '%' + '%') AND (GenKStock.Category LIKE '%') AND (GenKStock.Manuf LIKE
'%')
> AND (GenKStock.Type LIKE '%') AND (GenKStock.SingleArtist LIKE '%') ORDER
BY
> OrderNo ASC
>
> Response.write SQL gives me nothing because there is no variable named
SQL.
> This is response.write SQL: <%=Response.Write(SQL)%> (gives me no results)
> Sorry if I don't understand what you want.
>
> "Bob Barrows [MVP]" <> wrote in message
> news:...
> > shank wrote:
> > > I really like the idea of the function, but having problems with
> > >
> > > The SQL to my page is....
> > > SQL = SELECT GenKStock.OrderNo, GenKStock.SingleArtist,
> >
> > No it isn't. This is vbscript code that is supposed to result in a valid
> sql
> > statement.
> >
> > Do this to see the actual sql statement being sent to the database:
> >
> > Response.Write SQL
> >
> > We NEED to see the result of this! We have no hope of figuring out what
> your
> > problem is without seeing it!
> >
> > Bob Barrows
> >
> >
> > --
> > Microsoft MVP - ASP/ASP.NET
> > Please reply to the newsgroup. This email account is my spam trap so I
> > don't check it very often. If you must reply off-line, then remove the
> > "NO SPAM"
> >
> >
>
>

But surely, if it's to be an effective search, you don't want a match on all
variations?. For example, if I search for "some text" on dogpile.com, I'd
expect it to only return results with "some text", not "some' text" or
"sometext" or "some text's" etc

My point being, don't bother trying to account for what the user will or
will not search for as you'll still be trying to figure it out for the next
hundred years or so, and instead, have the user decide what will and will
not be returned.

--

Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!


"shank" <> wrote in message
news:...
> Example: In the data, some variations of the word ain't include: ain't,
> aint, aint'
> I have no control over what the user searches on, so I have to remove the
> apostrophe from what they submit and also the apostrophe that's in the
data.
> Otherwise I would not get a hit on all 3 variations.
>
> "Steven Burn" <> wrote in message
> news:%...
> > Why are you trying to use the function in the SQL query string?
> >
> > --
> >
> > Regards
> >
> > Steven Burn
> > Ur I.T. Mate Group
> > www.it-mate.co.uk
> >
> > Keeping it FREE!
> >
> >
> > "shank" <> wrote in message
> > news:...
> > > I really like the idea of the function, but having problems with
syntax
> > > again. I'm starting simple by removing the apostrophes.
> > >
> > > <%
> > > Function ReplaceThis(sWhat)
> > > ReplaceThis = Replace(UCase(sWhat), "'", "")
> > > End Function
> > > %>
> > >
> > > I'm just including the portion o fthe WHERE clause where I'm
> > experimenting.
> > >
> > > WHERE ( " & ReplaceThis("GenKTitles.Title") & " LIKE '%' + '" +
> > > ReplaceThis(rsResults__T) + "' + '%')
> > >
> > > The SQL to my page is....
> > > SQL = SELECT GenKStock.OrderNo, GenKStock.SingleArtist,
> > GenKStock.SoftHard,
> > > GenKStock.Category, GenKStock.Type, GenKStock.Label,
> > GenKStock.Description,
> > > GenKStock.Manuf, GenKTitles.ItemNumber, GenKTitles.Title,
> > GenKTitles.Artist,
> > > GenKTitles.mp3Files FROM GenKStock INNER JOIN GenKTitles ON
> > > GenKStock.OrderNo = GenKTitles.ItemNumber WHERE ( GENKTITLES.TITLE
LIKE
> > '%'
> > > + 'AINT' + '%') AND (GenKTitles.Artist LIKE '%' + '%' + '%') AND
> > > (GenKStock.Category LIKE '%') AND (GenKStock.Manuf LIKE '%') AND
> > > (GenKStock.Type LIKE '%') AND (GenKStock.SingleArtist LIKE '%') ORDER
BY
> > > OrderNo ASC
> > >
> > > The function ReplaceThis is working fine on this:
> > ReplaceThis(rsResults__T)
> > > But not on this: ReplaceThis("GenKTitles.Title")
> > > I'm getting results that match *aint* but not *ain't*
> > > What am I missing?
> > > thanks for the function!!!
> > >
> > > "Steven Burn" <> wrote in message
> > > news:...
> > > > Writing a function is fairly simple......
> > > >
> > > > Function ReplaceThis(sWhat)
> > > > '// Though not necessary, I tend to lcase
> > > > '// the string before doing anything with it
> > > > '// as it makes it easier to work with when
> > > > '// you want to replace the text, regardless
> > > > '// of whether it has upper or lowercase text
> > > >
> > > > ReplaceThis = Replace(lcase(sWhat), "the dog", "some text")
> > > > End Function
> > > >
> > > > Then in your page, you'd simply use;
> > > >
> > > > Response.Write ReplaceThis("The dog went over the hill")
> > > >
> > > > --
> > > >
> > > > Regards
> > > >
> > > > Steven Burn
> > > > Ur I.T. Mate Group
> > > > www.it-mate.co.uk
> > > >
> > > > Keeping it FREE!
> > > >
> > > >
> > > > "shank" <> wrote in message
> > > > news:...
> > > > > Thanks to all, but if I can't get past a few syntax issues, I'll
> never
> > > get
> > > > > past creating a function.
> > > > >
> > > > > "Steven Burn" <> wrote in message
> > > > > news:...
> > > > > > I'm certainly no expert but IMHO.......
> > > > > >
> > > > > > Suggestion #1. Use a regular expression to do the replacing for
> you
> > > > > >
> > > > > > This makes for much cleaner, and easier to read code. If you'd
> > rather
> > > > not
> > > > > > use a RegExp for whatever reason, stick to a max of 2
"Replace's"
> > per
> > > > > > line....
> > > > > >
> > > > > > Suggestion #2. Use a function that contains suggestion #1
> > > > > >
> > > > > > By using a function to do the replacing, it allows you to simply
> > use;
> > > > > >
> > > > > > Response.Write ReplaceThis(TheString)
> > > > > >
> > > > > > ... instead of ...
> > > > > >
> > > > > > Response.Write Replace(Replace(Replace.... etc etc etc
> > > > > >
> > > > > > Hint:
> > > > > >
> > > > > > Function ReplaceThis(sWhat)
> > > > > > '// RegExp or whatever, code goes here.....
> > > > > > End Function
> > > > > >
> > > > > > Suggestion #3. NEVER use wysywig app's such as DW, FP etc etc,
to
> > > write
> > > > > the
> > > > > > code for you....
> > > > > >
> > > > > > As has been mentioned by Bob, they screw up virtually
> everything....
> > > tis
> > > > > > much easier (and quicker) to learn how to hand code it yourself
> > (hint:
> > > > > > aspfaq.com ;o)) using Notepad or some other text editor (just
make
> > > sure
> > > > > you
> > > > > > stay away from editors such as MS Word etc, if going this route
as
> > > > they're
> > > > > a
> > > > > > royal pain in the backside....).
> > > > > >
> > > > > > --
> > > > > >
> > > > > > Regards
> > > > > >
> > > > > > Steven Burn
> > > > > > Ur I.T. Mate Group
> > > > > > www.it-mate.co.uk
> > > > > >
> > > > > > Keeping it FREE!
> > > > > >
> > > > > >
> > > > > > "shank" <> wrote in message
> > > > > > news:...
> > > > > > > It was and it wasn't related to the other post. There were 2
> > issues
> > > > and
> > > > > if
> > > > > > I
> > > > > > > could have gotten through either, I probably could have gotten
> > > through
> > > > > > both.
> > > > > > > The reason I don't post everything is it looks like a huge
mess
> of
> > > > code
> > > > > > > coming through the newsgroup. And I just figured it would make
> > > things
> > > > > more
> > > > > > > confusing. Here is the entire recordset created in
dreamweaver.
> It
> > > > works
> > > > > > > fine as it is here. I know you code heads may not like
> dreamweaver
> > > but
> > > > > > it's
> > > > > > > a huge crutch for those of us that don't have the knowledge to
> > hand
> > > > code
> > > > > > > things. It works very well for what I want to do, up until I
> want
> > to
> > > > > give
> > > > > > an
> > > > > > > extra effort to make things better. For this project I have to
> use
> > > > > Access
> > > > > > > 2002. I don't like it, but that's not my choice. I have a
music
> > > > database
> > > > > > > that users can search. I get titles and artists from many many
> > > > sources.
> > > > > > They
> > > > > > > all choose to spell titles and artists their own way. That's a
> > > > problem.
> > > > > In
> > > > > > > an effort to make it easier on the end users, I want to remove
> > > common
> > > > > > words
> > > > > > > and punctuation that varies. That includes "'", """, ",", "/",
"
> > ",
> > > > "&",
> > > > > > > "the", and "and". If I remove those characters from both
terms,
> it
> > > > > creates
> > > > > > a
> > > > > > > much better chance of relevant search results. I've done this
> with
> > > SQL
> > > > > > > stored procedures and it works great. Now I have to do this
with
> > SQL
> > > > in
> > > > > > ASP
> > > > > > > and I'm having a lot of syntax problems. I appreciate
everyone's
> > > > help!!!
> > > > > I
> > > > > > > thought I was trying to make it easier on everyone and I guess
I
> > > made
> > > > it
> > > > > > > more confusing. Sorry!
> > > > > > >
> > > > > > > I only have to remove those characters from titles and artists
> > > fields.
> > > > > The
> > > > > > > other fields I have control over the data. OK... now how do I
> > > > accomplish
> > > > > > > this feat?
> > > > > > > thanks!
> > > > > > >
> > > > > > > <%
> > > > > > > Dim rsResults__T
> > > > > > > rsResults__T = "%"
> > > > > > > If (Request("title") <> "") Then
> > > > > > > rsResults__T = Request("title")
> > > > > > > End If
> > > > > > > %>
> > > > > > > <%
> > > > > > > Dim rsResults__A
> > > > > > > rsResults__A = "%"
> > > > > > > If (Request("artist") <> "") Then
> > > > > > > rsResults__A = Request("artist")
> > > > > > > End If
> > > > > > > %>
> > > > > > > <%
> > > > > > > Dim rsResults__C
> > > > > > > rsResults__C = "%"
> > > > > > > If (Request("category") <> "") Then
> > > > > > > rsResults__C = Request("category")
> > > > > > > End If
> > > > > > > %>
> > > > > > > <%
> > > > > > > Dim rsResults__TY
> > > > > > > rsResults__TY = "%"
> > > > > > > If (Request("type") <> "") Then
> > > > > > > rsResults__TY = Request("type")
> > > > > > > End If
> > > > > > > %>
> > > > > > > <%
> > > > > > > Dim rsResults__M
> > > > > > > rsResults__M = "%"
> > > > > > > If (Request("manuf") <> "") Then
> > > > > > > rsResults__M = Request("manuf")
> > > > > > > End If
> > > > > > > %>
> > > > > > > <%
> > > > > > > Dim rsResults__SA
> > > > > > > rsResults__SA = "%"
> > > > > > > If (Request("singleartist") <> "") Then
> > > > > > > rsResults__SA = Request("singleartist")
> > > > > > > End If
> > > > > > > %>
> > > > > > > <%
> > > > > > > Dim rsResults
> > > > > > > Dim rsResults_numRows
> > > > > > >
> > > > > > > Set rsResults = Server.CreateObject("ADODB.Recordset")
> > > > > > > rsResults.ActiveConnection = MM_GenKAccess_STRING
> > > > > > > rsResults.Source = "SELECT GenKStock.OrderNo,
> > > GenKStock.SingleArtist,
> > > > > > > GenKStock.SoftHard, GenKStock.Category, GenKStock.Type,
> > > > GenKStock.Label,
> > > > > > > GenKStock.Description, GenKStock.Manuf, GenKTitles.ItemNumber,
> > > > > > > GenKTitles.Title, GenKTitles.Artist, GenKTitles.mp3Files FROM
> > > > GenKStock
> > > > > > > INNER JOIN GenKTitles ON GenKStock.OrderNo =
> GenKTitles.ItemNumber
> > > > > WHERE
> > > > > > > (GenKTitles.Title LIKE '%' + '" + Replace(rsResults__T, "'",
> "''")
> > +
> > > > "'
> > > > > +
> > > > > > > '%') AND (GenKTitles.Artist LIKE '%' + '" +
> > Replace(rsResults__A,
> > > > "'",
> > > > > > > "''") + "' + '%') AND (GenKStock.Category LIKE '" +
> > > > > > > Replace(rsResults__C, "'", "''") + "') AND (GenKStock.Manuf
> LIKE
> > > '"
> > > > +
> > > > > > > Replace(rsResults__M, "'", "''") + "') AND (GenKStock.Type
LIKE
> > '"
> > > +
> > > > > > > Replace(rsResults__TY, "'", "''") + "') AND
> > > (GenKStock.SingleArtist
> > > > > LIKE
> > > > > > > '" + Replace(rsResults__SA, "'", "''") + "')"
> > > > > > > rsResults.CursorType = 0
> > > > > > > rsResults.CursorLocation = 2
> > > > > > > rsResults.LockType = 1
> > > > > > > rsResults.Open()
> > > > > > >
> > > > > > > rsResults_numRows = 0
> > > > > > > %>
> > > > > > >
> > > > > > > "Chris Hohmann" <> wrote in message
> > > > > > > news:%...
> > > > > > > > "shank" <> wrote in message
> > > > > > > > news:...
> > > > > > > > > 1) I'm getting this error: Syntax error (missing operator)
> in
> > > > query
> > > > > > > > > expression on the below statement. Can I get some advice.
> > > > > > > > >
> > > > > > > > > 2) I searched ASPFAQ and came up blank. Where can find the
> > > "rules"
> > > > > for
> > > > > > > > when
> > > > > > > > > and how to use single quotes and double quotes in ASP?
> > > > > > > > >
> > > > > > > > > thanks!
> > > > > > > > > ----------------------
> > > > > > > > > SQL = SQL & "WHERE '" &
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RE PLACE(REPLACE("GenKTitles.
> > > > > > > > > Title","'",""),",",""),".",""),"
> > > > > > > ",""),"the",""),"and",""),"&",""),"/","")
> > > > > > > > &
> > > > > > > > > "' LIKE '%' '" & T & "' '%' "
> > > > > > > > >
> > > > > > > >
> > > > > > > > This appears to be related to your other "syntax error"
post.
> If
> > > so,
> > > > > it
> > > > > > > may
> > > > > > > > have been better to post a follow-up to that thread. Given
> that
> > > > > context,
> > > > > > > it
> > > > > > > > appears that you're attempting to eliminate false positives
> from
> > > > your
> > > > > > > > searches by discluding common words and punctuations. If
this
> is
> > > the
> > > > > > case,
> > > > > > > > perhaps it would be easier to remove those items from the
> search
> > > > term
> > > > > > > > instead of attempting to remove them from the
GenKTitles.Title
> > > > column
> > > > > in
> > > > > > > > your database. If you took this approach you could make use
of
> a
> > > > > regular
> > > > > > > > expression to "scrub" the search term prior to constructing
> the
> > > SQL
> > > > > > > > statement. Also, please consider using a stored procedure
(or
> > > > > > > parameterized
> > > > > > > > query depending on your database environment) instead of
> > > dynamically
> > > > > > > > constructing the SQL statement. Also, in the future, please
> > > provide
> > > > > > > > database, version, DDL, sample data and desired
> output/results.
> > > > Here's
> > > > > > are
> > > > > > > > some guidelines
> > > > > > > >
> > > > > > > > http://aspfaq.com/5000
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>