How do I build a gridview that is to display and enable editing of students' grades?

PianoEn
PianoEn used Ask the Experts™
on
Dear Experts,

I have the following tables in my mssql database:
STUDENTS(studentid,studentname,classid)
CLASSES(classid,classname)
COURSES(courseid,coursename,classid)
STUDENTSGRADES(studentgradeid,studentid,courseid)

The relationship between courses and classes is a one(class) to many(courses). So basically, each class can have a different number of courses.

I would like to build a gridview that will display the grades of the students in the following way:
I select a class from a dropdown, and based on that, I build a gridview programatically that has as the columns: studentname, coursename(s). (Of course, each class will diaplay a different number of columns, based on the courses it has.) Under studentname (the rows), would be the names of the student in that class, and under coursename, the grade that the student marked on that course.

For example, if I'd choose Class A from the dropdown:
Class A
studentname   math   english   biology   computers
------------------------------------------------------------
john                 80       75          93           100
david               73                     91            84

or Class B:
studentname   science   philosophy   geography
----------------------------------------------------------
george             91           41                 87
allan                 70           75                 79
mark                                                     92

I need the gridview to have a edit, and update button to allow inputting of the grades. I need help with everything... (the sql, how to bind the data, how to build the gridview programatically)

Any help would be greatly appreciated!

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
prairiedog

Commented:
>>>I need help with everything... (the sql, how to bind the data, how to build the gridview programatically)

http://www.asp.net/learn/data-access/
PianoEn

Author

Commented:
Sorry, I was exaggerating. I know how to build the gridview, get the data from the database, etc.... My question is if I could put all the above in one editable gridview.

Anyone?
jhabas_ons

Commented:
You may be able to do this with datatables. I did no testing of this code and you may have to modify to meet your needs. But it should give you a start. Bind the dataset to the gridview and use the studentgradeid and courseid for the edit item key on the gridview.



  Dim dt As New DataTable("GradeBook")
  Dim dtRow As DataRow
  Dim dtColumn As DataColumn
 
  'Build Datatable columns
  dtColumn = New DataColumn("StudentID")
  dtColumn.DataType = System.Type.GetType("System.Int32")
  dt.Columns.Add(dtColumn)
  dtColumn = New DataColumn("StudentName")
  dtColumn.DataType = System.Type.GetType("System.String")
  dt.Columns.Add(dtColumn)
 
  'SELECT * FROM @COURSES WHERE classid = @CourseID
  For Each itm As Course In Courses
   dtColumn = New DataColumn(CStr(Course.CourseID))
   dtColumn.DataType = System.Type.GetType("System.Int32")
   dt.Columns.Add(dtColumn)
   dtColumn = New DataColumn(CStr(Course.CourseName))
   dtColumn.DataType = System.Type.GetType("System.String")
   dt.Columns.Add(dtColumn)
   dtColumn = New DataColumn(CStr(Course.CourseGrade))
   dtColumn.DataType = System.Type.GetType("System.Int32")
   dt.Columns.Add(dtColumn)
  Next
 
  'SELECT * FROM STUDENTSGRADES sg INNER JOIN @COURSES c ON sg.courseid = c.courseid WHERE c.classid = @CourseID
  For Each itm As Grade In Grades
   dtRow = dt.NewRow()
   dtRow.Item("StudentGradeID") = studentgradeid
   dtRow.Item("StudentID") = studentid
   dtRow.Item(CStr(courseid)) = grade
  Next
 
  Dim ds As New DataSet()
  ds = New DataSet()
  ds.Tables.Add(dt)

Open in new window

jhabas_ons

Commented:
Potential working solution looks to be posted without the original poster's feedback.
Commented:
I'm sorry for the delay. I wasn't able to dedicate enough time to repl due to personal circumstances.

I eventually used a different solution than then one jhabas provided, which did not suit my needs. What I used was as follows:

I created a GridViewTemplate class which inherites the ITemplate. I then used that class as can seen in the code attached.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI.WebControls;
using System.Web.UI;
 
/// <summary>
/// Summary description for GridViewTemplate
/// </summary>
public class GridViewTemplate : ITemplate
{
    ListItemType itemType;
    public string fieldName;
    string infoType;
 
    public GridViewTemplate(ListItemType type, string field, string info)
    {
        itemType = type;
        fieldName = field;
        infoType = info;
    }
 
    public void InstantiateIn(System.Web.UI.Control container)
    {
        switch (itemType)
        {
            case ListItemType.Header:
                Literal headerLiteral = new Literal();
                headerLiteral.Text = "<B>" + fieldName + "</B>";
                container.Controls.Add(headerLiteral);
                break;
            case ListItemType.Item:
                switch (infoType)
                {
                    case "Command":
                        LinkButton editButton = new LinkButton();
                        editButton.ID = "EditButton";
                        editButton.Text = "Edit";
                        editButton.CommandName = "Edit";
                        editButton.Click += new EventHandler(editButton_Click);
                        container.Controls.Add(editButton);
                        break;
                    default:
                        Label label = new Label();
                        label.ID = fieldName;
                        label.Text = "";
                        label.DataBinding += new EventHandler(OnDataBinding);
                        container.Controls.Add(label);
                        break;
                }
                break;
            case ListItemType.EditItem:
                if (infoType == "Command")
                {
                    LinkButton updateButton = new LinkButton();
                    updateButton.ID = "UpdateButton";
                    updateButton.CommandName = "Update";
                    updateButton.Text = "Update";
                    //updateButton.OnClientClick = "return confirm('Are you sure?')";
                    container.Controls.Add(updateButton);
 
                    LinkButton cancelButton = new LinkButton();
                    cancelButton.ID = "CancelButton";
                    cancelButton.CommandName = "Cancel";
                    cancelButton.Text = "Cancel";
                    container.Controls.Add(cancelButton); ;
                }
                else
                {
                    TextBox fieldTB = new TextBox();
                    fieldTB.ID = fieldName;
                    fieldTB.Text = "";
                    fieldTB.Width = 65;
                    fieldTB.DataBinding += new EventHandler(OnDataBinding);
                    container.Controls.Add(fieldTB);
                }
                break;
        }
    }
 
    void  OnDataBinding(object sender, EventArgs e)
    {
        object bound_value_obj = null;
        Control ctrl = (Control)sender;
        IDataItemContainer data_item_container = (IDataItemContainer)ctrl.NamingContainer;
        bound_value_obj = DataBinder.Eval(data_item_container.DataItem, fieldName);
        switch (itemType)
        {
            case ListItemType.Item:
                Label fieldLiteral = (Label)sender;
                fieldLiteral.Text = bound_value_obj.ToString();
                break;
            case ListItemType.EditItem:
                TextBox fieldTB = (TextBox)sender;
                fieldTB.Text = bound_value_obj.ToString();
                break;
        }
    }
 
    void editButton_Click(object sender, EventArgs e)
    {
        new Page().Session["InsertFlag"] = 0;
    }
}
 
    protected void BuildDataTable()
    {
        int classID = Convert.ToInt32(ClassDropDown.SelectedValue);
        string term = TermDropDown.SelectedItem.Text;
 
        StudentsBLL studentLogic = new StudentsBLL();
        School.StudentsDataTable studentsTable = studentLogic.GetStudentsByClassID(classID);
        CoursesBLL courseLogic = new CoursesBLL();
        School.CoursesDataTable coursesTable = courseLogic.GetCoursesByClassIDAndTerm(classID, term);
 
        currentCourses.Clear();
        currentStudents.Clear();
        GradesGridView.Columns.Clear();
        StudentsGradesTable = new DataTable("StudentsGradesTable");
        DataColumn column;
        DataRow studentRow;
 
        column = new DataColumn("éÝ Þéä×Ô");
        column.DataType = System.Type.GetType("System.String");
        StudentsGradesTable.Columns.Add(column);
 
        column = new DataColumn("éÝ äèØÙ");
        column.DataType = System.Type.GetType("System.String");
        StudentsGradesTable.Columns.Add(column);
 
        foreach (School.CoursesRow row in coursesTable)
        {
            if (!string.IsNullOrEmpty(row.SubjectName))
            {
                currentCourses.Add(row.CourseID);
                column = new DataColumn(row.SubjectName);
                column.DataType = System.Type.GetType("System.String");
                StudentsGradesTable.Columns.Add(column);
            }
        }
 
        column = new DataColumn("ÞÞÕæâ", System.Type.GetType("System.String"));
        StudentsGradesTable.Columns.Add(column);
 
        foreach (School.StudentsRow rowS in studentsTable)
        {
            currentStudents.Add(rowS.StudentID);
            studentRow = StudentsGradesTable.NewRow();
            studentRow["éÝ Þéä×Ô"] = rowS["Family"].ToString();
            studentRow["éÝ äèØÙ"] = rowS.StudentName;
            foreach (School.CoursesRow rowC in coursesTable)
            {
                int studentID = rowS.StudentID;
 
                StudentGradesBLL studentGradesLogic = new StudentGradesBLL();
                School.StudentsGradesDataTable gradesTable = studentGradesLogic.GetStudentGradesByStudentIDAndCourseID(studentID, rowC.CourseID);
 
                if (gradesTable.Rows.Count > 0)
                {
                    studentRow[rowC.SubjectName] = gradesTable.Rows[0]["Grade"].ToString();
                }
            }
            string average = CalculateStudentAverage(studentRow);
            studentRow["ÞÞÕæâ"] = average;
 
            StudentsGradesTable.Rows.Add(studentRow);
        }
    }
 
    protected void PopulateGridView()
    {
        BuildDataTable();
 
        TemplateField butTemplateField = new TemplateField();
        butTemplateField.ItemTemplate = new GridViewTemplate(ListItemType.Item, "", "Command");
        butTemplateField.HeaderTemplate = new GridViewTemplate(ListItemType.Header, "", "Command");
        butTemplateField.EditItemTemplate = new GridViewTemplate(ListItemType.EditItem, "", "Command");
        GradesGridView.Columns.Add(butTemplateField);
        
        for (int i = 0; i < StudentsGradesTable.Columns.Count; i++)
        {
            TemplateField itemTemplateField = new TemplateField();
            if ((i < 2) || (i == (StudentsGradesTable.Columns.Count - 1))) // Family and Student Name
            {
                itemTemplateField.ItemTemplate = new GridViewTemplate(ListItemType.Item,
                                                                      StudentsGradesTable.Columns[i].ColumnName,
                                                                      StudentsGradesTable.Columns[i].DataType.Name);
            }
            else
            {
                if (DisplayRB.Checked)
                {
                    itemTemplateField.ItemTemplate = new GridViewTemplate(ListItemType.Item,
                                                                          StudentsGradesTable.Columns[i].ColumnName,
                                                                          StudentsGradesTable.Columns[i].DataType.Name);
                }
                else
                {
                    itemTemplateField.ItemTemplate = new GridViewTemplate(ListItemType.EditItem,
                                                                          StudentsGradesTable.Columns[i].ColumnName,
                                                                          StudentsGradesTable.Columns[i].DataType.Name);
                }
            }
            itemTemplateField.HeaderTemplate = new GridViewTemplate(ListItemType.Header,
                                                                  StudentsGradesTable.Columns[i].ColumnName,
                                                                  StudentsGradesTable.Columns[i].DataType.Name);
            if ((i < 2) || (i == (StudentsGradesTable.Columns.Count - 1)))
            {
                itemTemplateField.EditItemTemplate = new GridViewTemplate(ListItemType.Item,
                                                                      StudentsGradesTable.Columns[i].ColumnName,
                                                                      StudentsGradesTable.Columns[i].DataType.Name);
            }
            else
            {
                itemTemplateField.EditItemTemplate = new GridViewTemplate(ListItemType.EditItem,
                                                                      StudentsGradesTable.Columns[i].ColumnName,
                                                                      StudentsGradesTable.Columns[i].DataType.Name);
            }
            GradesGridView.Columns.Add(itemTemplateField);
        }
 
        GradesGridView.DataSource = StudentsGradesTable;
        GradesGridView.DataBind();
    }

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial