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)
Comments
Thank You!!!!
This save my Life!
easy way to still see recordset
If you want to still see the recordset in your SB palette.., instead of defining the SQL as a variable and adding it to your recordset (i.e:
MijnSQL = "SELECT RECity FROM tblNLRE WHERE ReCity = '" & MakeCity & "' "
then in recordset
rsCats.Source = MijnSQL
Instead just put the SQL code direct in your recordset:
rsCats.Source = "SELECT RECity FROM tblNLRE WHERE ReCity = '" & MakeCity & "' "
You can then still see the recordset
You must me logged in to write a comment.