ControlParameters and NULL value

[pbb72]

Hi all,

I am writing a page to display the people registered for a selected course, and the people that are on the waitinglist.

I do this using various GridViews bound to SqlDataSources. The courses table has a "Maximum" field for every course, which says how many people can participate. When more register, they are put on the waitinglist.

To show the list of participants, I use a SqlDataSource with SelectCommand "SELECT TOP(@max) ..." to leave off the ones on the waiting list.

Problem now is when a course has not defined the maximum number of participants, i.e. Maximum contains NULL. How should I handle this situation?

In a related problem, to show the waitinglist, I use "SELECT TOP(@amount-@max) ..." where @amount is determined by a COUNT(*) subquery counting the number of registrations for a selected course. However, when @amount < @max, I get a negative value for TOP and an error message. How should I handle this?

Should I use DataBinding event triggers to modify the SelectCommand parameter?

Thanks, Peter

 

[ca8msm]

Problem now is when a course has not defined the maximum number of participants

If this is mandatory, why not make it so it has to be entered when adding a course (or give it a default)?


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[pbb72]

No, it is not mandatory. Sometimes for example the exact location is decided afterwards, based on the number of participants. A default can also be misleading, suggesting that this amount has been determined.
Also, having a value for maximum does not solve my second problem, generating the waiting list...

 

[ca8msm]

Please provide the table structures for all relevant tables and I'll show you an example of how you could do it.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[pbb72]

Thanks for the help, c8!

Following is not the complete code, but since it is all in Norwegian I have to translate it to be understandable. I think this is all the relavant stuff:

Courses table: ID, Name, Maximum
Participants table: StudentID, CourseID, EnlistDate

<asp:SqlDataSource ID="CourseData" SelectCommand="
SELECT * FROM Courses
INNER JOIN (
SELECT CourseID, COUNT(*) AS Number
FROM Participants
GROUP BY CourseID) AS Aggregate
ON Courses.ID = Aggregate.CourseID
">
</asp:SqlDataSource>
<asp:GridView ID="CourseTable"
DataKeyNames="ID,Maximum,Number"
DataSourceID="CourseData"></asp:GridView>

<asp:SqlDataSource id="ParticipantsData"
SelectCommand = "
SELECT TOP(@max) * FROM Participants
ORDER BY EnlistDate
">
<SelectParameters>
<asp:ControlParameter ControlID="CoursesTable"
Name="max" PropertyName="SelectedDataKey[1]"/>
</SelectParameters>
</asp:SqlDataSource>

<asp:SqlDataSource id="WaitinglistData"
SelectCommand="
SELECT TOP(@number-@max) * FROM Participants
ORDER BY EnlistDate
">
<SelectParameters>
<asp:ControlParameter ControlID="CoursesTable"
Name="max" PropertyName="SelectedDataKey[1]"/>
<asp:ControlParameter ControlID="CoursesTable"
Name="number" PropertyName="SelectedDataKey[2]"/>
</SelectParameters>
</asp:SqlDataSource>

 

[jbenson001]

Show the table structures not the select statements

 

[pbb72]

What exactly is meant with the table structures? Don't you mean the structure of the database tables? That's right there at the beginning of my reply:

Courses table: ID, Name, Maximum
Participants table: StudentID, CourseID, EnlistDate

(Participants.CourseID is linked to Course.ID)

 

[jbenson001]

Sorry,,, missed that

 

[ca8msm]

Problem now is when a course has not defined the maximum number of participants, i.e. Maximum contains NULL. How should I handle this situation?

A simple (maybe not elegant though!) solution would be to check if the field is null first and if it is, pass in a large enough value so that all records would be returned (for example, I doubt if 9,999,999 people would ever turn up to your course!). This task would be made easier if you used a Stored Procedure rather than a dynamic SQL Statement as it could all be done in the SP itself.

In a related problem, to show the waitinglist, I use "SELECT TOP(@amount-@max) ..." where @amount is determined by a COUNT(*) subquery counting the number of registrations for a selected course. However, when @amount < @max, I get a negative value for TOP and an error message. How should I handle this?

Rather than do that, have a look at using an "not exists" clause. You can select all the records from the participants table and then use the clause to filter out all of those people who are attending; therefore you'll be left with the "waiting list".



____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[jshurst]

In your sql you could also use a case statement

SELECT ID, Name, 
Case Maximum
When is null then 0
Else Maximum
End
 FROM Courses
  INNER JOIN (
    SELECT CourseID, COUNT(*) AS Number
    FROM Participants
    GROUP BY CourseID) AS Aggregate
  ON Courses.ID = Aggregate.CourseID

Not sure if that helps, kind of wrote this out pretty quickly.