Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Syntax error (missing operator) in query expression

Reply
Thread Tools

Syntax error (missing operator) in query expression

 
 
Bob Lehmann
Guest
Posts: n/a
 
      08-09-2004
What Steve means is - Why aren't you using it in your IF blocks...
<%
Dim rsResults__A
rsResults__A = "%"
If (Request("artist") <> "") Then
rsResults__A = ReplaceThis(Request("artist"))
End If
%>

Then just use rsResults__A when you build your string fir the SQL, which
will make that code much more readable.

Does GENKTITLES.TITLE contain a " ' "? If it does, then why would you expect
a match?

WTF is this all about? AND (GenKTitles.Artist LIKE '%' + '%' + '%') AND

Bob Lehmann


"shank" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > > 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" <(E-Mail Removed)> wrote in message
> > > > news:(E-Mail Removed)...
> > > > > Thanks to all, but if I can't get past a few syntax issues, I'll

> never
> > > get
> > > > > past creating a function.
> > > > >
> > > > > "Steven Burn" <(E-Mail Removed)> wrote in message
> > > > > news:(E-Mail Removed)...
> > > > > > 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" <(E-Mail Removed)> wrote in message
> > > > > > news:(E-Mail Removed)...
> > > > > > > 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" <(E-Mail Removed)> wrote in message
> > > > > > > news:%(E-Mail Removed)...
> > > > > > > > "shank" <(E-Mail Removed)> wrote in message
> > > > > > > > news:(E-Mail Removed)...
> > > > > > > > > 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("GenKTitle

s.
> > > > > > > > > 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
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >

> >
> >

>
>



 
Reply With Quote
 
 
 
 
Steven Burn
Guest
Posts: n/a
 
      08-09-2004
> What Steve means is - Why aren't you using it in your IF blocks...

hehe, cheers Bob ;o) (think I'm having a blonde day today)

--

Regards

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

Keeping it FREE!


"Bob Lehmann" <(E-Mail Removed)> wrote in message
news:Om$(E-Mail Removed)...
> What Steve means is - Why aren't you using it in your IF blocks...
> <%
> Dim rsResults__A
> rsResults__A = "%"
> If (Request("artist") <> "") Then
> rsResults__A = ReplaceThis(Request("artist"))
> End If
> %>
>
> Then just use rsResults__A when you build your string fir the SQL, which
> will make that code much more readable.
>
> Does GENKTITLES.TITLE contain a " ' "? If it does, then why would you

expect
> a match?
>
> WTF is this all about? AND (GenKTitles.Artist LIKE '%' + '%' + '%') AND
>
> Bob Lehmann
>
>
> "shank" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:%(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > > 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" <(E-Mail Removed)> wrote in message
> > > > news:(E-Mail Removed)...
> > > > > 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" <(E-Mail Removed)> wrote in message
> > > > > news:(E-Mail Removed)...
> > > > > > Thanks to all, but if I can't get past a few syntax issues, I'll

> > never
> > > > get
> > > > > > past creating a function.
> > > > > >
> > > > > > "Steven Burn" <(E-Mail Removed)> wrote in message
> > > > > > news:(E-Mail Removed)...
> > > > > > > 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" <(E-Mail Removed)> wrote in message
> > > > > > > news:(E-Mail Removed)...
> > > > > > > > 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" <(E-Mail Removed)> wrote in message
> > > > > > > > news:%(E-Mail Removed)...
> > > > > > > > > "shank" <(E-Mail Removed)> wrote in message
> > > > > > > > > news:(E-Mail Removed)...
> > > > > > > > > > 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("GenKTitle
> s.
> > > > > > > > > > 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
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >

> >
> >

>
>



 
Reply With Quote
 
 
 
 
shank
Guest
Posts: n/a
 
      08-09-2004
>>But surely, if it's to be an effective search, you don't want a match on
all variations<<
Yes! Absolutely I want to match on all variations! There are too many
variations in the data plus what the user may search upon. I have to replace
those common characters in BOTH the data and criteria.

I can use the function on the IF blocks - no problem. But I still have to
replace the common characters in the data. Hence, the syntax problem.

>>WTF is this all about? AND (GenKTitles.Artist LIKE '%' + '%' + '%') AND<<

These are more fields that can be searched upon to narrow the search
results. Dreamweaver generates the code using the SQL wildcard % and of
course the + signs. Like it or not, it all works well, except for this
Replacing of the common characters.

thanks

"Bob Lehmann" <(E-Mail Removed)> wrote in message
news:Om$(E-Mail Removed)...
> What Steve means is - Why aren't you using it in your IF blocks...
> <%
> Dim rsResults__A
> rsResults__A = "%"
> If (Request("artist") <> "") Then
> rsResults__A = ReplaceThis(Request("artist"))
> End If
> %>
>
> Then just use rsResults__A when you build your string fir the SQL, which
> will make that code much more readable.
>
> Does GENKTITLES.TITLE contain a " ' "? If it does, then why would you

expect
> a match?
>
> WTF is this all about? AND (GenKTitles.Artist LIKE '%' + '%' + '%') AND
>
> Bob Lehmann
>
>
> "shank" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:%(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > > 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" <(E-Mail Removed)> wrote in message
> > > > news:(E-Mail Removed)...
> > > > > 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" <(E-Mail Removed)> wrote in message
> > > > > news:(E-Mail Removed)...
> > > > > > Thanks to all, but if I can't get past a few syntax issues, I'll

> > never
> > > > get
> > > > > > past creating a function.
> > > > > >
> > > > > > "Steven Burn" <(E-Mail Removed)> wrote in message
> > > > > > news:(E-Mail Removed)...
> > > > > > > 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" <(E-Mail Removed)> wrote in message
> > > > > > > news:(E-Mail Removed)...
> > > > > > > > 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" <(E-Mail Removed)> wrote in message
> > > > > > > > news:%(E-Mail Removed)...
> > > > > > > > > "shank" <(E-Mail Removed)> wrote in message
> > > > > > > > > news:(E-Mail Removed)...
> > > > > > > > > > 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("GenKTitle
> s.
> > > > > > > > > > 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
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >

> >
> >

>
>



 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      08-09-2004
shank wrote:
> 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.


Well then you need to create a variable called SQL. (I usually call it sSQL,
but you can do what you want: it's your code) It is good programming
practice to assign the result of string concatenations to a variable so you
can inspect the variable (using Response.Write in server-side code). You can
use the variable in your recordset's Source statment instead of the string
concatenation code:

rsResults.Source = sSQL


However, having said that, I now see that you've response.written the
recordset's Source property, you has given us what I asked for. My mistake.

The other thing you've never told us is what database you are using. If you
are using Access, this

GENKTITLES.TITLE LIKE '%' + 'AINT' + '%') AND

should be this:

GENKTITLES.TITLE LIKE '%' & 'AINT' & '%') AND

I am going to refrain from further analysis until I find out what database
you are using.

I will say that you seem to have an inordinate number of comparisons to '%'.
Do you have any idea what this sql statement should really look like? Have
you used your database's query tool to test the query to make sure it gives
you what you want? This is another good programming practice. You can't run
a query unless you know what the query is supposed to look like.

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"


 
Reply With Quote
 
shank
Guest
Posts: n/a
 
      08-09-2004
I'm using Access 2002. I mentioned that somewhere, but not sure where now.
Yes I ran the query in Access and viewed the query in SQL mode. The query
works fine. The problem comes into play when I try using the Replace on both
sides of the coin: data and criteria. The replace for criteria works well. I
just can't get the syntax for the data side correct.
thanks!

"Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> shank wrote:
> > 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.

>
> Well then you need to create a variable called SQL. (I usually call it

sSQL,
> but you can do what you want: it's your code) It is good programming
> practice to assign the result of string concatenations to a variable so

you
> can inspect the variable (using Response.Write in server-side code). You

can
> use the variable in your recordset's Source statment instead of the string
> concatenation code:
>
> rsResults.Source = sSQL
>
>
> However, having said that, I now see that you've response.written the
> recordset's Source property, you has given us what I asked for. My

mistake.
>
> The other thing you've never told us is what database you are using. If

you
> are using Access, this
>
> GENKTITLES.TITLE LIKE '%' + 'AINT' + '%') AND
>
> should be this:
>
> GENKTITLES.TITLE LIKE '%' & 'AINT' & '%') AND
>
> I am going to refrain from further analysis until I find out what database
> you are using.
>
> I will say that you seem to have an inordinate number of comparisons to

'%'.
> Do you have any idea what this sql statement should really look like? Have
> you used your database's query tool to test the query to make sure it

gives
> you what you want? This is another good programming practice. You can't

run
> a query unless you know what the query is supposed to look like.
>
> 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"
>
>



 
Reply With Quote
 
Aaron [SQL Server MVP]
Guest
Posts: n/a
 
      08-09-2004
> Thanks to all, but if I can't get past a few syntax issues, I'll never get
> past creating a function.


You'll never even get TO creating a function if you insist on using crapware
like DreamWeaver to do your work for you.

Sorry to be blunt, but it's your own reliance on these tools that is causing
the "few syntax issues" in the first place.


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      08-09-2004
What you need to do is use the query tool in Access to create the query with
the Replace function on the data, so you can see what it's supposed to look
like when it works (PS. It is a really bad idea to do this. Your query will
not perform very well if you use pass columns to functions in your WHERE
clause. Also, you cannot use user-defined function in your queries when you
call them via ADO. You must use only built-in VBA functions. So that
ReplaceThis function will not work when you call the query from ASP).

Your task will be made much simpler if, when you get a query that works in
the query tool (you have to use * instead of % in the WHERE clause when you
run the query using the query tool - change the * back to % when you run it
via ADO), you parameterize and save the query, which can more easily be run
from ASP than all this concatenation business. Do a quick Google for posts
by me containing the words "saved parameter query" to see how easy this can
be. You will no longer have to worry about all the stupid "quote" issues.

Bob Barrows

shank wrote:
> I'm using Access 2002. I mentioned that somewhere, but not sure where
> now. Yes I ran the query in Access and viewed the query in SQL mode.
> The query works fine. The problem comes into play when I try using
> the Replace on both sides of the coin: data and criteria. The replace
> for criteria works well. I just can't get the syntax for the data
> side correct.
> thanks!
>
> "Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> shank wrote:
>>> 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.

>>
>> Well then you need to create a variable called SQL. (I usually call
>> it sSQL, but you can do what you want: it's your code) It is good
>> programming practice to assign the result of string concatenations
>> to a variable so you can inspect the variable (using Response.Write
>> in server-side code). You can use the variable in your recordset's
>> Source statment instead of the string concatenation code:
>>
>> rsResults.Source = sSQL
>>
>>
>> However, having said that, I now see that you've response.written the
>> recordset's Source property, you has given us what I asked for. My
>> mistake.
>>
>> The other thing you've never told us is what database you are using.
>> If you are using Access, this
>>
>> GENKTITLES.TITLE LIKE '%' + 'AINT' + '%') AND
>>
>> should be this:
>>
>> GENKTITLES.TITLE LIKE '%' & 'AINT' & '%') AND
>>
>> I am going to refrain from further analysis until I find out what
>> database you are using.
>>
>> I will say that you seem to have an inordinate number of comparisons
>> to '%'. Do you have any idea what this sql statement should really
>> look like? Have you used your database's query tool to test the
>> query to make sure it gives you what you want? This is another good
>> programming practice. You can't run a query unless you know what the
>> query is supposed to look like.
>>
>> 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"


--
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"


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Syntax error (missing operator) in query expression D ASP .Net 4 07-18-2006 10:13 PM
Syntax error (missing operator) in query expression D ASP .Net 0 07-18-2006 08:06 PM
syntax error in query expression in vb.net amitbadgi@gmail.com ASP .Net 1 08-12-2005 06:26 AM
Syntax error in Query Expression, Raphael Gluck ASP General 1 10-13-2003 12:29 PM
syntax error (missing operator) query expression alexz ASP General 0 07-11-2003 05:11 PM



Advertisments