Syntax error (missing operator) in query expression

Discussion in 'ASP General' started by shank, Aug 7, 2004.

  1. shank

    shank Guest

    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(REPLACE(REPLACE("GenKTitles.
    Title","'",""),",",""),".","")," ",""),"the",""),"and",""),"&",""),"/","") &
    "' LIKE '%' '" & T & "' '%' "
     
    shank, Aug 7, 2004
    #1
    1. Advertising

  2. shank

    Bob Lehmann Guest

    What the heck is this....
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE
    all about?

    Bob Lehmann

    "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(REPLACE(REPLACE("GenKTitles.
    > Title","'",""),",",""),".","")," ",""),"the",""),"and",""),"&",""),"/","")

    &
    > "' LIKE '%' '" & T & "' '%' "
    >
    >
     
    Bob Lehmann, Aug 7, 2004
    #2
    1. Advertising

  3. shank

    Anthony Guest

    What on earth are you trying to do? I can't imagine the text your trying to
    replace... post the sample text.. I'd imagine there is a much better way to
    do it...

    I can't wrap my head around that no matter how hard I try...

    sorry...
    "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(REPLACE(REPLACE("GenKTitles.
    > Title","'",""),",",""),".","")," ",""),"the",""),"and",""),"&",""),"/","")

    &
    > "' LIKE '%' '" & T & "' '%' "
    >
    >
     
    Anthony, Aug 7, 2004
    #3
  4. Shank, you need to start with something simple and work from there. They
    don't let the rookie at NASA go up in the space shuttle by himself.

    --
    http://www.aspfaq.com/
    (Reverse address to reply.)





    "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(REPLACE(REPLACE("GenKTitles.
    > Title","'",""),",",""),".","")," ",""),"the",""),"and",""),"&",""),"/","")
    > &
    > "' LIKE '%' '" & T & "' '%' "
    >
    >
     
    Aaron [SQL Server MVP], Aug 7, 2004
    #4
  5. shank wrote:
    > 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(REPLACE(REPLACE("GenKTitles.
    > Title","'",""),",",""),".",""),"
    > ",""),"the",""),"and",""),"&",""),"/","") & "' LIKE '%' '" & T & "'
    > '%' "


    shank, we (you) can't possibly begin to debug a syntax error in a query
    expression without knowing what the actual query expression is. Showing us
    the vbscript code that generates the query expression does not help. You
    need to response.write the expression so we (you) can see that actual
    resulting statement that is generating the syntax error:

    Response.Write SQL
    Response.End

    In addition, we cannot help unless you tell us the type and version of
    database you are using.

    Bob Barrows
    PS, I reiterate: queries should be designed and tested in the database's
    query execution tool BEFORE you attempt to run them via ASP.

    --
    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"
     
    Bob Barrows [MVP], Aug 7, 2004
    #5
  6. "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(REPLACE(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
     
    Chris Hohmann, Aug 7, 2004
    #6
  7. Aaron [SQL Server MVP], Aug 7, 2004
    #7
  8. "Aaron [SQL Server MVP]" <> wrote in message
    news:...
    > > http://aspfaq.com/5000

    >
    > http://www.aspfaq.com/5006
    >


    Yeah, that's what I put first, but I think 5003, 5006 and 5009 were all in
    play. 5003 is a little bit of a stretch. This was more of a "stick to one
    thread situation" or "please provide historical context" situation and not
    so much a "please don't multi-post" one.
     
    Chris Hohmann, Aug 8, 2004
    #8
  9. shank

    shank Guest

    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(REPLACE(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, Aug 8, 2004
    #9
  10. shank

    Bob Lehmann Guest

    >>>It works very well for what I want to do, up until I want to give an
    extra effort to make things better.
    So what you're saying is, it doesn't work very well for what you *really*
    want to do? Maybe it's time to move on?

    That's the problem with automagical code generation tools - they try to
    account for every possible circumstance, write overly-verbose code that
    noone can follow, and make it even worse by appending a proprietary prefix
    to the variables. Look at me, MM_MacroMedia made this! And in the end, they
    don't do that one thing that the developer had in mind.

    What's even worse, is that newbies attempt to do things they don't
    understand, and end up trying to fix the thing don't understand by wading
    through the incomprehensible code generated by the <not>helpful</not> tool.

    From the code you provided, you are doing lots of LIKEs without a wildcard
    when a value is entered. So you would end up with something like this...
    AND (GenKStock.SingleArtist LIKE 'jo') in your query if someone typed 'jo'
    in your search form.

    Suggestions -
    You should specify the collection in the Request object you are using.
    Request.Form("field_name"), Request.QueryString("field_name"), etc....
    Your test for "" is unreliable. You should check the length of the
    string or...

    If Not Request.Form ("title") = "" Then
    .....
    End If

    For concatenation, "&" is the correct operator in VB - "+" can cause
    some unexpected esults if the value is number.
    Consider doing the Replace in your If staements to make the SQL more
    readable in the code.
    Also, write a function to do the replacing so you don't have to keep
    writing - Replace(rsResults__TY, "'", "''") - over and over.

    Bob Barrows's advice to response.write the query to make sure it works in
    Access, and Aaron's advice to start simple, are probably the best two pieces
    of advice should follow.

    Bob Lehmann

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

    >
    >
     
    Bob Lehmann, Aug 8, 2004
    #10
  11. shank

    Steven Burn Guest

    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(REPLACE(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
    > >
    > >

    >
    >
     
    Steven Burn, Aug 8, 2004
    #11
  12. shank

    shank Guest

    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(REPLACE(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, Aug 8, 2004
    #12
  13. shank

    Steven Burn Guest

    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(REPLACE(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
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >
     
    Steven Burn, Aug 8, 2004
    #13
  14. shank

    shank Guest

    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(REPLACE(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, Aug 9, 2004
    #14
  15. 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"
     
    Bob Barrows [MVP], Aug 9, 2004
    #15
  16. shank

    Steven Burn Guest

    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(REPLACE(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
    > > > > > >
    > > > > > >
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >
     
    Steven Burn, Aug 9, 2004
    #16
  17. shank

    shank Guest

    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"
    >
    >
     
    shank, Aug 9, 2004
    #17
  18. shank

    shank Guest

    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(REPLACE(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, Aug 9, 2004
    #18
  19. shank

    Steven Burn Guest

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

    >
    >
     
    Steven Burn, Aug 9, 2004
    #19
  20. shank

    Steven Burn Guest

    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(REPLACE(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
    > > > > > > > >
    > > > > > > > >
    > > > > > > >
    > > > > > > >
    > > > > > >
    > > > > > >
    > > > > >
    > > > > >
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >
     
    Steven Burn, Aug 9, 2004
    #20
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. D
    Replies:
    4
    Views:
    2,287
  2. D
    Replies:
    0
    Views:
    2,240
  3. alexz
    Replies:
    0
    Views:
    118
    alexz
    Jul 11, 2003
  4. Maciej Paras
    Replies:
    1
    Views:
    160
    Bob Barrows
    Oct 30, 2003
  5. Replies:
    4
    Views:
    203
    Bob Barrows
    Sep 30, 2005
Loading...

Share This Page