My name
is
Jon Skeet

subtracting Dates and saving value in different column of database

Ok, so What i want to do is take Two dates, subtract them and insert their answer (in integers) to another column. Should be like 15 days and so on. The dates to be fetched are stored in the database. The answer needs to be in DAYS this is my gridview code:

<div class="gview"> 
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" HeaderStyle-HorizontalAlign="Center"  FooterStyle-HorizontalAlign="Center" CssClass="gview" DataKeyNames="PID" Width="613px">
        <Columns>
            <asp:BoundField DataField="PID" HeaderText="PID" SortExpression="PID" InsertVisible="False" ReadOnly="True" />
            <asp:BoundField DataField="Pname" HeaderText="Pname" SortExpression="Pname" />
            <asp:BoundField DataField="Gender" HeaderText="Gender" SortExpression="Gender" />
            <asp:BoundField DataField="Department" HeaderText="Department" SortExpression="Department" />
            <asp:BoundField DataField="Ward" HeaderText="Ward" SortExpression="Ward" />
            <asp:BoundField DataField="Bed" HeaderText="Bed" SortExpression="Bed" />
            <asp:BoundField DataField="Status" HeaderText="Status" SortExpression="Status" />
            <asp:BoundField DataField="AdmitDate" HeaderText="AdmitDate" SortExpression="AdmitDate" />
            <asp:BoundField DataField="DischargeDate" HeaderText="DischargeDate" SortExpression="DischargeDate" />
            <asp:BoundField DataField="NumOfDays" HeaderText="NumOfDays" SortExpression="NumOfDays" />
        </Columns>
        <FooterStyle HorizontalAlign="Center"></FooterStyle>
        <HeaderStyle HorizontalAlign="Center"></HeaderStyle>
    </asp:GridView>

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SMCConnectionString %>" SelectCommand="SELECT [PID], [Pname], [Gender], [Department], [Ward], [Bed], [Status], [AdmitDate], [DischargeDate], [NumOfDays] FROM [Patient]">
    </asp:SqlDataSource>
 </div>

This is my C# code:

protected void Button6_Click(object sender, EventArgs e)
{

    string str = "update Patient (Department, Ward, Bed, Status, AdmitDate, DischargeDate) set Department= @Department,Ward=@Ward,Bed=@Bed,Status=@Status,AdmitDate=@AdmitDate,DischargeDate=@DischargeDate   where PID = '" + TextBox3.Text + "'";
    cmd = new SqlCommand(str, con);
    con.Open();
    cmd.Parameters.AddWithValue("@Department", DropDownList4.SelectedItem.Text);
    cmd.Parameters.AddWithValue("@Ward", DropDownList3.SelectedItem.Text);
    cmd.Parameters.AddWithValue("@Bed", DropDownList1.SelectedItem.Text);
    cmd.Parameters.AddWithValue("@Status", DropDownList2.SelectedItem.Text);
    cmd.Parameters.AddWithValue("@AdmitDate", TextBox1.Text);
    DateTime.Parse(TextBox1.Text.ToString());
    cmd.Parameters.AddWithValue("@DischargeDate", TextBox2.Text);
    DateTime.Parse(TextBox2.Text.ToString());
    var diff = DateTime.Parse(TextBox2.Text.ToString()).Subtract(DateTime.Parse(TextBox1.Text.ToString()));
    cmd.Parameters.AddWithValue("@NumOfDays", diff);
    cmd.ExecuteNonQuery();
    con.Close();
    show();
    Response.Write("Record Updated");
}

Now when I click the button it generates the following error:

SqlDbType.Time overflow. Value '10.00:00:00' is out of range. Must be between 00:00:00.0000000 and 23:59:59.9999999.

I did quite a research and found out the idea that i have implemented. But it's no good so far. Any kind of help is greatly appreciated.

These lines:

 var diff = DateTime.Parse(TextBox2.Text.ToString()).Subtract(DateTime.Parse(TextBox1.Text.ToString()));
 cmd.Parameters.AddWithValue("@NumOfDays", diff);

set the value of the @NumOfDays parameter to a TimeSpan. Firstly, I'd recommend explicitly setting the data type you expect to use - it'll make it easier to find the error earlier.

Next, you say you want the value of the column to be a number of days, but you haven't said anywhere how you want the TimeSpan to be converted. You probably want to use TimeSpan.Days for this, which assumes you don't care about any sub-day information. So:

var diff = ...; // Code as before, but see notes. 
cmd.Parameters.Add("@NumOfDays", SqlDbType.Int).Value = diff.Days;

However, for computing diff:

Ideally, separate out your presentation layer from your storage layer. It's odd to deal with both controls and SQL in the same class, IMO.
I would suggest parsing with the expected format and culture. Ideally, use a date picker control on the client-side to avoid users having to type in dates at all; you can then ensure it will use a standard format (e.g. yyyy-MM-dd)
I would encourage you to rename your controls to indicate intent, rather than just a type name followed by a number.
If your users are typing in text, use TryParse or TryParseExact to cleanly handle user error.
You don't need to call ToString on the Text property of a TextBox - it's already a string
I would use the - operator for simplicity
I'd use separate local variables to make it easier to diagnose issues.

So if you do want to use a textbox, it would be something like:

DateTime start, end;
if (!DateTime.TryParseExact(startTextBox.Text, "yyyy-MM-dd",
                            CultureInfo.InvariantCulture, DateTimeStyles.None,
                            out start))
{
    // Invalid start - handle appropriately.
}
if (!DateTime.TryParseExact(endTextBox.Text, "yyyy-MM-dd",
                            CultureInfo.InvariantCulture, DateTimeStyles.None,
                            out end))
{
    // Invalid end - handle appropriately.
}
var diff = end - start;

Next, look at your SQL:

string str = "update Patient (Department, Ward, Bed, Status, AdmitDate, DischargeDate) set Department= @Department,Ward=@Ward,Bed=@Bed,Status=@Status,AdmitDate=@AdmitDate,DischargeDate=@DischargeDate   where PID = '" + TextBox3.Text + "'";

This has two important problems:

You're including the PID directly in the SQL. Don't do that. It's a serious potential security flaw (SQL Injection Attack). You're using parameters for everything else - why no the PID?
You're not using the NumOfDays parameter or mentioning the NumOfDays column, so obviously that's not going to be affected.

See more on this question at Stackoverflow