Post reply

Using "Like" or Wildcard

  • June 6, 2003 at 12:06 pm

    #82121

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

  • June 6, 2003 at 12:20 pm

    #460246

    Try:

    

    SELECT LEDef_PK 

    FROM LEDef 

    WHERE LEDef_ActiveInd = 1 

    AND LEDef_Cd LIKE '10-%'

  • June 6, 2003 at 12:46 pm

    #460252

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

  • June 6, 2003 at 12:49 pm

    #460253

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

  • June 6, 2003 at 1:22 pm

    #460255

    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

    

    %>

  • June 6, 2003 at 1:33 pm

    #460259

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

  • June 6, 2003 at 2:23 pm

    #460262

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

  • June 6, 2003 at 2:29 pm

    #460264

    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

  • June 9, 2003 at 5:53 am

    #460338

    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

  • June 9, 2003 at 9:29 am

    #460395

    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.

  • June 9, 2003 at 9:47 am

    #460402

    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

  • June 9, 2003 at 9:49 am

    #460403

    I want to do both not either or...

  • June 9, 2003 at 10:21 am

    #460411

    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').

  • June 9, 2003 at 10:53 am

    #460418

    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

  • June 9, 2003 at 11:11 am

    #460420

    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