how to nest loop in sql statement

[frogggg]

I have a sql statement which brings up all records which relate to criteria set on a page before in session variables. One of the criteria needs to be multiple select, so I have the following array to get all the values from the listbox and put them in a session variable:

'allow for multiple select
dim intCount
'dynamic array
dim arraySkills()

'for each selected element in listbox
for intCount=0 to Request.Form.Item("lstSkills&quot.count - 1
'dynamically increase array size but preserve contents
redim preserve arraySkills(intCount)
'populate array
arraySkills(intCount) = Request.Form.Item("lstSkills&quot(intCount + 1)
next

'put array in session variable
session("SearchSkills&quot = arraySkills

This works fine.

The question is how to use this info in a where clause of my sql statement. Usually I would retrieve the contents of the array, element by element, like this:

for intCount = 0 to ubound(session.contents("SearchSkills&quot)
response.write session.contents("SearchSkills&quot(intCount)
next

How on earth would I get this information in a sql statement?

Any help would be greatly appreciated!

 

[cmmrfrds]

You can use the IN keyword. The SQL Statement would look like this example from SQL Server. Notice the example uses a char type field since it has quotes around the value. It might be better to build a comma delimited string instead since that would more closely match the IN list.

SELECT pub_name
FROM publishers
WHERE pub_id in ('1389', '0736')

Put something like this in the loop but remove the comma on the last occurs.
Dim str
str = str & Request.Form.Item("lstSkills&quot(intCount + 1) & ", "

 

[frogggg]

I'm not sure I understand - what does the keyword in mean, and what are the values you have of in? Can I put the array name right there, or do I have to fish out the values and put them in a variable one by one?

Thanks for your help.

 

[cmmrfrds]

The SQL keyword IN, says to retrieve the records that match the IN list. You would need to take the values out of the array as it is a comma delimited list, that is why it is probably easier to build a comma delimited list in your loop where you are now filling the array. This way all the values are in a variable that can be used when building the SQL string.

 

[frogggg]

Thanks!
Now I have the strangest problem:
I'm getting Subscript out of range: 'ubound' error
This is the identical code for many other listboxes that I have on my pages. Here is my code - do you see the problem?

dim intCount
'dynamic array
dim arraySkills()

'for each selected element in listbox
for intCount=0 to Request.Form.Item("lstSkills&quot.count - 1
'dynamically increase array size but preserve contents
redim preserve arraySkills(intCount)
'populate array
arraySkills(intCount) = Request.Form.Item("lstSkills&quot(intCount + 1)
next

'put array in session variable
session.Contents("SearchSkills&quot = arraySkills

That was on the first page. This is on the second page to get the values:
dim intCount
dim skills

for intCount = 0 to ubound(session.Contents("SearchSkills&quot)

skills = skills & session.Contents("SearchSkills&quot(intCount) & ","
next


Also, how do I get rid of the comma on the last run of the loop?

Thanks again!