Gridview - How to pass value from datatable to MKB TimeSelector

In my asp.net + VB Gridview, I had bind several column from datatable into one single column of the gridview.

But I have no idea how to pass the time value from datatable to MKB TimeSelector in gridview and update. Please help. Thanks.

But I have no idea how to get those value when doing RowEditing & RowUpdating. Please help. Thanks.

The following is the VB code:

Protected Sub GridView1_RowEditing(ByVal sender As Object, ByVal e As GridViewEditEventArgs)
    'Set the edit index.
    Gridview1.EditIndex = e.NewEditIndex        
    'Bind data to the GridView control.
    BindData()
End Sub

Protected Sub GridView1_RowCancelingEdit(ByVal sender As Object, ByVal e As GridViewCancelEditEventArgs)
    'Reset the edit index.
    Gridview1.EditIndex = -1
    'Bind data to the GridView control.
    BindData()
End Sub

Protected Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)
    'Retrieve the table from the session object.
    Dim dt = CType(Session("dt"), DataTable)

    'Update the values.
    Dim row = Gridview1.Rows(e.RowIndex)

    .............................

    'Reset the edit index.
    Gridview1.EditIndex = -1

    'Bind data to the GridView control.
    BindData()
End Sub

The following is the aspx code:

Private Sub CreateDataTable() 

    Dim cmd As New System.Data.SqlClient.SqlCommand 
    Dim sql As String 
    Dim reader As System.Data.SqlClient.SqlDataReader 
    Dim cmd3 As New System.Data.SqlClient.SqlCommand 
    Dim sql3 As String 
    Dim reader3 As System.Data.SqlClient.SqlDataReader 

    Using conn2 As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("XXXonnectionString").ConnectionString) 
        conn2.Open() 
        cmd.Connection = conn2 

        sql = "SET DATEFORMAT dmy;SELECT * FROM XXXX " 
        cmd.CommandText = sql 
        reader = cmd.ExecuteReader() 

        Dim TempStaffKey As Integer 
        Dim TempPostKey As Integer 
        Dim TempShiftDate As DateTime 
        Dim TempStartTime As DateTime 
        Dim TempEndTime As DateTime 
        Dim TempSL As String 
        Dim TempRosterKey As Integer 
        Dim TempVL As String 
        Dim TempML As String 
        Dim TempPH As String 
        Dim TempAPH As String 
        Dim TempTOIL As String 
        Dim TempOthers As String 
        Dim TempShiftType As Integer 
        Dim TempSubmittedBy As Integer 

        Dim dt As New DataTable() 
        dt.Columns.Add(New DataColumn("StaffName", GetType(String))) 
        dt.Columns.Add(New DataColumn("PostCode", GetType(String))) 
        dt.Columns.Add(New DataColumn("StaffKey", GetType(Int32))) 
        dt.Columns.Add(New DataColumn("PostKey", GetType(Int32))) 

        'Monday 
        dt.Columns.Add(New DataColumn("Col1_RosterKey", GetType(Int32))) 
        dt.Columns.Add(New DataColumn("Col1_ShiftDate", GetType(DateTime))) 
        dt.Columns.Add(New DataColumn("Col1_StartTime", GetType(DateTime))) 
        dt.Columns.Add(New DataColumn("Col1_EndTime", GetType(DateTime))) 
        dt.Columns.Add(New DataColumn("Col1_SL", GetType(String))) 
        dt.Columns.Add(New DataColumn("Col1_VL", GetType(String))) 
        dt.Columns.Add(New DataColumn("Col1_ML", GetType(String))) 
        dt.Columns.Add(New DataColumn("Col1_PH", GetType(String))) 
        dt.Columns.Add(New DataColumn("Col1_APH", GetType(String))) 
        dt.Columns.Add(New DataColumn("Col1_TOIL", GetType(String))) 
        dt.Columns.Add(New DataColumn("Col1_Others", GetType(String))) 
        dt.Columns.Add(New DataColumn("Col1_ShiftType", GetType(Int32))) 
        dt.Columns.Add(New DataColumn("Col1_SubmittedBy", GetType(Int32))) 

        Dim dr As DataRow 

        While reader.Read()    '---For each row 

            g_TempStaffKey = "0" 
            TempStaffKey = reader("staff_key")   'will not null 
            g_selectstaffkey = TempStaffKey 
            g_selectpostkey = reader("post_key") 
            g_selectstaffname = RTrim(reader("name_eng")) 
            g_selectpostcode = RTrim(reader("post_code")) 

            Using conn3 As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("XXXConnectionString").ConnectionString) 
                conn3.Open() 
                cmd3.Connection = conn3 
                sql3 = "SET DATEFORMAT dmy;SELECT * FROM xxx" 
                cmd3.CommandText = sql3 
                reader3 = cmd3.ExecuteReader() 
                If reader3.Read() Then 

                    TempStaffKey = reader3("staff_key") 

                    If Not IsDBNull(reader3("post_key")) Then 
                        TempPostKey = reader3("post_key") 
                    End If 

                    If Not IsDBNull(reader3("roster_key")) Then 
                        TempRosterKey = reader3("roster_key") 
                    End If 

                    If Not IsDBNull(reader3("shift_date")) Then 
                        TempShiftDate = Format(reader3("shift_date"), "dd/MM/yyyy") 
                    End If 

                    If Not IsDBNull(reader3("start_time")) Then 
                        TempStartTime = Format(reader3("start_time"), "HH:mm") 
                    End If 

                    If Not IsDBNull(reader3("end_time")) Then 
                        TempEndTime = Format(reader3("end_time"), "HH:mm") 
                    End If 

                    If Not IsDBNull(reader3("SL")) Then 
                        TempSL = reader3("SL") 
                    Else 
                        TempSL = "0" 
                    End If 

                    If Not IsDBNull(reader3("VL")) Then 
                        TempVL = reader3("VL") 
                    Else 
                        TempVL = "0" 
                    End If 

                    If Not IsDBNull(reader3("ML")) Then 
                        TempML = reader3("ML") 
                    Else 
                        TempML = "0" 
                    End If 

                    If Not IsDBNull(reader3("PH")) Then 
                        TempPH = reader3("PH") 
                    Else 
                        TempPH = "0" 
                    End If 

                    If Not IsDBNull(reader3("APH")) Then 
                        TempAPH = reader3("APH") 
                    Else 
                        TempAPH = "0" 
                    End If 

                    If Not IsDBNull(reader3("TOIL")) Then 
                        TempTOIL = reader3("TOIL") 
                    Else 
                        TempTOIL = "0" 
                    End If 

                    If Not IsDBNull(reader3("Others")) Then 
                        TempOthers = reader3("Others") 
                    Else 
                        TempOthers = "null" 
                    End If 

                    If Not IsDBNull(reader3("shift_type")) Then 
                        TempShiftType = reader3("shift_type") 
                    End If 

                    If Not IsDBNull(reader3("submitted_by")) Then 
                        TempSubmittedBy = reader3("submitted_by") 
                    End If 

                    dr = dt.NewRow() 

                    dr("StaffName") = g_selectstaffname 
                    dr("PostCode") = g_selectpostcode 
                    dr("StaffKey") = TempStaffKey 
                    dr("PostKey") = TempPostKey 

                    'Col1 
                    If TempShiftDate = g_header1 Then 
                        dr("Col1_RosterKey") = TempRosterKey 
                        dr("Col1_ShiftDate") = TempShiftDate 
                        dr("Col1_StartTime") = TempStartTime 


                        dr("Col1_EndTime") = TempEndTime 
                        dr("Col1_SL") = TempSL 
                        dr("Col1_VL") = TempVL 
                        dr("Col1_ML") = TempML 
                        dr("Col1_PH") = TempPH 
                        dr("Col1_APH") = TempAPH 
                        dr("Col1_TOIL") = TempTOIL 
                        dr("Col1_Others") = TempOthers 
                        dr("Col1_ShiftType") = TempShiftType 
                        dr("Col1_SubmittedBy") = TempSubmittedBy 
                    End If 

                End If 
................. 

                conn3.Close() 
                reader3.Close() 
            End Using 
        End While 

        Gridview1.DataSource = dt 
        Gridview1.DataBind() 

        'Persist the table in the Session object. 
        Session("dt") = dt 

        reader.Close() 

    End Using 

End Sub



<%@ Page Title="Input" Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="sd210.aspx.vb" Inherits="sd210" ValidateRequest="false"%>
<%@ Register Assembly="TimePicker" Namespace="MKB.TimePicker" TagPrefix="MKB" %>

<asp:Content ID="Content1" ContentPlaceHolderID="CPH1" Runat="Server">
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>    
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:hris_shiftdutyConnectionString %>"SelectCommand="set language english; SET DATEFORMAT dmy; select * from troster">
</asp:SqlDataSource>

    <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:hris_shiftdutyConnectionString %>"
    SelectCommand="set language english; select * from tshift_type">
</asp:SqlDataSource>
<asp:Label ID="lb_login_name" runat="server"  Visible="false"  ></asp:Label>
<asp:Label ID="lb_login_staff_key" runat="server"  Visible="false"  ></asp:Label>
<asp:Label ID="lb_login_post_key" runat="server"  Visible="false"  ></asp:Label>
<asp:Label ID="lb_test" runat="server"  Visible="false"  ></asp:Label>

<asp:GridView ID="Gridview1" runat="server"
AutoGenerateColumns = "false" Font-Names = "Arial"
AutoGenerateEditButton="True" 
Font-Size = "10pt" AlternatingRowStyle-BackColor = "#C2D69B" 
AllowPaging ="true"  
PageSize = "20" Caption = "" 
onrowdatabound="GridView1_RowDataBound"
OnRowEditing="GridView1_RowEditing"
OnRowCancelingEdit="GridView1_RowCancelingEdit"
OnRowUpdating="GridView1_RowUpdating"
OnPageIndexChanging="GridView1_PageIndexChanging">

<HeaderStyle BackColor="#4A3C8C" Font-Bold="True" ForeColor="#F7F7F7" />
<Columns>
 <asp:BoundField DataField = "PostCode" HeaderText = "Post"  ReadOnly ="true" />
 <asp:BoundField DataField = "StaffName" HeaderText = "Name" ReadOnly ="true"  />

<asp:TemplateField HeaderText="Working<br>Time">
<ItemTemplate>
<asp:Label ID="lb1_rosterkey"  runat="server"  Text='<%#DataBinder.Eval(Container.DataItem, "Col1_RosterKey")%>'  Visible ="false" ></asp:Label>
<asp:BoundField DataField = "Col1_ShiftType" />
<MKB:TimeSelector ID="Col1_StartTime1" runat="server" DisplaySeconds="False" ReadOnly="true" MinuteIncrement="1" AmPm="AM" BorderColor="Silver" 
                            Date="" Hour="07" Minute="0"  SelectedTimeFormat="Twelve"></MKB:TimeSelector>
<MKB:TimeSelector ID="Col1_EndTime1" runat="server" DisplaySeconds="False"  ReadOnly="true" MinuteIncrement="1" AmPm="PM" BorderColor="Silver" 
                            Date="" Hour="07" Minute="0" SelectedTimeFormat="Twelve"></MKB:TimeSelector>

</ItemTemplate>
<EditItemTemplate>
<MKB:TimeSelector ID="Col1_StartTime1" runat="server" Displa开发者_JAVA技巧ySeconds="False"  MinuteIncrement="1" AmPm="AM" BorderColor="Silver" 
                            Date="" Hour="07" Minute="0"  SelectedTimeFormat="Twelve"></MKB:TimeSelector>
<MKB:TimeSelector ID="Col1_EndTime1" runat="server" DisplaySeconds="False"  MinuteIncrement="1" AmPm="PM" BorderColor="Silver" 
                            Date="" Hour="07" Minute="0" SelectedTimeFormat="Twelve"></MKB:TimeSelector>
</EditItemTemplate> 

</asp:TemplateField>

<asp:TemplateField HeaderText="Leave/TOIL">
<ItemTemplate>
<asp:CheckBox
ID="cb1_VL" Enabled="false" Text="VL" 
runat="server"
Checked=<%#DataBinder.Eval(Container.DataItem, "Col1_VL")%> />
<asp:CheckBox
ID="cb1_SL" Enabled="false" Text="SL" 
runat="server"
Checked=<%#DataBinder.Eval(Container.DataItem, "Col1_SL")%> />
<asp:CheckBox
ID="cb1_ML" Enabled="false" Text="ML" 
runat="server"
Checked=<%#DataBinder.Eval(Container.DataItem, "Col1_ML")%> />
<asp:CheckBox
ID="cb1_PH" Enabled="false" Text="PH" 
runat="server"
Checked=<%#DataBinder.Eval(Container.DataItem, "Col1_PH")%> />
<asp:CheckBox
ID="cb1_APH" Enabled="false" Text="APH" 
runat="server"
Checked=<%#DataBinder.Eval(Container.DataItem, "Col1_APH")%> />
<asp:CheckBox
ID="cb1_TOIL" Enabled="false" Text="TOIL" 
runat="server"
Checked=<%#DataBinder.Eval(Container.DataItem, "Col1_TOIL")%> />
<br />
<%#DataBinder.Eval(Container.DataItem, "Col1_Others")%>
</ItemTemplate>
<EditItemTemplate>

<asp:CheckBox
ID="cb1_VL" Text="VL" 
runat="server"
Checked=<%#DataBinder.Eval(Container.DataItem, "Col1_VL")%> />
<asp:CheckBox
ID="cb1_SL"  Text="SL" 
runat="server"
Checked=<%#DataBinder.Eval(Container.DataItem, "Col1_SL")%> />
<asp:CheckBox
ID="cb1_ML"  Text="ML" 
runat="server"
Checked=<%#DataBinder.Eval(Container.DataItem, "Col1_ML")%> />
<asp:CheckBox
ID="cb1_PH"  Text="PH" 
runat="server"
Checked=<%#DataBinder.Eval(Container.DataItem, "Col1_PH")%> />
<asp:CheckBox
ID="cb1_APH"  Text="APH" 
runat="server"
Checked=<%#DataBinder.Eval(Container.DataItem, "Col1_APH")%> />
<asp:CheckBox
ID="cb1_TOIL" Text="TOIL" 
runat="server"
Checked=<%#DataBinder.Eval(Container.DataItem, "Col1_TOIL")%> />
<asp:TextBox ID="tb1_Others" runat="server" Width="50" Text='<%#DataBinder.Eval(Container.DataItem, "Col1_Others") %>'></asp:TextBox>
</EditItemTemplate>
</asp:TemplateField>

............

</Columns>
</asp:GridView>    
</asp:Content>

Joe

Here is a way to set your TimePicker's value when GridView is in Edit-Mode:

GridView1_DataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs) Handles GridView1.RowDataBound
    If e.Row.RowType = DataControlRowType.DataRow AndAlso e.Row.RowState = DataControlRowState.Edit Then
        Dim row = DirectCast(e.Row.DataItem, DataRowView).Row
        Dim Col1_StartTime1 = DirectCast(e.Row.FindControl("Col1_StartTime1"), MKB.TimePicker)
        'set the TimePicker's Value here according to the Time-Value in the DataRow'
    End If
End Sub

Here is a way to get the values in RowUpdating:

Private Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs) Handles GridView1.RowUpdating
    Dim dt = CType(Session("dt"), DataTable)
    'Update the values.
    Dim row = GridView1.Rows(e.RowIndex)
    dt.Rows(row.DataItemIndex)("PostCode") = (CType((row.Cells(1).Controls(0)), TextBox)).Text
    dt.Rows(row.DataItemIndex)("StaffName") = (CType((row.Cells(2).Controls(0)), TextBox)).Text
    Dim Col1_StartTime1 = DirectCast(row.FindControl("Col1_StartTime1"),TimeSelector)
    dt.Rows(row.DataItemIndex)("WorkingTime") = Col1_StartTime1.Value
    '.....'

    'Bind data to the GridView control.
    BindData()
  End Sub