Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Rey
    Rey is offline
    New Coder
    Join Date
    Sep 2006
    Location
    Peoria, AZ
    Posts
    16
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Gridview field gets updated w/1900 date

    Howdy.

    I'm stumped...

    My problem is that on editing/updating the row for department, title or manager the termination date field gets updated to a 1900 date when the initial terminationdate field is null. However, if the terminationdate field has a valid date then it does not get updated.

    Have attempted to trap for change in termination date via the e.newvalues(3) in both the rowupdating and rowupdated events but neither had any entry.

    Problem surfaced as I was working my way through the grid, e.g. displaying the manager’s name in place of the manager’s employeeID number. I revised the UPDATE statement with a parameter for the termination date field and encountered the problem when after test updating an employee said employee was not displayed again in the grid on postback; had to then remove the TerminationDate IS NULL from the SELECT statement and found the field contents to contain a 1900 date.

    Would greatly appreciate any help on the subject as to what I may be doing incorrectly. My intent is to possibly add a calendar dropdown for the termination date field. I went with sqlDataObjects primarily to simplify the grid as I was using dropdownlists for department and manager fields. Would appreciate suggestions on whether using code instead of sqlDataObjects is potentially better from a code maintenance standpoint or web page performance viewpoint or whether it is a matter of preference similar to the multitude of opinions related to using datareaders instead of datasets.

    Thank you,
    Rey



    Code:
                    <asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" AllowPaging="True" DataSourceID="SqlDataSource1" DataKeyNames="EmployeeID" Height="300px" PageSize="25">
                       <Columns>
                          <asp:CommandField ShowEditButton="True" />
                          <asp:BoundField DataField="EmployeeID" HeaderText="EmployeeID" SortExpression="EmployeeID" ReadOnly="True" />
                          <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" ReadOnly="True" />
                          <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" ReadOnly="True" />
                          <asp:BoundField DataField="HireDate" HeaderText="HireDate" SortExpression="HireDate" ReadOnly="True" />
                          <asp:TemplateField HeaderText="Department" SortExpression="deptID">
                             <EditItemTemplate>
                                <asp:DropDownList ID="DropDownList1" runat="server"  DataTextField="Department"
                                   DataValueField="deptID" SelectedValue='<%# Bind("deptID") %>' Width="190px" DataSourceID="sqlGridDept">
                                </asp:DropDownList>
                             </EditItemTemplate>
                             <ItemTemplate>
                                <asp:Label ID="Label1" runat="server" Text='<%# Eval("department") %>'></asp:Label>
                             </ItemTemplate>
                          </asp:TemplateField>
                          <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
                          <asp:TemplateField HeaderText="Manager" SortExpression="MgrID">
                             <EditItemTemplate>
                                &nbsp;<asp:DropDownList ID="ddlManager" runat="server" Width="103px" DataSourceID="sqlGridMgr"  
                                DataTextField= "Name" DataValueField="MgrID" SelectedValue='<%# Bind("MgrID") %>' >                            
                                </asp:DropDownList>
                             </EditItemTemplate>
                             <ItemTemplate>
                                <asp:Label ID="Label2" runat="server" Text='<%# Eval("Manager") %>'></asp:Label>
                             </ItemTemplate>
                          </asp:TemplateField>
                          <asp:BoundField DataField="TerminationDate" HeaderText="TerminationDate" SortExpression="TerminationDate" />
                       </Columns>
                         <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
                         <RowStyle BackColor="#EFF3FB" />
                         <EditRowStyle BackColor="#E0E0E0" />
                         <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
                         <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
                         <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" HorizontalAlign="Left" />
                         <AlternatingRowStyle BackColor="White" />
                       
                    </asp:GridView>
           <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TestingConnectionString %>"
    
              SelectCommand="SELECT e.EmployeeID, e.FirstName, e.LastName, CONVERT(varchar(15), e.HireDate, 101) AS HireDate, e.deptID,d.department, e.Title, m.FirstName + ' ' + m.LastName AS Manager, CONVERT(varchar(15), e.TerminationDate, 101) AS TerminationDate, e.MgrID FROM Employees e join Departments d on e.deptID = d.deptID join Employees m on e.mgrid = m.employeeid WHERE  e.UserType = 0 ORDER BY e.LastName"
    
              UpdateCommand="UPDATE Employees
      SET Title = @Title, TerminationDate = @TerminationDate, deptID = @deptID, MgrID = @MgrID
      WHERE employeeID = @employeeID">
              <UpdateParameters>
                 <asp:Parameter Name="Title" />
                 <asp:ControlParameter ControlID="GridView2" Name="TerminationDate" PropertyName="SelectedValue" />
                 <asp:ControlParameter ControlID="GridView2" Name="deptID" PropertyName="SelectedValue" />
                 <asp:Parameter Name="employeeID" />
                 <asp:ControlParameter ControlID="GridView2" Name="MgrID" PropertyName="SelectedValue" />
              </UpdateParameters>
           </asp:SqlDataSource>
    
           <asp:SqlDataSource ID="sqlGridDept" runat="server" ConnectionString="<%$ ConnectionStrings:TestingConnectionString %>"
    
              SelectCommand="SELECT d.Department, d.deptID FROM Departments d join Employees e ON d.deptID = e.deptID GROUP BY d.department, d.deptid Order by d.Department"></asp:SqlDataSource>
    
           <asp:SqlDataSource ID="sqlGridMgr" runat="server" ConnectionString="<%$ ConnectionStrings:TestingConnectionString %>"
    
              SelectCommand="SELECT  FirstName + ' ' + LastName AS Name, employeeID MgrID from Employees where Management = 1"></asp:SqlDataSource>
        
        </div>
    
    
      Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
         ' display all current employees
    
          If Not IsPostBack Then
             GridView2.DataBind()
    
          End If
    
    
       End Sub
    
        Function LoadDepartmentDDL() As DataSet
          ' purpose: load text and value of department ddl in the grid
    
           Dim strSQL As String = String.Empty
           strSQL = "SELECT Department, deptID FROM DEPARTMENTS ORDER BY Department"
    
           Dim strConnDB As String = ConfigurationManager.ConnectionStrings("TestingConnectionString").ToString
    
           Dim sqlConn As New SqlConnection(strConnDB)
    
           Dim sqlCmd As New SqlCommand(strSQL, sqlConn)
           Dim ds As New DataSet("theDepartments")
           Dim da As New SqlDataAdapter(sqlCmd)
    
    
          Try
              ' open connection
              sqlConn.Open()
    
              da.Fill(ds)   ' should fill that ddl
    
             sqlConn.Close()
             Return ds
    
          Catch exc As Exception
             sqlConn.Close()
             txtError.Text = "Problem loading departments." & ControlChars.CrLf _
                                  & "Reason: " & exc.Message
             txtError.Visible = True
             Return ds
          End Try
    
       End Function
    
       Protected Sub GridView2_RowCancelingEdit(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCancelEditEventArgs) Handles GridView2.RowCancelingEdit
          Dim rowIndex As Integer = GridView2.EditIndex
          Dim row As GridViewRow = GridView2.Rows(rowIndex)
    
          ' Cell(1) contains employeeID in this grid display..
          txtError.Text = "Edits to Employee ID = " & GridView2.Rows(rowIndex).Cells(1).Text _
                                   & GridView2.Rows(rowIndex).Cells(2).Text & Space(1) _
                                   & GridView2.Rows(rowIndex).Cells(3).Text & " were cancelled"
          txtError.Visible = True
    
       End Sub
    
    
    
       Protected Sub GridView2_RowEditing(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewEditEventArgs) Handles GridView2.RowEditing
             Dim test As String = String.Empty
             Dim intRow As Integer = 0
             Dim intempID As Integer = 0
             Dim intEditingRow As Int16 = 0
    
             intRow = GridView2.EditIndex
    
             intEditingRow = e.NewEditIndex  ' row being edited
    
    
       End Sub
    
          Protected Sub GridView2_RowUpdated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdatedEventArgs) Handles GridView2.RowUpdated
             ' inform user update took
             Dim rowIndex As Integer = GridView2.EditIndex
             Dim row As GridViewRow = GridView2.Rows(rowIndex)
    
             txtError.Text = "Edits to Employee ID = " & GridView2.Rows(rowIndex).Cells(1).Text _
                                   & Space(1) & GridView2.Rows(rowIndex).Cells(2).Text & Space(1) _
                                   & GridView2.Rows(rowIndex).Cells(3).Text & " were successful"
    
       End Sub
    
       Protected Sub GridView2_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles GridView2.RowUpdating
          ' appears can't - least I can't - get value from grid while in this event so have to use oldvalues & newvalues to check...
          Dim strDeptID As String = String.Empty
          Dim intRowIndex As Integer = GridView2.EditIndex     ' getting row being edited NOT same as selected row...
    
          Dim strMgr As String = String.Empty   ' track mgr change
          Dim strTermDate As String = String.Empty
    
         GridView2.SelectedIndex = intRowIndex
    
          Dim theRow As GridViewRow = GridView2.Rows(intRowIndex)
    
          strDeptID = e.NewValues("deptID")
    
          strMgr = e.NewValues("MgrID")
          strTermDate = e.NewValues("TerminationDate")
    
        'If e.NewValues.Item(3).Year = 1900 Then
        '   e.NewValues.Item(3) = String.Empty
        'End If
    
       End Sub
    Last edited by vinyl-junkie; 04-13-2007 at 03:28 AM. Reason: Added code tags

  • #2
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    Stupid question, kinda, but what is the field defined as in the database, and what, if any, is the default value?
    Check your DB just to be sure it isn't the database itself changing NULL to new date() or equivalent.

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #3
    Rey
    Rey is offline
    New Coder
    Join Date
    Sep 2006
    Location
    Peoria, AZ
    Posts
    16
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Howdy.
    Thanks for replying.
    The field, TerminationDate, is a datetime field that allows nulls.
    Will see about adding an update method that is called when the update button is clicked (rowupdating) in order to trap what the sql statement contains.
    Again thanks for replying.
    Rey


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •