GridView Hidden DateTime Column date conversion

babuman · February 1st, 2007, 06:22 PM

Hi,

I have a GridView and a SqlDataSource controls on a page. The SqlDataSource object uses stored procedures to do the CRUD (create, update, delete) operations. The Table has three columns, one of which - "Modified" of type DateTime - is used to check the concurrency of the row for edits. This column is hidden on the Grid since it should not be edited by the user. In order to access the value of this field, I have created a template column and assigned the "Modified" column to it. The Update Stored Procedure requires the original value of this field to do the cocurrency checks, the last modified datetime. Everything seems to work fine except that the datetime value that is retrieved from the table and held in the template column - as a text field - does not match the "Modified" column in the table when the Update stored procedure receives it. Obviously, when I store the "Modified" column value in the template field's text property, somehow it looses the information partially. In other words, may be, it is dropping the milli seconds. How can I make these two values compare properly. I am pasting my page code, the table structure and the Update stored procedure here. Can somebody please help me figure this one out.

Babu.

Table:
CREATE TABLE [dbo].[ProperNames](
    [PKId] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_ProperNames_FirstName] DEFAULT ('Unknown'),
    [LastName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_ProperNames_LastName] DEFAULT ('Unknown'),
    [Modified] [datetime] NOT NULL CONSTRAINT [DF_ProperNames_Modified] DEFAULT (getdate()),
CONSTRAINT [PK_ProperNames] PRIMARY KEY CLUSTERED
(
    [PKId] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

ALTER PROCEDURE [dbo].[ProperNamesUpdate]
(
    @PKId numeric output,
    @FirstName [varchar](50),
    @LastName [varchar](50),
    @Modified [varchar](50)
)
AS
UPDATE ProperNames SET FirstName=@FirstName, LastName=@LastName,Modified=getdate()
WHERE PKId = @PKId and modified = @Modified

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
          <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="PKId"
             DataSourceID="SqlDataSource1">
             <Columns>
                 <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
                 <asp:BoundField DataField="PKId" HeaderText="PKId" InsertVisible="False" ReadOnly="True"
                     SortExpression="PKId" />
                 <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" />
                 <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" />
                 <asp:BoundField DataField="Modified" HeaderText="Modified" SortExpression="Modified"
                     Visible="False" />
                 <asp:TemplateField Visible="False">
                 <ItemTemplate>
                    <asp:Label ID="lblModified" runat="server" Text='<%# Eval("Modified") %>' />
                 </ItemTemplate>
                 </asp:TemplateField>
             </Columns>
         </asp:GridView>
     </div>
         <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DataNozzleDevConnectionString %>"
             DeleteCommand="ProperNamesDelete" DeleteCommandType="StoredProcedure" InsertCommand="ProperNamesInsert"
             InsertCommandType="StoredProcedure" SelectCommand="ProperNamesSelectAll" SelectCommandType="StoredProcedure"
             UpdateCommand="ProperNamesUpdate" UpdateCommandType="StoredProcedure" OnUpdating="SqlDataSource1_Updating">
             <DeleteParameters>
                 <asp:Parameter Name="PKId" Type="Decimal" />
             </DeleteParameters>
             <UpdateParameters>
                 <asp:Parameter Direction="InputOutput" Name="PKId" Type="Decimal" />
                 <asp:Parameter Name="FirstName" Type="String" />
                 <asp:Parameter Name="LastName" Type="String" />
                 <asp:Parameter Name="Modified" Type="String" />
             </UpdateParameters>
             <InsertParameters>
                 <asp:Parameter Direction="InputOutput" Name="PKId" Type="Decimal" />
                 <asp:Parameter Name="FirstName" Type="String" />
                 <asp:Parameter Name="LastName" Type="String" />
             </InsertParameters>
         </asp:SqlDataSource>
    </form>
</body>
</html>

protected void SqlDataSource1_Updating(object sender, SqlDataSourceCommandEventArgs e)
{
    GridViewRow gr = GridView1.Rows[GridView1.EditIndex];
    DateTime modified = Convert.ToDateTime(((Label)gr.FindControl("lblModi fied")).Text);
    e.Command.Parameters["@Modified"].Value = modified;
}