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
– marc_s, Commented Oct 18, 2018 at 5:01
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
– Mohsin Mehmood, Commented Oct 18, 2018 at 5:02
Unrelated tip: graduation_date implies you should be using the Sql type date rather than datetime. — Richardissimo
– Richardissimo, Commented Oct 18, 2018 at 5:50
@MohsinMehmood There is no code in the code behind. The bind happens with the FormView using a DataSource in ASPX page. — oliverkiss.com
– oliverkiss.com, Commented Oct 18, 2018 at 14:07

Gauravsa · Accepted Answer · 2018-10-18 05:18:40Z

1

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" />

answered Oct 18, 2018 at 5:18

Gauravsa

6,6382 gold badges25 silver badges33 bronze badges

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

3 Comments

oliverkiss.com Over a year ago

Got the following error: Could not find control 'formView$txtGradDate' in ControlParameter 'graduation_date'.

oliverkiss.com Over a year ago

Got the ID in the HTML source: ctl00$ContentPlaceHolder$ctl01$frmStudents$txtGradDate THIS WORKED. Is there anyway I can continue to reference the control by it's original name (txtGradDate)? I'm going to get into problems if I keep changing parent containers...

oliverkiss.com Over a year ago

Also, why can a regular asp:parameter not work in this case? I have other textboxes that are working correctly. Is it because it's a date?

Habeeb · Accepted Answer · 2018-10-18 05:07:52Z

1

You need to take the value of graduation_date field from the TextBox. The value can be fetched and added to the list of UpdateParameters using ControlParameter

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

<UpdateParameters>
....
<asp:ControlParameter Name="graduation_date" ControlID="txtGradDate" Type="String" PropertyName="Text" />
</UpdateParameters>

answered Oct 18, 2018 at 5:07

Habeeb

8,0751 gold badge33 silver badges34 bronze badges

1 Comment

oliverkiss.com Over a year ago

The Type of String failed, but when I changed it to DateTime it worked. Also, I needed to get the full id of the control though the HTML source, txtGradDate didn't work. Any way to retain this name?

Collectives™ on Stack Overflow

Issue saving TextBox data as DateTime to SQL Database

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related