asked on

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

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!

prairiedog

>>>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

ASKER

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

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

Potential working solution looks to be posted without the original poster's feedback.

ASKER CERTIFIED SOLUTION

PianoEn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial