View Full Version : Gridview field gets updated w/1900 date

04-13-2007, 12:39 AM

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,

<asp:GridView ID="GridView2" runat="server" AutoGenerateColumns="False" AllowPaging="True" DataSourceID="SqlDataSource1" DataKeyNames="EmployeeID" Height="300px" PageSize="25">
<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">
<asp:DropDownList ID="DropDownList1" runat="server" DataTextField="Department"
DataValueField="deptID" SelectedValue='<%# Bind("deptID") %>' Width="190px" DataSourceID="sqlGridDept">
<asp:Label ID="Label1" runat="server" Text='<%# Eval("department") %>'></asp:Label>
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
<asp:TemplateField HeaderText="Manager" SortExpression="MgrID">
&nbsp;<asp:DropDownList ID="ddlManager" runat="server" Width="103px" DataSourceID="sqlGridMgr"
DataTextField= "Name" DataValueField="MgrID" SelectedValue='<%# Bind("MgrID") %>' >
<asp:Label ID="Label2" runat="server" Text='<%# Eval("Manager") %>'></asp:Label>
<asp:BoundField DataField="TerminationDate" HeaderText="TerminationDate" SortExpression="TerminationDate" />
<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: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">
<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" />

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


Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
' display all current employees

If Not IsPostBack Then

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)

' open connection

da.Fill(ds) ' should fill that ddl

Return ds

Catch exc As Exception
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

04-13-2007, 04:43 PM
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.

04-13-2007, 11:17 PM
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.