June 6, 2003 at 12:06 pm
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
Try:
SELECT LEDef_PK
FROM LEDef
WHERE LEDef_ActiveInd = 1
AND LEDef_Cd LIKE '10-%'
June 6, 2003 at 12:46 pm
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
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
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
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
Which string do you want me to do a Response.Write on?
June 6, 2003 at 2:29 pm
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
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
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
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
I want to do both not either or...
June 9, 2003 at 10:21 am
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
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
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