Hi,
I'm actually trying to compute the total for my individual gridview items but I am reaching the dead end at the moment.
I had an idea that probably I can specify another datasource for my total computation but was told that gridviews may only be bound to one datasource at a time.
My second idea simply tells me that, do I have to compute the total in the SQL statements itself as my SQL is already pretty complicated. Attached is how the gridview looks like and what I am trying to accomplish.
I hope someone could actually guide me through this process. Thanks in advance
Below is my ASP code:
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource2" BackColor="White" BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px" CellPadding="5" ForeColor="Black" GridLines="Horizontal">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<%# Container.DataItemIndex + 1 %>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="student_name" HeaderText="Name" SortExpression="student_name" />
<asp:BoundField DataField="student_number" HeaderText="Student ID" SortExpression="student_number" />
<asp:BoundField DataField="student_email" HeaderText="Student Email" SortExpression="student_email" />
<asp:BoundField DataField="course_name" HeaderText="Course" SortExpression="course_name" />
</Columns>
<FooterStyle BackColor="#CCCCCC" />
<PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="#CCCCCC" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:markingConnectionString %>"
SelectCommand="SELECT subject_id, student_id, student_name, student_number, student_email, course_name,
Max((CASE WHEN RowNumber = 1 THEN total ELSE '' END)) AS hurdle1,
Max((CASE WHEN RowNumber = 2 THEN total ELSE '' END)) AS hurdle2,
Max((CASE WHEN RowNumber = 3 THEN total ELSE '' END)) AS hurdle3,
Max((CASE WHEN RowNumber = 4 THEN total ELSE '' END)) AS hurdle4,
Max((CASE WHEN RowNumber = 5 THEN total ELSE '' END)) AS hurdle5
FROM (SELECT Student.student_id, Student.student_name, Student.student_number, Course.course_name, Student.student_email, Subject.subject_id,
ROW_NUMBER() OVER (PARTITION BY Student.student_id
ORDER BY Student.student_id) AS RowNumber, SUM(Marks.marks_value) * Hurdle.hurdle_weight / 100 AS total
FROM Marks INNER JOIN
Student ON Marks.student_id = Student.student_id INNER JOIN
Class ON Student.student_id = Class.student_id INNER JOIN
Subject ON Class.subject_id = Subject.subject_id INNER JOIN
Course ON Student.course_id = Course.course_id INNER JOIN
MarkingCriteria ON Marks.criteria_id = MarkingCriteria.criteria_id INNER JOIN
AssessmentComponent ON MarkingCriteria.component_id = AssessmentComponent.component_id INNER JOIN
Hurdle ON AssessmentComponent.hurdle_id = Hurdle.hurdle_id
GROUP BY Hurdle.hurdle_id, Hurdle.hurdle_weight, Student.student_id, Student.student_name, Student.student_number, Student.student_email,
Course.course_name, Subject.subject_id) AS derive
GROUP BY subject_id, student_id, student_name, student_number, student_email, course_name" FilterExpression="subject_id = '{0}'">
<FilterParameters>
<asp:ControlParameter Name="Subject" ControlID="dropdown_subjectCode" PropertyName="SelectedValue" />
</FilterParameters>
</asp:SqlDataSource>Below is my code-behind file:
string selectedValue = dropdown_subjectCode.SelectedValue.ToString();
GridView1.Columns.Clear();
GridView1.DataBind();
BoundField boundField = new BoundField();
boundField.DataField = "student_name";
boundField.HeaderText = "Name";
GridView1.Columns.Add(boundField);
boundField = new BoundField();
boundField.DataField = "student_number";
boundField.HeaderText = "Student ID";
GridView1.Columns.Add(boundField);
boundField = new BoundField();
boundField.DataField = "student_email";
boundField.HeaderText = "Student Email";
GridView1.Columns.Add(boundField);
boundField = new BoundField();
boundField.DataField = "course_name";
boundField.HeaderText = "Course";
GridView1.Columns.Add(boundField);
for (int i = 0; i < db.getHurdleCount(dropdown_subjectCode.SelectedValue.ToString()); i++)
{
BoundField bf = new BoundField();
string hurdleField = "hurdle" + (i + 1);
bf.DataField = hurdleField;
bf.HeaderText = "Hurdle " + (i + 1) + " Marks";
GridView1.Columns.Add(bf);
}
if (db.getHurdleCount(dropdown_subjectCode.SelectedValue.ToString()) > 0)
{
BoundField bf = new BoundField();
bf.DataField = "";
bf.HeaderText = "Total Marks";
GridView1.Columns.Add(bf);
bf = new BoundField();
bf.DataField = "";
bf.HeaderText = "Grade";
GridView1.Columns.Add(bf);
}
