ASP.Net: Insert null DateTime

Question

I'am using a SQLDatasource in combination with a Formview. When I want to insert or update a DateTime that has no value, I always get an error.

How do you insert a DateTime null.

My SQLDataSource

<asp:SqlDataSource ID="sqldsDetailOrder" runat="server" ConnectionString="<%$ ConnectionStrings:csBookStore %>"
    SelectCommand="SELECT 'AUTHOR' = tblAuthors.FIRSTNAME + ' ' + tblAuthors.LASTNAME, tblBooks.*, tblGenres.*, tblLanguages.*, tblOrders.* FROM tblAuthors INNER JOIN tblBooks ON tblAuthors.AUTHOR_ID = tblBooks.AUTHOR_ID INNER JOIN tblGenres ON tblBooks.GENRE_ID = tblGenres.GENRE_ID INNER JOIN tblLanguages ON tblBooks.LANG_ID = tblLanguages.LANG_ID INNER JOIN tblOrders ON tblBooks.BOOK_ID = tblOrders.BOOK_ID WHERE tblOrders.ID = @ID" 
    DeleteCommand="DELETE FROM [tblOrders] WHERE [ID] = @ID" 
    InsertCommand="INSERT INTO [tblOrders] ([NAME], [ADDRESS], [CITY], [PC], [DATE], [BOOK_ID], [COUNT], [AMOUNT], [DELIVERED], [DDATE], [PAID], [PDATE]) VALUES (@NAME, @ADDRESS, @CITY, @PC, @DATE, @BOOK_ID, @COUNT, @AMOUNT, @DELIVERED, @DDATE, @PAID, @PDATE)" 
    UpdateCommand="UPDATE [tblOrders] SET [NAME] = @NAME, [ADDRESS] = @ADDRESS, [CITY] = @CITY, [PC] = @PC, [DATE] = @DATE, [BOOK_ID] = @BOOK_ID, [COUNT] = @COUNT, [AMOUNT] = @AMOUNT, [DELIVERED] = @DELIVERED, [DDATE] = @DDATE, [PAID] = @PAID, [PDATE] = @PDATE WHERE [ID] = @ID">
    <SelectParameters>
        <asp:ControlParameter ControlID="gvOrdersAdmin" Name="ID" 
            PropertyName="SelectedValue" Type="Int32" />
    </SelectParameters>
    <DeleteParameters>
        <asp:Parameter Name="ID" Type="Int32" />
    </DeleteParameters>
    <UpdateParameters>
        <asp:Parameter Name="NAME" Type="String" />
        <asp:Parameter Name="ADDRESS" Type="String" />
        <asp:Parameter Name="CITY" Type="String" />
        <asp:Parameter Name="PC" Type="String" />
        <asp:Parameter DbType="DateTime" Name="DATE" />
        <asp:Parameter Name="BOOK_ID" Type="Int32" />
        <asp:Parameter Name="COUNT" Type="Int32" />
        <asp:Parameter Name="AMOUNT" Type="Decimal" />
        <asp:Parameter Name="DELIVERED" Type="Boolean" />
        <asp:Parameter DbType="DateTime" Name="DDATE" />
        <asp:Parameter Name="PAID" Type="Boolean" />
        <asp:Parameter DbType="DateTime" Name="PDATE" />
        <asp:Parameter Name="ID" Type="Int32" />
    </UpdateParameters>
    <InsertParameters>
        <asp:Parameter Name="NAME" Type="String" />
        <asp:Parameter Name="ADDRESS" Type="String" />
        <asp:Parameter Name="CITY" Type="String" />
        <asp:Parameter Name="PC" Type="String" />
        <asp:Parameter DbType="DateTime" Name="DATE" />
        <asp:Parameter Name="BOOK_ID" Type="Int32" />
        <asp:Parameter Name="COUNT" Type="Int32" />
        <asp:Parameter Name="AMOUNT" Type="Decimal" />
        <asp:Parameter Name="DELIVERED" Type="Boolean" />
        <asp:Parameter DbType="DateTime?" Name="DDATE" />
        <asp:Parameter Name="PAID" Type="Boolean" />
        <asp:Parameter DbType="DateTime" Name="PDATE" />
    </InsertParameters>
</asp:SqlDataSource>

Thanks Vincent

Does the database support null in that column?

Roman
– Roman

2010-05-23 14:50:34 +00:00
Commented May 23, 2010 at 14:50 — Roman
– Roman, Commented May 23, 2010 at 14:50

Roman Marusyk · Accepted Answer · 2017-09-28 09:05:10Z

1

Insert DBNull.Value, not just null

edited Sep 28, 2017 at 9:05

Roman Marusyk

24.8k27 gold badges83 silver badges127 bronze badges

answered May 23, 2010 at 14:55

TheGeekYouNeed

7,5412 gold badges29 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vinzcent Over a year ago

I don't think that I can choose what I insert, because I use a SQLDataSource.

Roman · Accepted Answer · 2010-05-23 14:51:46Z

0

In order to work with a null DateTime you need to wrap in a Nullable. Most common way of doing that is to declare it as a DateTime? (question mark is the syntax that makes it nullable).

answered May 23, 2010 at 14:51

Roman

20.3k6 gold badges73 silver badges85 bronze badges

2 Comments

Vinzcent Over a year ago

When I do this, I get the following error: Cannot create an object of type 'System.Data.DbType' from its string representation 'DateTime?' for the 'DbType' property.

Roman Over a year ago

@Vinzcent: Cen's right, you also need to deal with it as a DBNull.Value as opposed to a straight up null.

Collectives™ on Stack Overflow

ASP.Net: Insert null DateTime

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related