Click here to Skip to main content
15,943,077 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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:

ASP.NET
<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:
C#
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");
}


CSS
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.


ADDITIONAL INFO: The type of both columns that store Dates is set to DateTime and these are the values that i am inserting. prntscr.com/666tl8 (using a Calender). DataType of NumOfDays is int

any help is greatly appreciated!
Posted
Comments
Richard Deeming 17-Feb-15 13:35pm    
Your code is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

1 solution

Well before getting to the difference between the dates, you need to look at how you are populating your stored procedure parameters.

The stored proc is expecting DateTimes, you are passing strings. I cant imagine this would work, but even if it does its vulnerable to regional issues. Different places represent dates differently.

If you use something like a DatePicker control rather than a textbox, it will give you a nice region safe DateTime to work with rather than a string that could be anything.

Now for the difference part. Good SQL practice would not store the difference, you can calculate it on the fly. By storing it you leave the database vulnerable to bad data. Someone could update one of the dates or the difference and lead to inconsistency.

If you don't store it, you could have a view which provides it. In TSQL you can simply subtract one date from another to get the difference in days in floating point form, which you probably want to round up.
 
Share this answer
 
Comments
Sana Farooq (Sana Qureshi) 17-Feb-15 12:35pm    
I am using date time picker, when you click on those textboxes, date time picker appears. And how so? Can you please guide me a bit?
Rob Philpott 17-Feb-15 12:38pm    
Well you should be able to get the date as a datetime with this:

DateTime startDate = dateTimePicker1.Value;
Sana Farooq (Sana Qureshi) 17-Feb-15 12:44pm    
I am using AJAX calendar extender. Is that no good?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900