i'm trying to display some values in Gridview. but i don't know how many columns i need to display. at run time only i will come to know how many columns i need to display. it depends up on the values from Database. so i'm creating columns dynamically and adding to gridview. till that it works fine. but i want some columns value to be hyperlink. so i need to create some columns hyperlink dynamically.

Database table looks like this

depends upon the "Distinct Date", the number of columns in gridview is created dynamically.

Gridview looks like this

for all the date columns i want to create dynamic hyperlink.

Codes

Sub Display_Table
        Dim Table_MAINPAGE As DataTable
        Dim Row As DataRow
        Dim dcol As DataColumn
        Dim myConnection As SqlConnection
        Dim MySQL As String
        Dim myCommand As SqlCommand
        Dim myreader As SqlDataReader
        Dim pFirst As Boolean = True
        Dim Name_NotExist, Group_NotExist As Boolean
        aryDate.Clear()
        aryName.Clear()
        aryDate_Desc.Clear()
       
        Table_MAINPAGE = New DataTable()
        dcol = New DataColumn(" # ")
        Table_MAINPAGE.Columns.Add(dcol)
        dcol = New DataColumn("Name")
        Table_MAINPAGE.Columns.Add(dcol)
        myConnection = New SqlConnection(WebConfigurationManager.ConnectionStrings("cvConnectionString").ToString)
        MySQL = "select DISTINCT ondate from sList ORDER BY ondate"
        myConnection.Open()
        myCommand = New SqlCommand(MySQL, myConnection)
        myreader = myCommand.ExecuteReader
        While myreader.Read
            'Get distinct Date from database
            aryDate.Add(myreader(0))
            dcol = New DataColumn(Trim(myreader(0)))
            Table_MAINPAGE.Columns.Add(dcol)
        End While
        myreader.Close()
        pFirst = True
        MySQL = "Select fname, lname from Users ORDER BY fname"
        myCommand = New SqlCommand(MySQL, myConnection)
        myreader = myCommand.ExecuteReader
        While myreader.Read
            If pFirst = True Then
                pFirst = False
                'Get distinct Name from database
                aryName.Add(myreader(0) & " " & myreader(1))
                GoTo NextValue
            End If
            Name_NotExist = False
            Group_NotExist = False
            For q As Integer = 0 To aryName.Count - 1
                If myreader(0) & " " & myreader(1) <> aryName.Item(q) Then
                    Name_NotExist = True
                Else
                    Name_NotExist = False
                    GoTo NextValue
                End If
            Next
            If Name_NotExist = True Then
                aryName.Add(myreader(0) & " " & myreader(1))
            End If
NextValue:
        End While
        myreader.Close()

        Dim h As Integer = 0
        Dim gBool As Boolean = False
        'Now add data for dynamic columns
        'As first column is increment, as number of data in database
        'Let's add some data to the other columns
        For k As Integer = 0 To aryName.Count - 1
            aryDate_Desc.Clear()
            MySQL = "select ondate, description from sList where [name] = '" & Trim(aryName.Item(k).ToString) & "' order by ondate"
            myCommand = New SqlCommand(MySQL, myConnection)
            myreader = myCommand.ExecuteReader
            'Create a new row
            Row = Table_MAINPAGE.NewRow()
            h = h + 1
            Row(" # ") = h
            Row("Name") = aryName.Item(k).ToString
            While myreader.Read
                aryDate_Desc.Add(myreader(0) & " - " & myreader(1))
            End While
            For i As Integer = 0 To aryDate.Count - 1
                gBool = False
                For j As Integer = 0 To aryDate_Desc.Count - 1
                    If Trim(aryDate.Item(i)) = Trim(aryDate_Desc.Item(j).Split("-")(0)) Then
                        'Initialize the row data.
                        Row(Trim(aryDate.Item(i))) = Trim(aryDate_Desc.Item(j).Split("-")(1))
                        gBool = True
                        GoTo NextArrayValue
                    End If
                Next
NextArrayValue:
                If gBool <> True Then
                    Row(Trim(aryDate.Item(i))) = "Add New"
                End If
            Next
            'Add the row to the datatable.
            Table_MAINPAGE.Rows.Add(Row)
            myreader.Close()
        Next
       
        'Initialize the DataSource
        GridView1.DataSource = Table_MAINPAGE
        For i As Integer = 0 To GridView1.Columns.Count - 1
            GridView1.Columns(i).ItemStyle.Width = 500
        Next
        'Bind the datatable with the GridView
        GridView1.DataBind()
        myConnection.Close()
End Sub

How can i create dynamic hyperlinks for the date columns.....

if you have any idea, how to do this, please help me. if you can some example, that's will be great.

Thanks in advance.  

Then i tried this code using TemplateFiled,

Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports system.IO

Public Class TemplateHandler
    Implements ITemplate
    Private columnID, columnText As String
    Public Sub New(ByVal colText As String)
        columnText = colText
    End Sub
    Private Sub ITemplate_InstantiateIn(ByVal container As Control) Implements ITemplate.InstantiateIn
        Dim lbl As New Label()
        lbl.Text = columnText
        container.Controls.Add(lbl)
    End Sub
End Class

Imports System
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Configuration

Partial Class Issues
    Inherits System.Web.UI.Page
    Dim aryDate, aryName, aryDate_Desc, aryDesc_Color_Date As New ArrayList

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim Table_MAINPAGE As DataTable
        Dim Row As DataRow
        Dim dcol As DataColumn
        Dim myConnection As SqlConnection
        Dim MySQL As String
        Dim myCommand As SqlCommand
        Dim myreader As SqlDataReader
        Dim pFirst As Boolean = True
        Dim Name_NotExist, Group_NotExist As Boolean
        aryDate.Clear()
        aryName.Clear()
        aryDate_Desc.Clear()
        aryDesc_Color_Date.Clear()
        Table_MAINPAGE = New DataTable()
        dcol = New DataColumn(" # ")
        Table_MAINPAGE.Columns.Add(dcol)
        dcol = New DataColumn("Name")
        Table_MAINPAGE.Columns.Add(dcol)
        myConnection = New SqlConnection(WebConfigurationManager.ConnectionStrings("cvConnectionString").ToString)
        MySQL = "select DISTINCT ondate from sList ORDER BY ondate"
        myConnection.Open()
        myCommand = New SqlCommand(MySQL, myConnection)
        myreader = myCommand.ExecuteReader
        While myreader.Read
            aryDate.Add(myreader(0))
        End While
        myreader.Close()
        pFirst = True
        MySQL = "Select fname, lname from Users ORDER BY fname"
        myCommand = New SqlCommand(MySQL, myConnection)
        myreader = myCommand.ExecuteReader
        While myreader.Read
            If pFirst = True Then
                pFirst = False
                'Get distinct Name from database
                aryName.Add(myreader(0) & " " & myreader(1))
                GoTo NextValue
            End If
            Name_NotExist = False
            Group_NotExist = False
            For q As Integer = 0 To aryName.Count - 1
                If myreader(0) & " " & myreader(1) <> aryName.Item(q) Then
                    Name_NotExist = True
                Else
                    Name_NotExist = False
                    GoTo NextValue
                End If
            Next
            If Name_NotExist = True Then
                aryName.Add(myreader(0) & " " & myreader(1))
            End If
NextValue:
        End While
        myreader.Close()

        Dim h As Integer = 0
        Dim gBool As Boolean = False
        'Now add data for dynamic columns
        'As first column is increment, as number of data in database
        'Let's add some data to the other columns
        For k As Integer = 0 To aryName.Count - 1
            If Trim(aryName.Item(k).split("-")(1)) = Trim(aryGroup.Item(p)) Then
                aryDate_Desc.Clear()
                MySQL = "select ondate, description, color from sList where [name] = '" & Trim(aryName.Item(k).ToString) & "' order by ondate"
                myCommand = New SqlCommand(MySQL, myConnection)
                myreader = myCommand.ExecuteReader
                'Create a new row
                Row = Table_MAINPAGE.NewRow()
                h = h + 1
                Row(" # ") = h
                Row("Name") = aryName.Item(k).ToString
                While myreader.Read
                    aryDate_Desc.Add(myreader(0) & " - " & myreader(1))
                    aryDesc_Color_Date.Add(myreader(0) & " -" & myreader(1) & " - " & myreader(3))
                End While
                'Add the row to the datatable.
                Table_MAINPAGE.Rows.Add(Row)
                myreader.Close()
            End If
        Next
        GridView1.AutoGenerateColumns = False
        Dim NameColumn As New BoundField()
        NameColumn.DataField = " # "
        NameColumn.HeaderText = " # "
        GridView1.Columns.Add(NameColumn)
        NameColumn = New BoundField()
        NameColumn.DataField = "Name"
        NameColumn.HeaderText = "Name"
        GridView1.Columns.Add(NameColumn)
        ' Here is template column portion
        Dim TmpCol As New TemplateField()
        TmpCol.HeaderText = aryDate.Item(0).ToString
        GridView1.Columns.Add(TmpCol)
        TmpCol.ItemTemplate = New TemplateHandler(String.Format("<a href=Update.aspx?id=" & "81" & ">" & "work" & "</a>"))
        TmpCol = New TemplateField()
        TmpCol.HeaderText = aryDate.Item(1).ToString
        GridView1.Columns.Add(TmpCol)
        TmpCol.ItemTemplate = New TemplateHandler(String.Format("<a href=Update.aspx?id=" & "81" & ">" & "Off" & "</a>"))
        TmpCol = New TemplateField()
        TmpCol.HeaderText = aryDate.Item(2).ToString
        GridView1.Columns.Add(TmpCol)
        TmpCol.ItemTemplate = New TemplateHandler(String.Format("<a href=Update.aspx?id=" & "81" & ">" & "Out" & "</a>"))
        'Initialize the DataSource
        GridView1.DataSource = Table_MAINPAGE
        For i As Integer = 0 To GridView1.Columns.Count - 1
            GridView1.Columns(i).ItemStyle.Width = 500
        Next
        'Bind the datatable with the GridView
        GridView1.DataBind()
        myConnection.Close()
    End Sub
    Protected Sub GridView1_RowCreated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowCreated
        Dim tCell As TableCell
        Dim drv As DataRowView
        Dim ob As Object
        Dim j As Integer = 1
        For i As Integer = 0 To aryDate.Count - 1
            j = j + 1
            If e.Row.RowType = DataControlRowType.DataRow Then
                drv = e.Row.DataItem
                ob = drv(aryDate.Item(i))
                If ob.ToString.Contains("Work") Then
                    tCell = e.Row.Cells(j)
                    tCell.BackColor = Drawing.Color.Blue
                ElseIf ob.ToString.Contains("Off") Then
                    tCell = e.Row.Cells(j)
                    tCell.BackColor = Drawing.Color.LightBlue
                ElseIf ob.ToString.Contains("Out") Then
                    tCell = e.Row.Cells(j)
                    tCell.BackColor = Drawing.Color.Gray
                ElseIf ob.ToString.Contains("Add New") Then
                    tCell = e.Row.Cells(j)
                    tCell.BackColor = Drawing.Color.White
                End If
            End If
        Next
    End Sub
End Class

when i use the above code, its creating the hyperlink in label's inside gridview. but the gridview looks like this... its adds same text for the entire columns.

But this is the format i need to display the gridview.

is it possible to display it like that?

i tried this code too.. but same result

Dim myLink As New HyperLink
myLink.Text = columnText
myLink.NavigateUrl = "Update.aspx?id=" & columnID
container.Controls.Add(myLink)

Dim TmpCol As New TemplateField()
TmpCol.HeaderText = aryDate.Item(0).ToString
GridView1.Columns.Add(TmpCol)
TmpCol.ItemTemplate = New TemplateHandler(81,"my Link Text")

If you have any idea, how to do this, please let me know.

Thanks in advance....

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
       
        Dim h As Integer = 0
        Dim gBool As Boolean = False
        'Now add data for dynamic columns
        For k As Integer = 0 To aryName.Count - 1
            If Trim(aryName.Item(k).split("-")(1)) = Trim(aryGroup.Item(p)) Then
                aryDate_Desc.Clear()
 'for each name, need to get date, description, color and recno
                MySQL = "select ondate, description, color, recno from sList where [name] = '" & Trim(aryName.Item(k).ToString) & "' order by ondate"
                myCommand = New SqlCommand(MySQL, myConnection)
                myreader = myCommand.ExecuteReader
                'Create a new row
                Row = Table_MAINPAGE.NewRow()
 h = h + 1
 'adding values to "#" row.
                Row(" # ") = h
 'adding values to "Name" row.
                Row("Name") = aryName.Item(k).ToString
                While myreader.Read
                    aryDate_Desc.Add(myreader(0) & " - " & myreader(1))
                    'adding date, description, index, name to an array. so while creating hyperlink, we can pass the index values  
                    aryHyperLinkIndex.Add(myreader(0) & " - " & myreader(1) & " - " & myreader(3) & " - " & Trim(aryName.Item(k).ToString))
                End While
 For i As Integer = 0 To aryDate.Count - 1
                    gBool = False
                    For j As Integer = 0 To aryDate_Desc.Count - 1
                        If Trim(aryDate.Item(i)) = Trim(aryDate_Desc.Item(j).Split("-")(0)) Then
                            'adding value to "Date" row.
                            Row(Trim(aryDate.Item(i))) = Trim(aryDate_Desc.Item(j).Split("-")(1))
                            gBool = True
                            GoTo NextArrayValue
                        End If
                    Next
NextArrayValue:
                    If gBool <> True Then
                        'adding value as "Add New" to "Date" row, if no value is set.
                        Row(Trim(aryDate.Item(i))) = "Add New"
         'adding date, description, index, name to an array. so while creating hyperlink, we can pass the index values
         aryHyperLinkIndex.Add(Trim(aryDate.Item(i)) & " - " & "Add New" & " - " & "0" & " - " & Trim(aryName.Item(k).split("-")(0)).ToString)
                    End If
                Next
                'Add the row to the datatable.
                Table_MAINPAGE.Rows.Add(Row)
                myreader.Close()
            End If
        Next
            'for new group, we need to create empty row (empty row will seperate each group)
            'Create a new row
            Row = Table_MAINPAGE.NewRow()
            Row(" # ") = ""
            Row("Name") = ""
            For i As Integer = 0 To aryDate.Count - 1
                'Initialize the row data.
                Row(Trim(aryDate.Item(i))) = ""
            Next
            'Add the row to the datatable.
            Table_MAINPAGE.Rows.Add(Row)
        Next
        'Initialize the DataSource
        GridView1.DataSource = Table_MAINPAGE
        For i As Integer = 0 To GridView1.Columns.Count - 1
            GridView1.Columns(i).ItemStyle.Width = 500
        Next
        'Bind the datatable with the GridView
        GridView1.DataBind()
        myConnection.Close()
    End Sub

Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
     
  If e.Row.RowType = DataControlRowType.DataRow Then
             'gets first date cell's header 
            Dim Header_Date1 As DataControlFieldHeaderCell = CType(Me.GridView1.HeaderRow.Cells(2), DataControlFieldHeaderCell)
                For k As Integer = 0 To aryHyperLinkIndex.Count - 1
                     'checks if first date cell's header and AryHyperLinkIndex date are equal
                    If Trim(Header_Date1.Text) = Trim(aryHyperLinkIndex.Item(k).split("-")(0)) Then
                        'if Current cell2  (first Date) text is equal to aryHyperLinkIndex description  
                        If Trim(e.Row.Cells(2).Text) = Trim(aryHyperLinkIndex.Item(k).Split("-")(1)) Then
                            'if current cell1 (Name) text is equal to aryHyperLinkIndex name
                            If Trim(e.Row.Cells(1).Text) = Trim(aryHyperLinkIndex.Item(k).split("-")(3)) Then
                                e.Row.Cells(2).Text = e.Row.Cells(2).Text
                                'if current cell2 (first date) text is equal to "Add New", then create Hyperlink
                                If Trim(e.Row.Cells(2).Text) = "Add New" Then
                                    Dim link As HyperLink = New HyperLink()
                                    link.Text = e.Row.Cells(2).Text
                                    'Passing the date and name
                                    link.NavigateUrl = "AddValues.aspx?DateValue=" & Trim(Header_Date1.Text) & "&NameValue=" & Trim(aryHyperLinkIndex.Item(k).split("-")(3))
                                    e.Row.Cells(2).Controls.Add(link)
                                    GoTo SecondCell
                                Else
                                    Dim link As HyperLink = New HyperLink()
                                    link.Text = e.Row.Cells(2).Text
                                    'Passing index
                                    link.NavigateUrl = "ViewUpdates.aspx?ViewUpdateValue=" & Trim(aryHyperLinkIndex.Item(k).Split("-")(2))
                                    e.Row.Cells(2).Controls.Add(link)
                                    GoTo SecondCell
                                End If
                            End If
                        End If
                    End If
                Next
SecondCell:
        End If
    End Sub