Dynamically update project_efforts columns based on 2 dates difference

Question

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 17-Apr-22 3:13am

Foody Tummy

Updated 20-Apr-22 2:28am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2022-04-20T02:28:00

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);