Help in Gridview Problem

[gurubhullar]

Hey all, im having problem and would like you gurus to help me out.

I have a table in my database as below:

CSEB42401 (a table in my database)

SW079444 CSEB424 01 06/04/2010 Present
SW081907 CSEB424 01 06/04/2010 Absent
SW079444 CSEB424 01 07/04/2010 Present
SW081907 CSEB424 01 07/04/2010 Present
SW079444 CSEB424 01 08/04/2010 Absent
SW081907 CSEB424 01 08/04/2010 Present

Then in my system i have a gridview called GVDistinctDate where i took distinct dates from the above table and made it like this:

GridView : GVDistinctDate

TakenDate
06/04/2010
04704/2010
08/04/2010

Then, I have another gridview called GVReport that is created dynamically at runtime.The columns for GVReport comes from GVDistinctDate, this is how it looks like now:

GridView : GVReport

StudentID 06/04/2010 07/04/2010 08/04/2010
SW079444
SW081907


So now i hope you can help me on how to populate the table with status (present / absent) from the CSEB42401 table which is in my database into the gridview above:

I need to check the Status(present/absent) by using the StudentID and its corresponding date, then bind it to the gridview, in the proper row and cell. my current codes:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
lblSubject.Text = Session("subjectcode")
lblSection.Text = Session("section")
lblError.Visible = False

Dim strTableName As String = lblSubject.Text + lblSection.Text
'MsgBox(strTableName)

Dim connString As String = ConfigurationManager.ConnectionStrings("Conn").ConnectionString

Using myConn As New SqlConnection(connString)
myConn.Open()
Dim command As SqlCommand = myConn.CreateCommand()
Dim r As SqlDataReader
command.Connection = myConn

command.CommandText = "SELECT DISTINCT TakenDate FROM " & strTableName & " ORDER BY [TakenDate]"
'MsgBox(command.CommandText)

r = command.ExecuteReader
GVDistinctDate.DataSource = r
GVDistinctDate.DataBind()

End Using

Using myConn As New SqlConnection(connString)
myConn.Open()
Dim command As SqlCommand = myConn.CreateCommand()
Dim r As SqlDataReader
command.Connection = myConn
command.CommandText = "SELECT DISTINCT StudentID FROM " & strTableName & " ORDER BY [StudentID]"
'MsgBox(command.CommandText)

Dim k As Integer = 1
Dim i As Integer = 0
While i < GVDistinctDate.Rows.Count

Dim headerText As String = GVDistinctDate.Rows(i).Cells(0).Text
If Not (headerText = "") Then
'MsgBox(headerText)
Dim nameColumn As New BoundField()
nameColumn.HeaderText = headerText
GVReport.Columns.Add(nameColumn)
GVReport.DataBind()
Else
MsgBox("error")
End If
k += 1
i += 1

End While

r = command.ExecuteReader
GVReport.DataSource = r
GVReport.DataBind()

End Using
End Sub

How do it insert the PResent and Absent into the gridview. Any help will be appreciated. Thank you.