CODEWITHSUNDEEP
c#sqlasp.netssms
RMR
RMR

Reputation: 629

schedule for update date in asp.net

I want to make schedule that run after every 24 hours in asp.net (global.asax). I just want to check current date and date which are store into my database one by one. if date is less than current date then add 7 days into it.

I am just trying but without close the reader update is not possible. this kind of error shows me every time.

private static void Task()
{
    String CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
    using (SqlConnection con = new SqlConnection(CS))
    {
        con.Open();
        DateTime Newdate;
        String ct = DateTime.Now.ToString("dd/MM/yyyy");
        DateTime CT = DateTime.ParseExact(ct, "dd/MM/yyyy", CultureInfo.InvariantCulture);
        SqlCommand cmd1 = new SqlCommand("select Date from tblInsertDate", con);
        cmd1.ExecuteNonQuery();
        SqlDataReader rdr = cmd1.ExecuteReader();
        while (rdr.Read())
        {
            DateTime dt = DateTime.ParseExact(rdr["Date"].ToString(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
            int result = DateTime.Compare(dt, CT);
            if (result < 0)
            {
                Newdate = dt.AddDays(7);
                SqlCommand cmd2 = new SqlCommand("update tblInsertDate set Date=@Newdate", con);
                cmd2.Parameters.AddWithValue("@Newdate", Newdate);
                cmd2.ExecuteNonQuery();
            }
        }
    }
}

I make the schedule in global.asax with the help of timer.

Upvotes: 0

Views: 1570

Answers (1)

Anupam Sharma
Anupam Sharma

Reputation: 368

You have written update query using same connection object inside reader.read(). That means reader would have a open connected connection while reading the data and at the same time another command is trying to update data using same connection. Either you can fixed this by creating another connection object in same loop or same connection object after reader close. I have made a small change, Try use this:- , placed update query out of reader loop

private static void Task()
{
    String CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
    using (SqlConnection con = new SqlConnection(CS))
    {
        con.Open();
        DateTime Newdate;
        String ct = DateTime.Now.ToString("dd/MM/yyyy");
        DateTime CT = DateTime.ParseExact(ct, "dd/MM/yyyy", CultureInfo.InvariantCulture);
        SqlCommand cmd1 = new SqlCommand("select Date from tblInsertDate", con);
        cmd1.ExecuteNonQuery();
        SqlDataReader rdr = cmd1.ExecuteReader();
       List<DateTime> dates=new List<DateTime();
        while (rdr.Read())
        {
            DateTime dt = DateTime.ParseExact(rdr["Date"].ToString(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
            int result = DateTime.Compare(dt, CT);
            if (result < 0)
            {
                Newdate = dt.AddDays(7);
                dates.Add(Newdate);

            }
        }
        foreach(DateTime dt in dates)
        {
                SqlCommand cmd2 = new SqlCommand("update tblInsertDate set Date=@Newdate", con);
                cmd2.Parameters.AddWithValue("@Newdate", dt);
                cmd2.ExecuteNonQuery();
        }
    }
}

Upvotes: 2

Related Questions