HairJam
asked on
Classic ASP array and SQL SELECT question
Hi there,
I have a record in my database which is comma delimeted, an example might be "orange, apple, pear" which is the result of a user selecting three items from a multi-select box or three checkboxes of the same name but with different values.
I have a form at the front-end containing a <select multiple> box containing a list of different fruits (as an example) which the User can select multiple items from to search. So when a user submits the form the Response.Form("fruit_type"
I'm trying to then construct a SQL SELECT statement to interogate my database which would have the following structure:
strSQL = "SELECT * FROM tblFruit WHERE fruit_type LIKE '%orange%' AND fruit_type LIKE '%pear%' AND fruit_type LIKE '%strawberry%' AND fruit_type LIKE '%lemon%' ORDER BY id"
The idea being that my record in the database which contains "orange, apple, pear" will be picked up because it contains one or more matching results.
Fundamentally my thought process would suggest I needed to put the form value "orange, pear, strawberry, lemon" into an array which is split at the comma (,) and then construct my SQL SELECT statement using a for...next loop.
I can't seem to figure out, however, how to actually code this up.
Can anyone help?
I have a record in my database which is comma delimeted, an example might be "orange, apple, pear" which is the result of a user selecting three items from a multi-select box or three checkboxes of the same name but with different values.
I have a form at the front-end containing a <select multiple> box containing a list of different fruits (as an example) which the User can select multiple items from to search. So when a user submits the form the Response.Form("fruit_type"
I'm trying to then construct a SQL SELECT statement to interogate my database which would have the following structure:
strSQL = "SELECT * FROM tblFruit WHERE fruit_type LIKE '%orange%' AND fruit_type LIKE '%pear%' AND fruit_type LIKE '%strawberry%' AND fruit_type LIKE '%lemon%' ORDER BY id"
The idea being that my record in the database which contains "orange, apple, pear" will be picked up because it contains one or more matching results.
Fundamentally my thought process would suggest I needed to put the form value "orange, pear, strawberry, lemon" into an array which is split at the comma (,) and then construct my SQL SELECT statement using a for...next loop.
I can't seem to figure out, however, how to actually code this up.
Can anyone help?
Last Comment
Guy Hengel [angelIII / a3]
that should be OR and not AND...
anyhow:
strList = "orange,pear,strawberry,lemon"
arrList = split(strList, ",")
strSQL = "SELECT * FROM tblFruit WHERE ',' + fruit_type ',' LIKE '%," & join(arrList, ",%' OR ',' + fruit_type ',' LIKE '%," ) & ",%' ORDER BY id"
ASKER
Hmm - I'm getting an error here:
Line 1: Incorrect syntax near ','.
Here's the code I'm using:
Front-end select box...
<select name="technologies" multiple>
<option value="all" selected>All</option>
<option value=".Net">.Net</option>
<option value="Active Directory">Active Directory</option>
<option value="BizTalk">BizTalk</o
<option value="CRM">CRM</option>
<option value="Live Communications Server">Live Communications Server</option>
<option value="Office">Office</opt
</select>
ASP...
strFormTechnology = Request.Form("technologies
ListArray = Split(strFormTechnology,",
strSQL = "SELECT * FROM tblTechnology WHERE ',' + ma_technology ',' LIKE '%," & join(ListArray, ",%' OR ',' + ma_technology ',' LIKE '%," ) & ",%' ORDER BY id"
Line 1: Incorrect syntax near ','.
Here's the code I'm using:
Front-end select box...
<select name="technologies" multiple>
<option value="all" selected>All</option>
<option value=".Net">.Net</option>
<option value="Active Directory">Active Directory</option>
<option value="BizTalk">BizTalk</o
<option value="CRM">CRM</option>
<option value="Live Communications Server">Live Communications Server</option>
<option value="Office">Office</opt
</select>
ASP...
strFormTechnology = Request.Form("technologies
ListArray = Split(strFormTechnology,",
strSQL = "SELECT * FROM tblTechnology WHERE ',' + ma_technology ',' LIKE '%," & join(ListArray, ",%' OR ',' + ma_technology ',' LIKE '%," ) & ",%' ORDER BY id"
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
I'm using SQL Server 2000 for this particular project. :-)
ASKER
Many thanks for your help. It is working very well now. If you think using & instead of + would be better in the case of SQL Server 2000 just let me know, but certainly it is working perfectly at the moment as is. Thanks again!
& would have been for Ms Access :)
+ is just fine for ms sql server.
glad I could help
+ is just fine for ms sql server.
glad I could help
ASP
Active Server Pages (ASP) is Microsoft’s first server-side engine for dynamic web pages. ASP’s support of the Component Object Model (COM) enables it to access and use compiled libraries such as DLLs. It has been superseded by ASP.NET, but will be supported by Internet Information Services (IIS) through at least 2022.
82K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
