querying information

[aweise]

Hi,

I have a drop down menu that pulls information based on option selected:

<%
varClass = Request.QueryString("classname")
%>

...
<OPTION VALUE="index3.asp?classname=Leadership Excellence Phase I">Leadership Excellence Phase I</option>

I'm using classname instead of classid because I have several classes with the same name but different ids (diff dates).

Once a user selects an option from there they should be able to enroll and/or view a roster of those already enrolled. If I hardcode the value for one particular class everything comes up perfectly. Since I want to do this dynmaically based on on the classname how do I use the variable correctly in my select statement?

Here what I have so far and I tried the following:

mySQL="SELECT c.classID, c.className, c.classDate, c.maxEnroll, count(e.enrollID) as enrolled "&_
"FROM classTable c "&_
"LEFT JOIN enrollTable e ON c.classID = e.classID "&_
"WHERE c.className = 'Leadership Training Phase I' "&_
"GROUP BY c.classID, c.className, c.classDate, c.maxEnroll"

I've tried:

mySQL="SELECT c.classID, c.className, c.classDate, c.maxEnroll, count(e.enrollID) as enrolled "&_
"FROM classTable c "&_
"LEFT JOIN enrollTable e ON c.classID = e.classID "&_
"WHERE c.className = '" & classname & "' "&_
"GROUP BY c.classID, c.className, c.classDate, c.maxEnroll"

STILL NOT WORKING...HELP!

[chris_f]

Hi aweise,

Try changing the class name's in the drop-down menu to 'Leadership Training Phase I' and not the url as they appear to be now. I suspect the database is looking for 'index3.asp?classname=Leadership Excellence Phase I' and not 'Leadership Training Phase I'

Chris

[davegerard]

Try this...

varClass = Request.QueryString("classname")

mySQL = "SELECT c.classID, c.className, c.classDate, c.maxEnroll, count(e.enrollID) as enrolled"
mySQL = mySQL & " FROM classTable c"
mySQL = mySQL & " LEFT JOIN enrollTable e ON c.classID = e.classID"
mySQL = mySQL & " WHERE c.className = ' " & varClass & " ' "
mySQL = mySQL & " GROUP BY c.classID, c.className, c.classDate, c.maxEnroll"

I use "mySql = mySql & " so I can easily comment out a line or a join if need be. That's all. Let me know if it works.

Dave

[aweise]

Thanks so much I actually got it to work similarly to how you had it laid out but a bit differently.