Multiple list selection handing 2

[ierax]

hi all,

the user selects from a list box in a html form in originating page.
I use his selection as a parameter in my target asp page,
in order to execute a MS Access 2000 query.
i.e when the user chooses "Rblt1" from the list box the variable "varRblt" gets the value "Rblt1" and the query

sqltext="SELECT * FROM DBname WHERE RBLT='"& varRblt&"';" works fine.

When the user makes multiple selections (i.e Rblt1 and Rblt2)from the list box,the variable varRblt gets the value
"Rblt1,Rblt2" and the query does NOT work.

How should i write sqltext in order to work with single and multiple selections?

Thanks in advance
ierax

 

[vasah20]

The list box is a collection, so you can use that to your advantage.

dim item, strWhere
strWhere = " WHERE "
for each item in Request.Form("mylistbox")
 strWhere = strWhere & "( RBLT = '" &item& "') AND "
next
 strWhere = Left(strWhere, Len(strWhere) - 5)

this code will iterate through the list box collection, then create the appropriate where clause. the last line hacks off the last AND so that you don't have any syntax errors. So then to use it you would do something like

RS.Open "SELECT * FROM mytable " &strWhere

of course, you can always adapt this to what you need, but you get the idea.

This also works for multiple checkboxes as well.

hope this helps.
leo

 

[ierax]

thanks for your answer
but i'm sad to say that the code still does not work with multiple selections.
the line
objRS1.MoveFirst
produces the error
"Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record"

 

[Mighty]

Try print out the SQl statement to the screen to verify that the syntax is correct. If it is OK, is there definitely code in the database to match the query.

Try the following:

sqltext="SELECT * FROM DBname WHERE RBLT IN ("
for each item in request.form("myListBox&quot
sqltext = sqltext & "'" & item & "', "
next
sqltext = Left(sqltext, Len(sqltext) - 2)
sqltext = sqltext & &quot"

This should give you an SQL command of the form:

SELECT * FROM DBname WHERE RBLT IN ('Rblt1', 'Rblt2')

Mise Le Meas,

Mighty

 

[ierax]

thanks mighty,
At last,the script works for both single and multiple selection.
But,it does not work without a selection.
See i have two list boxes as elements in the same html form,
and the user could choose to select values from one box ang to ignore the other box.
in this case i get the error
"In operator without () in query expression 'RBLT IN)'".
when the RBLT box is the ignored one.

 

[vasah20]

What I would do is change the strWhere clause I wrote above to start with RBLT IN (, then check to make sure the collection isn't empty.

ex:

dim item, strWhere

if Request.Form(&quot;mylistbox&quot;) <> &quot;&quot; then
 strWhere = &quot; RBLT IN(&quot;
 for each item in Request.Form(&quot;mylistbox&quot;)
  strWhere = strWhere & &quot;( RBLT = '&quot; &item& &quot;') AND &quot;
 next
  strWhere = Left(strWhere, Len(strWhere) - 5) & &quot;)&quot;
else
 strWhere = &quot;&quot;
end if

The above should work, since if nothing is found, strWhere becomes empty. So it won't throw it into your SQLString. If it has any value, though, it'll drop into the loop and create the appropriate clause.

all that's left to do is attach it unto your SQLString
ex:

dim sqlStr
sqlStr = "SELECT * FROM table WHERE fieldA = 1 " & strWhere

Response.write this sqlStr above, though, just to make sure that everything is working as it should.

hope this solves your problem -
leo