Problem with updating the DOB value from Gridview

Question

I have problem with updating the dateofbirth value from gridview.

In my database table i have stored date of birth of person in a field like...Day, Month, Year. After storing i have displayed these values in gridview under a single field called DateOfBirth by using this query.

SELECT Id, Name, Address, Day + '-' + Month + '-' + Year AS DateOfBirth, 
    Phone, EmergencyContact, DateOfRegistration FROM Patient

It's working fine, displaying the DateOfBirth with DOB format. But when i am trying to update the DateOfBirth Contains from gridview its not updating where as other field in the table is updating correctly.

Here is my code:

<Columns>
    <asp:BoundField DataField="DateOfBirth" HeaderText="DateOfBirth" 
        SortExpression="DateOfBirth" /> 
</Columns>
<asp:SqlDataSource>
    InsertCommand="INSERT INTO [Patient] ([Name], [Address], [Day],[Month],
         [Year], [Phone], [EmergencyContact], [DateOfRegistration]) 
         VALUES (@Name, @Address, @Day, @Month, @Year, @Phone, 
         @EmergencyContact, @DateOfRegistration)
    ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"
    SelectCommand="SELECT Id, Name, Address, Day + '-' + Month + '-' + 
          Year AS DateOfBirth, Phone, EmergencyContact, DateOfRegistration
          FROM Patient
    UpdateCommand="UPDATE [Patient] SET [Name] = @Name, [Address] = @Address,
         [Day] = @Day, [Month]=@Month, [Year]= @Year, [Phone]=@Phone,
         [EmergencyContact]=@EmergencyContact, 
         [DateOfRegistration]=@DateOfRegistration 
         WHERE [Id] = @Id">
    <InsertParameters>
        <asp:Parameter Name="Day" Type="String" />
        <asp:Parameter Name="Month" Type="String" />
        <asp:Parameter Name="Year" Type="String" />
    </InsertParameters>
    <UpdateParameters>
        <asp:Parameter Name="Day" Type="String" />
        <asp:Parameter Name="Month" Type="String" />
        <asp:Parameter Name="Year" Type="String" />
    </UpdateParameters>
</asp:SqlDataSource>

My second problem is when i am selecting the date from calender control and putting it inside the TextBox then saving.After that when i am displaying the in gridview Date is showing correctly but along with this for all date, time is 12:00:00AM. For registrationdate type as taken DatTime for both table and StoredProcedure. Here is my code:

protected void 
CalendarRegistrationDate_SelectionChanged(object sender, EventArgs e)
{
    TxtRegistrationDate.Text = 
        CalendarRegistrationDate.SelectedDate.ToShortDateString();
    CalendarRegistrationDate.Visible = false;
}   

protected void BtnCalender_Click(object sender, EventArgs e)
{
    CalendarRegistrationDate.Visible = true;        
}

DateTime registrationdate = Convert.ToDateTime(TxtRegistrationDate.Text);

Answer 1

i. A good practice to store the date of birth in the db would be store it in date time format. When you bind to GridView Column, you can specify the formatting to be in the way you want to display.

The property you need to set is

DataFormatString = "dd-MMM-yyyy"

The above will display the date in the format you want. Modify your insert/update/select queries in so that the DateofBirth field is a single field of the type datetime.

ii. Where ever you want to display the date, format in the way you want to display.

eg: string date = DateTime.Now.ToString("dd-MMM-yyyy")