Click here to Skip to main content
15,359,217 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hello Team,

I need help in forming logic to update the project_efforts column based on currentdate and enddate difference.
I am able to calculate efforts and store in table but as days pass by I want to decrement the days displayed and update same in sql table.

Requirement - On Page load the gridview table should be updated with project_status based on currentdate condition in SQL table - This is successfully achievable

2nd Requirement - Along with status update the efforts date should decrement based on currentdate and enddate difference calculation - NEED HELP!

What I have tried:

Below is my code:
<pre lang="C#">if (!Page.IsPostBack)
            {
                var currentdate = DateTime.Today.ToString("dd/MM/yyyy");
                SqlConnection con = new SqlConnection(strcon);
                con.Open();
                SqlCommand cmd = new SqlCommand("Update project_table SET (project_status = @projectstatus, project_efforts=@project_efforts) where project_startdate =  '" + currentdate + "' ", con);
                cmd.Parameters.AddWithValue("@projectstatus", "Started");
               cmd.Parameters.AddWithValue("@project_efforts", result);
                cmd.ExecuteNonQuery();
                               
               timecal(project_startdate, project_enddate);
//gives error as parameters are not defined but unable to understand how to achieve currentdate and enddate value in parameters.

                GridView1.DataSource = GetDataFromDB();
                GridView1.DataBind();
                con.Close();
            }


Timecal Method:
public string result { get; set; }
public void timecal(string startdate, string enddate)
{
    TimeSpan ts = DateTime.Parse(enddate) - DateTime.Parse(startdate);
    string lbl = "";
    if (ts.Days > 0)
    {

        lbl = string.Format("{0:%d}", ts);
    }
    else
    {
        lbl = string.Format("{0:%d}", ts);
    }
    result = lbl;
}
Posted
Updated 20-Apr-22 2:28am

1 solution

Instead of storing this on the database each day I would calculate this each time I am displaying the data, and only store the effort when the project is closed. You do not need a separate function for this in your c# code - you can do as part of the SQL statement e.g. as in this demo
SQL
declare @demo table (item int identity(1,1), startdate date);
insert into @demo(startdate) values
('2022-01-13'), ('2022-04-01');

SELECT item, startdate, DATEDIFF(DAY, startdate, GETDATE())
from @demo;
This will give the difference in days from the start date of each row up to but not including today (or not including the start date depending on how you view it).
1	2022-01-13	97
2	2022-04-01	19
Another point, congratulations on using a Parameterized Query, but why do you also use string concatenation? What is wrong with
SQL
SqlCommand cmd = new SqlCommand("Update project_table SET (project_status = @projectstatus, project_efforts=@project_efforts) where project_startdate =  @currentDate", con);
cmd.Parameters.AddWithValue("@projectstatus", "Started");
cmd.Parameters.AddWithValue("@project_efforts", result);
cmd.Parameters.AddWithValue("@currentdate", currentdate);
   

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