Using the IN Operator to select multiple records

As an extra to my tutorial on filtering records based on the result of a multiple select list or a group of identical named checkboxes, here is a tutorial of retrieving your records in a more efficient way. Once you have got your page working when you use the OR-operator in SQL, you might want to try this. Because this one uses the must faster IN Operator in SQL.

The idea of checkboxes or a multiple select list is that no matter if the user selects one option or twelve, your resultpage returns one or twelve records. Although this sounds simple and logical, this can be a daunting task in Dreamweaver. Thanks to the "wishdom" of MM who keeps forgetting about these common SQL options.
It costed me a long time to sort this solution out.

Ok, let's start. You will see in the end that is not at all very complicated once you know how to do it.

On page one you will have a multiple select list or, as in my case, dynamically generated checkboxes. The checkboxes are based on a recordset that presents a list of Cities in which certain objects can be found. In this case we are talking about searchpages for a real estate company. The object of this page is to present visitors the option to select one or more cities where appropriate office buildings are available. They should be able to select one or more cities.

The code used is a checkbox with the name ReCity with a standard Repeat Region applied.
The code for the checkboxes is this:

<input type="hidden" name="RECity" value="xx">
<%
While ((Repeat1__numRows <> 0) AND (NOT rsCats.EOF))
%>
<p>
<input type="checkbox" name="ReCity" value="<%=(rsCats.Fields.Item("ReCity").Value)%>">
<font color="#FFFFFF"><%=(rsCats.Fields.Item("ReCity").Value)%></font><br>
</p>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsCats.MoveNext()
Wend
%>

Because the checkbox list is dynamically generated, there is no way to know how many checkboxes will appear. The only thing you know is that they are all called "ReCity". As soon as the visitor submits the form, the info is send using a get or post method. For testing purposes it is better to use GET, because you will be able to see the result in the querystring. Once everything works fine you should change that to Post, because the querystring is limited in the number of characters it can handle.

In your resultspage window you will see a querystring with all cities glued together in a way that is not very usefull.
We have to arrange those concatenated values into something more manageable.

This is how you do it. There is handcoding involved here, but it is not too complicated.

<%
QSCity = Request.Querystring("ReCity")
StrCities = Replace( QSCity, ", ", "','" )
%>

You just created an variable (array) called QSCity. All nice and fair. But what does that do?
Ok, we are going to find out by typing the next code.

<%
Response.Write "My cities are: " & Request("QSCity") & "<P>"
%>

Here is the result. It should be something like this (depending on what your selection on the previous page where):

My cities are: New York, Amsterdam, London, Vienna

Now go back to the code above and examine the line that says "StrCities = Replace( QSCity, ", ", "','" )". This does something special.
It replaces all comma-spaces by something we will need to build our query later on. You actually redefined the string with citynames that are separated by comma-spaces to a string where the words are separated by a comma.

Study the format of the data for a moment. The info is in the form of a textstring that holds the citynames, each separated by a comma and a space.
You might consider to use the Split function in ASP to break this string apart. It is an option, but that is not what we will do, because SQL has a great option that works much more elegant and fast. We are going to use the IN-operator to select our results form the database (Works both in Access and SQL Server by the way).
The syntax for this is something like "SELECT bla bla FROM Blabla WHERE FieldName IN (value, value, value)".

Well, the data is actually allready in the right format we want for that particular SQL instruction. Your could look something like this:

MySQL = "SELECT City FROM tblCities WHERE ReCity IN ('" & StrCity & "')"

This query will select all cities in your database who's cityname is in the array you created.
But, first have a good look at the code and mind the single quotes around the text " & StrCity & ".
You need those, otherwise the query won't work for textvalues.

There is an alternative that is even more simple. You can use this if you don't use names, but ID-numbers.

You variables will then look like this:

<%
QSCity = Request.Querystring("ReCity")
%>

And your SQL-clause will be this one:

MySQL = "SELECT City FROM tblCities WHERE ReCity IN (" & StrCity & ")"

You did some pretty nice handcoding now. And you can be proud of these pages because you use the most efficient way to retrieve your data from the database.
The IN-Operator works very smooth and fast. More info on this operator is in the Access help-files and in the Online SQL Books for SQLServer. There is piece of misleading info in the Access Help though. You might stumble over a line that tells you that Access wants the OR-clause separated with semicolons: "Amsterdam";"New York";"London". And that is correct, but only if you work directly in Access and not via ADO. Access DOES accept a comma as separator.

If you have questions about this stuff, you can mail me.