Hi all,
I have a database for insect species. I have set up a search box which a user can enter a keyword and it pull out any info from the database containing that word. I have two problems. Firstly Everything works ok, but I have just noticed that if no entry is place in the text box and the search button is pressed then everything from the database is displayed. Is there anyway to correct this. I don't want the users to have to type in the full name to search but a small part of a name. Secondly, I have added a list box which has families listed in it. I want to allow a user to be able to also search the database by family. I have created a second recordset for this and placed the listbox along side the textbox within the same form. Is this correct or do I need a seperate form to do this?
Hope you can help
Thanks
Simon

Hi Simon,

For your 1st problem the SQL should pull records that are LIKE the value in the textbox and use the % symbol as a wildcard - try this link for details www.techonthenet.com/sql/like.htm
Your problem might be to do with how you've constructed your LIKE clause i.e. it could be returning anything with a space in it; if you post your select statement maybe someone can spot what's going on there.

Second problem:
Its probably easier to have 2 forms, each submitting to a nearly identical page - everything but the recordset would be the same. However that's not very elegant and means you've got 2 copies of 1 page but with 1 change in the SQL... not good from a reuse of code perspective!
The alternative would be to set some conditional logic on results.asp that checks for the presence of a value from the textbox and if there is a value then it runs the select that filters based on that value; else it runs another select that filters based on the value in the combo box.

Live the life you love
Love the life you live

Simon

[DWMX 2004]|[SQL]|[ASP/VBScript]|[XP-Pro]

Thanks for the reply simon,
Here is my like statement which dreamweaver has generated. Not sure whether you can see an improvement.

SELECT ID, BF, Vernacular, Taxon
FROM species
WHERE Vernacular LIKE '%colname%'
ORDER BY Vernacular ASC

I think it is picking up a space, so if you can help me then that would be great.
Thanks
Simon

That select should pick up anything where the value entered is found anywhere in the column Vernacular. If you enter a partial term does it filter the results correctly e.g. enter "Lep" would it pick up "Lepidoptera" but not "Coleoptera" or "Hemiptera"?
If so then the select itself would appear to be working correctly. Which would make me think that its maybe matching blank spaces in your names???

Live the life you love
Love the life you live

Simon

[DWMX 2004]|[SQL]|[ASP/VBScript]|[XP-Pro]

Thanks for the reply Simon,
The search does pick up partial keywords, so I think your right that its picking up the spaces in the names. Is there anything that I can add to the script to stop this from happening?
Thanks
Simon

Either ensure there are no spaces in the text or remove the initial wildcard so you are searching where the word begins with ....
Otherwise I think you'll have to do some funky string manipulation to get rid of the blank spaces and then do the LIKE to see if there is a match

Live the life you love
Love the life you live

Simon

[DWMX 2004]|[SQL]|[ASP/VBScript]|[XP-Pro]