Issue saving TextBox data as DateTime to SQL Database

Question

Basically, I am using a FormView with ItemTemplate & EditItemTemplate controls. Loading the data works fine (binding through DataSource), when I click edit (going to EditItemTemplate controls now), it works fine, but when I click save I receive the following error:

Cannot insert the value NULL into column 'graduation_date', table 'dyswis.dbo.tbl_students'; column does not allow nulls. UPDATE fails

I don't understand why it's null, when there is a value in the text box. The other form fields work fine. I suspect it has something to do with how the date is being formatted, or even the text mode.

ASP.NET:

<asp:TextBox ID="txtGradDate" 
     Text='<%#((DateTime)Eval("graduation_date")).ToString("yyyy-MM-dd") %>' 
     CssClass="form-control" TextMode="Date" runat="server"></asp:TextBox>

Datasource:

<UpdateParameters>
    <asp:Parameter Name="id" Type="Int16" />
    <asp:Parameter Name="email" Type="String" />
    <asp:Parameter Name="first_name" Type="String" />
    <asp:Parameter Name="last_name" Type="String" />
    <asp:Parameter Name="university" Type="String" />
    <asp:Parameter Name="program" Type="String" />
    <asp:Parameter Name="student_number" Type="String" /><
    <asp:Parameter Name="graduation_date" Type="DateTime" />
    <asp:Parameter Name="student_card_image_name" Type="String" />
    <asp:Parameter Name="id_card_image_name" Type="String" />
</UpdateParameters>

SQL Server stored procedure:

CREATE PROCEDURE [dbo].[student_update]
    @id INT,
    @university VARCHAR(200),
    @program VARCHAR(200),
    @student_number VARCHAR(50),
    @graduation_date DATETIME,
    @student_card_image_name VARCHAR(500) = NULL,
    @id_card_image_name VARCHAR(500) = NULL
AS
    UPDATE tbl_students 
    SET university = @university, 
        program = @program, 
        student_number = @student_number, 
        graduation_date = @graduation_date 
    WHERE 
        user_id = @id

SQL Server table definition:

graduation_date DATETIME

If you post code, XML or data samples, PLEASE highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it! — marc_s
I don't see any code for binding update parameters to control values. If you do it in code behind please share that code. — Mohsin Mehmood
Unrelated tip: graduation_date implies you should be using the Sql type date rather than datetime. — Richardissimo
@MohsinMehmood There is no code in the code behind. The bind happens with the FormView using a DataSource in ASPX page. — oliverkiss.com

Gauravsa Gauravsa · Accepted Answer · 2018-10-18T05:18:40

Need to reference the filed through a ControlParameter like below:

If your FormView id is "formView" and textBox id is txtGradDate.

<asp:ControlParameter Name="graduation_date" ConvertEmptyStringToNull="true" Type="DateTime" ControlID="formView$txtGradDate" PropertyName="Text" />

Issue saving TextBox data as DateTime to SQL Database

2 Answers