Handling recordsets and multiple selection lists

Filtering records using dynamically generated checkboxes or Multiple selection lists

There is an excellent extension and a good tutorial available for deleting and updating records using checkboxes and multiple slection lists. This is based on tweaking the command-options in Dreamweaver. But when you want to use dynamically generated checkboxes or multiple selection lists to filter records, you are virtually on your own. An integrated solution for Dreamweaver is high on my whishlist, but untill then we have to do some handcoding.

The code below is simplified for clarification. There are some more issues to this. It now uses an OR-operator which is not allways the most elegant way to retrieve data from the DB. In a next article i will cover the use of the IN operator in SQL and Dreamweaver, which does a better job. Trying to use the IN-Operator with Dreamweaver can be a very "interesting experience" since MM forgot about this very handy and common option in SQL as well.

How to handle multiple selects from a list or checkboxes?

Selection lists and checkboxes are about as handy as they come. They allow visitiors to easily select the options they want. But for a Dreamweaver developer this can be a real torture to implement. Of course you have no problem if you can give every checkbox a unique name. Then it is just a matter of letting the querystring do it's thing and create a standard select query on the next page. But, if it is an multiple select list called "cities" the querystring will look something like this: "search.asp?cities=Amsterdam, London, Zurich, New York". The same goes for dynamically generated checkboxes. Dreamweaver doesn't like it when you do something like this.

I have been in this hell for about a week. Posted messages on all developerboards i knew, but nothing helped. Appearantly this was a very difficult question. So after i figured out a way to work around the problem, i decided to post it an an tutorial

I had to create search pages for a large multinational commercial real estate company. On the first page people would select their type of building (office/industrial/etc), the desired region and the number of square meters they were looking for. These results are passed to the second page. This page holds a recordset to retrieve all valid results from the database. It then dynamically renders a list of cities in which real estate of the right kind, size and region are available.

Every checkbox therefor has the same name; "ReCity". No problem, but my client wants to give visitors the option to select multiple cities.

That means that the resultspage will get the data as a comma delimited querystring. That's where the trouble starts. Suppose you create a query looking like "Select * from tblRE where ReCity is bla bla MM_ColParam bla bla Querystring bla bla. Dreamweaver will then ask the database to retrieve all records where ReCity is 'Amsterdam, London, Zurich, New York'. Mind the single quotes here. Dreamweaver will add them automatically. Of course there is no such record to be found.

What you need is a statement that will retrieve all records that are Amsterdam OR London OR Zurich OR New York. So we first have to split the comma delimited string into seperate values, and store them in an array. Then you have to write a line that will replace the result by something more usefull. The next few lines of code will take care of that.

<%
FCities = Request.form("ReCity") ' Convert the info from the form to a string: Amsterdam, Schiphol, ... etc.
MakeCity = Replace(FCities, ", ", "' OR Recity= '") ' Replace comma-space to single quote - OR Recity= - single quote
MijnSQL = "SELECT RECity FROM tblNLRE WHERE ReCity = '" & MakeCity & "' "
%>

What happens here is that the first line takes the posted form data ( i prefer this over sending the stuff by querystring for obvious reasons) and creates an array.
The second line replaces the comma-spaces with something better like OR ReCity=. In the third line i create a SQL statement to retrieve the records.

Why do i do this? If you try to achieve the right result using the standard Dreamweaver or Ultradev tools, you will find it is getting you nowhere.
You just can not do this in Dreamweaver. Simple as that.

I then used the SB palette to create a recordset called rsCats. Look at the fourth line: "rsCats.Source = MijnSQL".

<%
'My recordset
set rsCats = Server.CreateObject("ADODB.Recordset")
rsCats.ActiveConnection = MM_cnnjll_STRING
rsCats.Source = MijnSQL
rsCats.CursorType = 0
rsCats.CursorLocation = 2
rsCats.LockType = 3
rsCats.Open()
rsCats_numRows = 0
%>

And voila! It works like a charm. But there is a disadvantage. You will not be able to find this recordset in your server behaviour palette anymore. Dreamweaver does not recognise it at all. You don't even get a red exclamation mark. So make sure this is the last step you take while developing your page. Implement all corresponding server behaviours first and test them before you do this. It is also a good idea to save a backup-copy of this page before you you implement this solution. If you ever have to work on the page in future, you will not be on your own. (Code provided in this tutorial is simplyfied for clarification)