Setting default values in a listview insert

RichardL1969 · July 9th, 2012, 08:21 AM

I have a comments section on my website which allows logged in users to add short comments.

Comments should write to a table called "News", which is used to store a variety of content for the site.

I use a textbox to allow the user to enter into the column "Summary", and I want the Date, NewsCategory and SubmittedBy columns to have automatic values.

I've tried to do this in the code behind

Code:

    protected void CommentSqlDataSource_Inserting(object sender, SqlDataSourceCommandEventArgs e)
    {
        CommentSqlDataSource.InsertParameters["Date"].DefaultValue = DateTime.Now.ToString();
        CommentSqlDataSource.InsertParameters["NewsCategory"].DefaultValue = "8"; // 8 is Id for a comment
        CommentSqlDataSource.InsertParameters["SubmittedBy"].DefaultValue = User.Identity.Name.ToString();
    }

I get and error though, saying "Cannot insert the value NULL into column 'Date', table 'C:\BEGASPNET\MIDDLERIPSLEY\APP_DATA\ASPNETDB.MDF. dbo.News'; column does not allow nulls. INSERT fails.
The statement has been terminated."

My page has the code...

Code:

    <asp:SqlDataSource ID="CommentSqlDataSource" runat="server" 
        ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
        DeleteCommand="DELETE FROM [News] WHERE [NewsId] = @original_NewsId" 
        InsertCommand="INSERT INTO [News] ([Date], [NewsCategory], [Headline], [Summary], [Main], [SubmittedBy], [Approved], [PictureUrl]) VALUES (@Date, @NewsCategory, @Headline, @Summary, @Main, @SubmittedBy, @Approved, @PictureUrl)" 
        OldValuesParameterFormatString="original_{0}" 
        SelectCommand="SELECT * FROM [News]" 
        UpdateCommand="UPDATE [News] SET [Date] = @Date, [NewsCategory] = @NewsCategory, [Headline] = @Headline, [Summary] = @Summary, [Main] = @Main, [SubmittedBy] = @SubmittedBy, [Approved] = @Approved, [PictureUrl] = @PictureUrl WHERE [NewsId] = @original_NewsId" 
        oninserting="CommentSqlDataSource_Inserting">

        <DeleteParameters>
            <asp:Parameter Name="original_NewsId" Type="Int32" />
        </DeleteParameters>
        <InsertParameters>
            <asp:Parameter DbType="Date" Name="Date" />
            <asp:Parameter Name="NewsCategory" Type="Int32" />
            <asp:Parameter Name="Headline" Type="String" />
            <asp:Parameter Name="Summary" Type="String" />
            <asp:Parameter Name="Main" Type="String" />
            <asp:Parameter Name="SubmittedBy" Type="String" />
            <asp:Parameter Name="Approved" Type="Boolean" />
            <asp:Parameter Name="PictureUrl" Type="String" />
        </InsertParameters>
        <UpdateParameters>
            <asp:Parameter DbType="Date" Name="Date" />
            <asp:Parameter Name="NewsCategory" Type="Int32" />
            <asp:Parameter Name="Headline" Type="String" />
            <asp:Parameter Name="Summary" Type="String" />
            <asp:Parameter Name="Main" Type="String" />
            <asp:Parameter Name="SubmittedBy" Type="String" />
            <asp:Parameter Name="Approved" Type="Boolean" />
            <asp:Parameter Name="PictureUrl" Type="String" />
            <asp:Parameter Name="original_NewsId" Type="Int32" />
        </UpdateParameters>

    </asp:SqlDataSource>
    <asp:LoginView ID="CommentLoginView" runat="server" EnableViewState="false"  >
        <AnonymousTemplate>
            <asp:Label ID="CommentsLogInLabel" runat="server" CssClass="menuboxes" Text="You need to be logged in to add comments" />
            <br />
            <br />
            Clich here to <a href="~/Account/Login.aspx" ID="CommentLoginStatus" runat="server" class="menuboxes">Log In</a> 
        </AnonymousTemplate>
        <LoggedInTemplate>
           <asp:Label ID="CommentLabel" runat="server" Text="Use the form below to add a comment.<br /><br />Your comments will appear on the Home Page." CssClass="NewsSummary" />
            <br />
            <asp:ListView ID="LogginInListView" runat="server" DataSourceID="CommentSqlDataSource" 
                DataKeyNames="NewsId" InsertItemPosition="LastItem">

                <EmptyDataTemplate>
                    <span>No comments.</span>
                </EmptyDataTemplate>
                <InsertItemTemplate>
                  <span style="">
                    <br />
                    Your Comment:
                    <br />
                    <asp:TextBox ID="SummaryTextBox" runat="server" Text='<%# Bind("Summary") %>'  TextMode="MultiLine" Width="300px" />
                    <br />
                    <asp:Button ID="InsertButton" runat="server" CommandName="Insert"  Text="Insert" />
                    <asp:Button ID="CancelButton" runat="server" CommandName="Cancel"  Text="Clear" />
                    <br />
                    <br />
                  </span>
                </InsertItemTemplate>
                <ItemTemplate>
                    <span style=""></span>
                </ItemTemplate>
                <LayoutTemplate>
                    <div ID="itemPlaceholderContainer" runat="server" style="">
                        <span runat="server" id="itemPlaceholder" />
                    </div>
                    <div style="">
                    </div>
                </LayoutTemplate>

            </asp:ListView>

        </LoggedInTemplate>
     </asp:LoginView>

Any ideas?

guenfire · July 12th, 2012, 01:25 PM

Your issue is most likely related to the fact that you have the InsertParameter on the data source specified as a DbType of date, but your specifiying the insert parameter in code with a ToString() value. Why the mismatch?

Frankly, I take the easiest route in cases like this. I would just skip the insert statement of that date altogether, and let the database do the work by setting the default value on the database field with the (GETDATE()) method.