Using "Like" or Wildcard

  • I am searching against a sql database and I want to return all active courses and all courses whose field code begins with a 10. This is the table (LEDef) & some of the info that is in the field.

    LEDef_Cd

    DB356

    40ILTC0007

    HW442

    51NWAC000020

    10-CSSR-2013

    10-FESW-0002

    I want to return only the "10-" stuff. Here is part of the code I'm using. I've tried everything but nothing seems to work:

    r.open "SELECT LEDef_PK FROM LEDef WHERE LEDef_ActiveInd = 1 and LEDef_Cd Like '%10%'"

  • Try:

    
    
    SELECT LEDef_PK
    FROM LEDef
    WHERE LEDef_ActiveInd = 1
    AND LEDef_Cd LIKE '10-%'
  • I've already tried this but I tried it again and it didn't work:

    r.open "SELECT LEDef_PK FROM LEDef WHERE LEDef_ActiveInd = 1 AND LEDef_Cd LIKE '10-%'"

  • The SQL is correct. Perhaps you could post an error message or the VB/ASP code you are using?

  • I'm not getting an error message, it's just not returning the correct results. This is the code from my search page:

    
    
    <%

    'FOLLOWING SESSION VARIABLES OBTAINED FROM GLOBAL.ASA

    ' CHANGE THE FOLLOWING CATALOGS AS NEEDED
    dbCatalog = Session("dbCatalog")
    siteCatalog = Session("siteCatalog")

    ' URL FOR COURSE DATABASE, FOR LINKING TO SPECIFIC RESULT
    ' see global.asa
    DatabaseURL = Session("CourseDBURL")


    ' MODIFY querystring values for use here by both search routines
    if Trim(Request("qu")) <> "" then
    'for database search, want to search ALL searchable columns
    'adding @all to the search string accomplishes this
    strQu = "@all " & Trim(Request("qu"))
    strRQu = Trim(Request("qu"))
    else
    strRQu = ""
    end if

    strRQS = Request.QueryString
    if len(strRQS) > 0 then
    strRQS = Replace(strRQS,"%A0"," ")
    strRQS = Replace(strRQS,"%26nbsp%3B","")
    else
    strRQS = ""
    end if
    if Trim(strRQS) <> "" then
    strL = Left(strRQS,3)

    strR = Trim(Right(Trim(strRQS),len(Trim(strRQS)) - 3))
    'for database search, want to search ALL searchable columns
    strQS = strL & "@all+" & Trim(strR)
    strQS = Replace(strQS, " &", "&")
    else
    strQS = ""
    end if

    'set the number of search results to display, as selected by user; default is 25
    if Request("num") <> "" then
    maxRecs = CInt(Request("num"))
    else
    maxRecs = 25
    end if

    %>
  • Could you do a quick Response.Write on your querystring and post the results? Are you absolutely positive that the string being passed to the Recordset.Open method is the same as the string you posted? If the query you posted functions correctly in query analyzer, then it is a problem with the string passed to the Open method...

  • Which string do you want me to do a Response.Write on?

  • I did some Response.Writes and got the following:

    Trim(Request('qu')): customer
    


    ===================================================================

    Request.QueryString: qu=customer&Search.x=27&Search.y=10

    replace %A0 strRQS: qu=customer&Search.x=27&Search.y=10

    qu=customer&Search.x=27&Search.y=10

    replace %26nbsp%3B strRQS: qu=customer&Search.x=27&Search.y=10

    strL: qu=

    strR: customer&Search.x=27&Search.y=10

    strL & @all+ & strR: qu=@all+customer&Search.x=27&Search.y=10

  • I'm not quite sure what you are doing with these statemeents, but it almost seems as if you are trying to use the ampersand symbol in place of the AND keyword? Is this correct? I'm having a little trouble seeing how your posted querystring relates to your original posted question on LIKE keyword...

    If possible, please elaborate.

    Thanks,

    Jay

  • In addition to pulling all courses that are active, I would also like to pull all courses from the LEDef_Cd field that begins with the number 10.

  • Wanda,

    I think you have been using an incorrect SQL.

    Use OR instead of AND in your SQL and try again.

    (LEDef_ActiveInd = 1 OR LEDef_Cd Like '10-%)

    Ram

  • I want to do both not either or...

  • By using the OR clause, you will get both. The OR clause returns all records that match either criteria (ie.. The course is active or the course name starts with '10'). The AND clause will only return those records that meet both criteria (ie.. the course is active and the course name starts with '10').

  • DAVNovak,

    Thanks for explaining it in more words.

    To add a bit of humor:

    In other words, the SQL logic is inverted by 180 degrees from the normal English query.

    (If Wanda requires AND, he has to use OR)

    Wanda, please post a message when you solve this problem.

    Ram

  • I'm sorry, but Wanda wants to limit the query based on BOTH criteria, not just one or the other. AND is the correct expression. The problem is in how the sql command string is being generated in the ASP script.

Viewing 15 posts - 1 through 15 (of 35 total)

You must be logged in to reply to this topic. Login to reply