Automatically inserting datetime with insert

问题:

I have a radgrid with the setup below. I'm able to properly insert a new row into my table but the Time_Stamp column still shows as NULL. Datatype is datetime in the database. I've tried including the Time_Stamp in my Insert query but still get NULL on the other side.

<telerik:RadGrid ID="Grid_ValueCard" runat="server" DataSourceID="SqlValueCard" AutoGenerateEditColumn="True" AllowAutomaticUpdates="True" Width="750px"  Skin="WebBlue" AllowAutomaticInserts="True" CellSpacing="0" GridLines="None">
    <MasterTableView AutoGenerateColumns="False" AllowAutomaticUpdates="True" DataSourceID="SqlValueCard" CommandItemDisplay="Top">
        <CommandItemSettings ShowCancelChangesButton="True" ShowSaveChangesButton="True" />
        <Columns>
            <telerik:GridBoundColumn DataField="ProviderID" DataType="System.Int32" FilterControlAltText="Filter ProviderID column" HeaderText="ProviderID" SortExpression="ProviderID" UniqueName="ProviderID">
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="AdditionalReports" DataType="System.Byte" FilterControlAltText="Filter AdditionalReports column" HeaderText="AdditionalReports" SortExpression="AdditionalReports" UniqueName="AdditionalReports">
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="SurveyCompleted" DataType="System.Byte" FilterControlAltText="Filter SurveyCompleted column" HeaderText="SurveyCompleted" SortExpression="SurveyCompleted" UniqueName="SurveyCompleted">
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="UniqueReportRequests" DataType="System.Byte" FilterControlAltText="Filter UniqueReportRequests column" HeaderText="UniqueReportRequests" SortExpression="UniqueReportRequests" UniqueName="UniqueReportRequests">
            </telerik:GridBoundColumn>
            <telerik:GridBoundColumn DataField="Time_Stamp" DataType="System.DateTime" FilterControlAltText="Filter Time_Stamp column" HeaderText="Time_Stamp" SortExpression="Time_Stamp" UniqueName="Time_Stamp">
            </telerik:GridBoundColumn>
        </Columns>
    </MasterTableView>
</telerik:RadGrid>

Here is my SqlDataSource:

<asp:SqlDataSource ID="SqlValueCard" runat="server" ConnectionString="<%$ ConnectionStrings:RTMD_MSConnectionString %>" SelectCommand="SELECT TOP 1 ProviderID, AdditionalReports, SurveyCompleted, UniqueReportRequests, Time_Stamp FROM ProviderValueCard"
     InsertCommand="INSERT INTO [ProviderValueCard] ([ProviderID], [AdditionalReports], [UniqueReportRequests], [SurveyCompleted]) VALUES (@ProviderID, @AdditionalReports, @UniqueReportRequests, @SurveyCompleted)">
    <InsertParameters>
        <asp:Parameter Name="ProviderID" />
        <asp:Parameter Name="AdditionalReports" />
        <asp:Parameter Name="UniqueReportRequests" />
        <asp:Parameter Name="SurveyCompleted" />
    </InsertParameters>
</asp:SqlDataSource>

回答1:

You could set up your TIME_STAMP column to have a default constraint that would set the current date and time automatically when a new row is inserted:

ALTER TABLE dbo.ProviderValueCard
ADD CONSTRAINT DF_ProviderValueCard_Time_Stamp
   DEFAULT (SYSDATETIME()) FOR TIME_STAMP

Now, every time a row is inserted and no value is provided from the front-end, SQL Server will fill in the current date & time into TIME_STAMP automagically :-)