displaying results from a DB

[Johnmitchell]

first off, i am almost 100% positive that the error is going to be something small, likely a wrong case char or something of that nature, but i can't spot it!it's just a small search form which is for now, only searching one table in the database.

You searched for: Organisation; . Area; . Keywords; mentalSQL Statement:select * from Organisation where (orgname like '%%' or AlternativeNames like '%%') and keywords like '%mental%' and (addtown like '%%' or addcounty like '%%' or addr_postcode like '%%') ORDER BY orgName ASC

this SQL statement, when run through the database returns 7 results, but when i use my form nothing is being written out.for security reasons i have edited the sql connect string. oh and i have just noticed that this has been word wrapped.

<div><form action="index.asp" method="post"><table id="searcharea">	<tr>		<td><strong>Oranisation: </strong><Br /><input type="text" name="orgSearch" /></td><td> </td>	</tr>	<tr>		<td style="text-align: right;"><strong>Area: </strong></td><td><input type="text" name="Location" ></td><td><input type="submit" Value="Search" /></td>	</tr>	<tr>		<td><strong>Keywords: </strong><br /><input type="text" name="Keywords" /></td><td> </td>	</tr></table><%'pull in the search from the formorgSearch = request.Form ("orgSearch")location = Request.Form ("Location")keywords = Request.Form ("Keywords")'write to the page what you searched for:response.write ("<strong>You searched for: </strong> Organisation; " & orgSearch & ". Area; " & Location & ". Keywords; " & Keywords)'write to the page the SQL statement:	response.write("<p><strong>SQL Statement:</strong><br />" & strSQL & "</p>")'select all from the table 'organisation' where orgname is like {wildcard} and whatever was in the textbox 'orgSearch' or alternative names like it, and keywords that are like what was filled in the keywords text box etcstrSQL = "select * from Organisation " & _"where (orgname like '%" & orgSearch & "%' or AlternativeNames like '%" & orgSearch & "%') " & _"and keywords like '%" & keywords & "%' " & _"and (addtown like '%" & location & "%' or addcounty like '%" & location & "%' or addr_postcode like '%" & location & "%') "	'order results by orgname in ascending order.	strSQL = strSQL & "ORDER BY orgName ASC"		'declare the SQL Connection.	set objcmd = server.createobject("adodb.command")	objcmd.activeconnection = "Provider=gsefrv;Database=sfrdv;Server=fsvd;trusted_connection=yes"	objcmd.commandtext = strSQL 	response.write strSQL	set objRS = objcmd.execute 	'write out the address of each company: 	If not objRS.EOF Then			objRS.moveFirst		while not objRS.eof					response.write("<address>")			response.write("Name = " & objRS("orgName") & "<br />")						' Dwelling			if not isNull(objRS("AddDwelling")) and trim(objRS("AddDwelling")) <> "" then 				response.write("Dwelling = " & objRS("AddDwelling") & "<br />")			end if						' Street			if not isNull(objRS("AddStreet")) and trim(objRS("AddStreet")) <> "" then 				response.write("Street = " & objRS("AddStreet") & "<br />")			end if				' Locality			if not isNull(objRS("AddLocality")) and trim(objRS("AddLocality")) <> "" then 				response.write("Locality = " & objRS("AddLocality") & "<br />")			end if				' Town			if not isNull(objRS("AddTown")) and trim(objRS("AddTown")) <> "" then 			response.write("Town = " & objRS("AddTown") & "<br />")			end if						' County			if not isNull(objRS("AddCounty")) and trim(objRS("AddCounty")) <> "" then 				response.write("County = " & objRS("AddCounty") & "<br />")			end if						' Postcode			if not isNull(objRS("Addr_Postcode")) and trim(objRS("Addr_Postcode")) <> "" then 				response.write("Postcode = " & objRS("Addr_Postcode") & "<br />")			end if						' Phone number			if not isNull(objRS("PhoneNo")) and trim(objRS("PhoneNo")) <> "" then 				response.write("Telephone = " & objRS("PhoneNo") & "<br />")			end if						' Fax number			if not isNull(objRS("FaxNo")) and trim(objRS("FaxNo")) <> "" then 				response.write("Fax = " & objRS("FaxNo") & "<br />")			end if						' Web address (includes checking for "http://" at the start of the address			if not isNull(objRS("WebAddress")) and trim(objRS("WebAddress")) <> "" then				if lcase(left(objRS("WebAddress"),7)) = "http://" then					response.write("<a href=""" & objRS("WebAddress") & """ target=""_blank"">" & objRS("WebAddress") & "</a><br />")				else					response.write("<a href=""http://" & objRS("WebAddress") & """ target=""_blank"">http://" & objRS("WebAddress") & "</a><br />")				end if			end if			response.write("</address>")			response.write("<br/>-------------------------------------------<br/>")			objRS.MoveNext		wend		End if		objRS.Close	set objRS = nothing%></form></div>

Thank you if you can help!

[justsomeguy]

The original SQL statement has a bunch of things that return all rows. The "like '%%'" returns every row. So this:select * from Organisation where (orgname like '%%' or AlternativeNames like '%%') and keywords like '%mental%' and (addtown like '%%' or addcounty like '%%' or addr_postcode like '%%') ORDER BY orgName ASCis the same as this:select * from Organisation where keywords like '%mental%' ORDER BY orgName ASCIn your other statement you add a bunch of other conditions, so it's not going to return the same set.

[Johnmitchell]

but like i said, when i run

select * from Organisation where (orgname like '%%' or AlternativeNames like '%%') and keywords like '%mental%' and (addtown like '%%' or addcounty like '%%' or addr_postcode like '%%') ORDER BY orgName ASC

through the Database it returns several resultsall im doing in the second statement is, instead of definately defining that the 'and keywords like' result to be %mental%, it is what ever is in the keywords text field of the form. so if they searched for mental the sql statement would say 'and keywords like %mental%'if they searched for psychotic then it would say 'and keywords like %psychotic%'the first quote was just what i had searched for in the text box's it was just spaffing it out with the sql aswell.

[Synook]

Well, that query will return any row where the keywords column had the word "mental" inside it, so things like "hello, mental, test" and "a, mental, person" would test true as well as just "mental".

[Johnmitchell]

exactly my point, and it has returned 7 results, but when i run it via filling in my form, nothing is displayed where it should be. hence me thinking its my ASP that's wrong...

[justsomeguy]

I think you misunderstood me.You started with this query:select * from Organisation where (orgname like '%%' or AlternativeNames like '%%') and keywords like '%mental%' and (addtown like '%%' or addcounty like '%%' or addr_postcode like '%%') ORDER BY orgName ASCThis query is the exact same thing as this:select * from Organisation where keywords like '%mental%' ORDER BY orgName ASCTry it, you'll see it returns the same set. The second query you have looks like this:select * from Organisation where (orgname like '%orgSearch%' or AlternativeNames like '%orgSearch%') and keywords like '%keywords%' and (addtown like '%location%' or addcounty like '%location%' or addr_postcode like '%location%')So obviously these two queries are not equal:

select * from Organisation where keywords like '%mental%' ORDER BY orgName ASC

select * from Organisation where (orgname like '%orgSearch%' or AlternativeNames like '%orgSearch%') and keywords like '%keywords%' and (addtown like '%location%' or addcounty like '%location%' or addr_postcode like '%location%')

You're adding a bunch of conditions in the second one that are not in the first one, that is why the second one is more limited then the first one is.

[Johnmitchell]

no i understand you. but the only reason that the first statement doesn't have the same bunch of conditions is the fact that the 'orgSearch' text box was empty when i ran the ASP as was the 'location' text box.this is the actuall sql inside the page

strSQL = "select * from Organisation " & _"where (orgname like '%" & orgSearch & "%' or AlternativeNames like '%" & orgSearch & "%') " & _"and keywords like '%" & keywords & "%' " & _"and (addtown like '%" & location & "%' or addcounty like '%" & location & "%' or addr_postcode like '%" & location & "%') "

and this is just what happens (what it spits out to the page) when i tell it to write out the strSQL variable:

select * from Organisation where (orgname like '%%' or AlternativeNames like '%%') and keywords like '%%' and (addtown like '%mental%' or addcounty like '%mental%' or addr_postcode like '%mental%') ORDER BY orgName ASC

i have three text box's labled orgSearch location and keywords. i have left orgSearch empty and i have left location empty, but i have filled keywords with mental. that is why the two differ. sorry it was my fault i didn't explain that the first sql string was just a quote from my web page after the query was ran. it would return it as 'orgname like '%%' ' if i left the text box empty.

[justsomeguy]

OK - but if you fill out the other boxes obviously the results are going to be limited. I thought your original question was asking why they are limited in one case and not the other.

[Johnmitchell]

yeah, i didn't really word it that well i guess.the whole point of the search is to limit the results as much as possible, so as to narrow down the amount of organisations that our clients find, depending on what they need.as it was, our database lady hadn't told me that during testing i was to point to another database. Doh!