retrievin data through datareader

[kirtee2209]

Hi..I have a table named tblTask and another one named tblDaily_Task_Schedule. the relationshipis such that a task can have many daily_tasks. the primary key of tblTask is Task and the primary key of tblDaily_Task_Schedule is Daily_Task and its foreign key is Task (from tblTask). Each subtask in the tblDaily_Task_Schedule has a status which is set to completed automatically when 1 task has been completed. and the Task from the tblTask on the whole also has a status which I have named to completed, and if task is completed, it has to be updated to "Yes".

private void updatetaskstatus()
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Effort Tracker System\\Effort Tracker System\\ETS.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";

            
            SqlCommand cmd1 = new SqlCommand("SELECT Task FROM tblTask", con);

            con.Open();
            SqlDataReader alltasks = cmd1.ExecuteReader();
            try
                {
                while (alltasks.Read())
                    {
                    string comp = "Completed";
                    string tTask = (string)alltasks["Task"];

                    SqlCommand cmd2 = new SqlCommand("SELECT count(*) FROM tblDaily_Task_Schedule WHERE tblDaily_Task_Schedule.Task = @Task", con);
                    cmd2.Parameters.Add(new SqlParameter("@Task", tTask));
                    int counter = Convert.ToInt32(cmd2.ExecuteScalar());

                    SqlCommand cmd3 = new SqlCommand("SELECT count(*) FROM tblDaily_Task_Schedule WHERE tblDaily_Task_Schedule.Task = @Task AND tblDaily_Task_Schedule.Task_Status = '" + comp.ToString() + "'", con);
                    int counter2 = Convert.ToInt32(cmd3.ExecuteScalar());
                    
                    if (counter == counter2)
                        {
                        string ya = "Yes";
                        SqlCommand cmd4 = new SqlCommand("UPDATE tblTask SET Completed = @Completed WHERE Task = @Task", con);
                        cmd4.Parameters.Add(new SqlParameter("@Completed", ya));
                        cmd4.Parameters.Add(new SqlParameter("@Task", tTask));

                        try
                        {
                            //	Execute the query
                            cmd4.ExecuteNonQuery();
                        }
                        catch (Exception exc)
                        {
                            //	Catch any errors and show the error message
                        MessageBox.Show(exc.ToString());
                        }
                        }
                     }
                }
            catch(Exception)
                {
                }
            finally
                {
                    // close the reader
                 if ( alltasks!= null)
                    {
                        alltasks.Close();
                    }
                con.Close();
                } 
        }

I have used the above code, but its working though i dont get any error message. any help will be greatly appreciated.

[kirtee2209]

the above code is NOT working!

[abelLazm]

what are you doing in this specific code block?

[kirtee2209]

I am trying to count the number of records from tblDailyTaskSchedule where i have the foreign key Task. and doing another count of the same but where the Daily_Task Status is "Completed". if both count matches, means that all the Daily_Task belonging to that Task has been completed. if all subtask has been completed, i have to update the status of the Task itself.

[abelLazm]

Have you try executing all the sql commands on sql server ? are they executing fine?

[abelLazm]

in this line cmd2.Parameters.Add(new SqlParameter("@Task", tTask)); why are you giving it tTask? in line 15 you declared comp but never used it... what are @Task and @Completed

[Mitja Bonca]

I did repair the code, and it should work now:

private void updatetaskstatus()
        {
            using(SqlConnection con = new SqlConnection())
			{
				con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Effort Tracker System\\Effort Tracker System\\ETS.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
            
				SqlCommand cmd1 = new SqlCommand("SELECT Task FROM tblTask", con);
				con.Open();
				using(SqlDataReader alltasks = cmd1.ExecuteReader())
				{
					while (alltasks.Read())
					{
						string comp = "Completed";
						string tTask = (string)alltasks[0];

						SqlCommand cmd2 = new SqlCommand("SELECT count(*) FROM tblDaily_Task_Schedule WHERE " +
						"tblDaily_Task_Schedule.Task = @Task", con);
						cmd2.Parameters.Add("@Task", SqlDbType, Int). Value = tTask;
						int counter = Convert.ToInt32(cmd2.ExecuteScalar());

						SqlCommand cmd3 = new SqlCommand(
						"SELECT count(*) FROM tblDaily_Task_Schedule WHERE " +
						"tblDaily_Task_Schedule.Task = @Task AND " +
						"tblDaily_Task_Schedule.Task_Status = @TaskStatus" , con);
						cmd3.Parameters.Add("@TaskStatus", SqlDbType, VarChar, 50). Value = comp;
						int counter2 = Convert.ToInt32(cmd3.ExecuteScalar());
                    
						if (counter == counter2)
						{
							try
							{
								string ya = "Yes";
								SqlCommand cmd4 = new SqlCommand(
								"UPDATE tblTask SET Completed = @Completed WHERE Task = @Task", con);
								cmd4.Parameters.Add("@Completed", SqlDbType, VarChar, 50). Value = ya;
								cmd4.Parameters.Add("@Task", SqlDbType, Int). Value = tTask;                
								//	Execute the query
								cmd4.ExecuteNonQuery();
							}
							catch (Exception ex)
							{
								MessageBox.Show(ex.Message);
							}							
						}
					}
				}
			}
        }