ASP Question

I am working on an .asp page where I have a recordset pull information from
a database. Basically I have five columns in a database with codes in them.
These codes are numbers (001, 002, 003, 004, etc...) which represent classes
for our project. The codes can be placed in any of the five columns. What I
am trying to do is create a list of all the records with a certain code. In
other words, a page would display all records with "003" (or another number)
in either of the five columns. This is where I run into a problem. I have
created the recordset to filter the classID from the URL Parameter. This is
the code for the recordset:

<%
Dim rs_Roster
Dim rs_Roster_numRows

Set rs_Roster = Server.CreateObject("ADODB.Recordset")
rs_Roster.ActiveConnection = MM_MotionsRegistration_STRING
rs_Roster.Source = "SELECT ID, Student1_First, Student1_Last FROM FormInfo
WHERE Class001 OR Class002 OR Class003 OR Class004 OR Class005 = '" +
Replace(rs_Roster__MMColParam, "'", "''") + "' ORDER BY Student1_Last ASC"
rs_Roster.CursorType = 0
rs_Roster.CursorLocation = 2
rs_Roster.LockType = 1
rs_Roster.Open()

rs_Roster_numRows = 0
%>

Currently, when I use the link with the URL Parameter, it displays all the
records. Where did I go wrong? I only want to display the records that have
the certain class code (passed through the URL) in either of the five
columns. How can I do this?

Seth Meranda
web (AT) meranda (DOT) org

On Wed, 7 Jul 2004 20:52:50 -0500, in macromedia.dreamweaver "Seth
Meranda" <web (AT) meranda (DOT) org> wrote:

| I am working on an .asp page where I have a recordset pull information from
| a database. Basically I have five columns in a database with codes in them.
| These codes are numbers (001, 002, 003, 004, etc...) which represent classes
| for our project. The codes can be placed in any of the five columns. What I
| am trying to do is create a list of all the records with a certain code. In
| other words, a page would display all records with "003" (or another number)
| in either of the five columns. This is where I run into a problem. I have
| created the recordset to filter the classID from the URL Parameter. This is
| the code for the recordset:
|
| <%
| Dim rs_Roster
| Dim rs_Roster_numRows
|
| Set rs_Roster = Server.CreateObject("ADODB.Recordset")
| rs_Roster.ActiveConnection = MM_MotionsRegistration_STRING
| rs_Roster.Source = "SELECT ID, Student1_First, Student1_Last FROM FormInfo
| WHERE Class001 OR Class002 OR Class003 OR Class004 OR Class005 = '" +
| Replace(rs_Roster__MMColParam, "'", "''") + "' ORDER BY Student1_Last ASC"
| rs_Roster.CursorType = 0
| rs_Roster.CursorLocation = 2
| rs_Roster.LockType = 1
| rs_Roster.Open()
|
| rs_Roster_numRows = 0
| %

Apart from the poor database design.....
The WHERE statement is not doing what you think it should do.
The CLASS001 OR is saying: return the values in CLASS001 field if it
is not null. You need to explicitly tell the sql statement what to
return as you've done with the CLASS005 field.

| Currently, when I use the link with the URL Parameter, it displays all the
| records. Where did I go wrong? I only want to display the records that have
| the certain class code (passed through the URL) in either of the five
| columns. How can I do this?
|
| Seth Meranda
| web (AT) meranda (DOT) org
|