Click here to Skip to main content
15,860,972 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i have problem when use executereader

i have table1:

id | date
__________
1 |27/1/2011
2 |28/1/2011
1 |5/8/2011
2 |6/9/2011
_______________

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["racon"].ToString());
           SqlCommand cmd = new SqlCommand("SELECT id,MAX(date) as enddate from table1 group by id", con);


when I get values in gridview i get these values

id|date
___________
1 |5/8/2011
2 |6/9/2011
____________

when use following code..it updated user(1) and not updated user(2) note:"datetime.now=10/10/2011"

 DateTime datenow = new DateTime();
            datenow = datetime.Now;
con.Open();
            SqlDataReader reader =cmd.ExecuteReader();
                  
            if (reader.Read())
            {
                DateTime enddte = new DateTime();
                enddte = Convert.ToDateTime(reader["enddate"]);
                             
                if (DateTime.Compare(datenow,enddte)>0)
                {
                    string id = Convert.ToString(reader["id"]);
                    string sql = "update table2 set type='expired' where id='" + id + "'";
                    SqlCommand cmd1 = new SqlCommand(sql, con);
                    reader.Dispose();
                    cmd1.ExecuteNonQuery();
                    con.Close();
                }



            }
Posted

Reader reads a single record at a time so if you want to handle all the records returned by the reader, you must use the Read method in a loop. Something like
while (reader.Read()) { ... }
 
Share this answer
 
Comments
#realJSOP 6-Apr-11 15:40pm    
5 - Proposed as answer
Wendelius 6-Apr-11 15:52pm    
Thank you :)
Espen Harlinn 6-Apr-11 17:26pm    
Essential, nice nad simple - 5ed!
You are using an if statement rather than a loop. Change your code to look something like this so that you cover all of the results and clean up the connection, reader and command objects properly.
DateTime datenow = DateTime.Now;
using(SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["racon"].ToString()))
{
   con.Open();
   using(SqlCommand cmd = new SqlCommand("SELECT id,MAX(date) as enddate from table1 group by id", con))
   {
      using(SqlDataReader reader = cmd.ExecuteReader())
      {
         while(reader.Read())
         {
            DateTime enddate = Convert.ToDateTime(reader["enddate"]);

            if(DateTime.Compare(datenow,enddate)>0)
            {
               string id = Convert.ToString(reader["id"]);
               string sql = "update table2 set type='expired' where id='" + id + "'";
               using(SqlCommand cmd1 = new SqlCommand(sql, con))
               {
                  cmd1.ExecuteNonQuery();
               }
            }
         }
      }
   }
}
 
Share this answer
 
v3
Comments
shms_rony 6-Apr-11 16:06pm    
thank you for this perfect answer but i still have the problem
user (1) updated and user(2) not !!!
Espen Harlinn 6-Apr-11 17:29pm    
while(reader.Read()) allows you to handle more than one result. Try setting a breakpoint and executing under the debugger.
Espen Harlinn 6-Apr-11 17:28pm    
Good reply :)
Hi,

This is in continuation to Kythen's answer. Move out this code block from the loop.



C#
string sql = "update table2 set type='expired' where id='" + id + "'";
using(SqlCommand cmd1 = new SqlCommand(sql, con))
{
   cmd1.ExecuteNonQuery();
}


Instead just collect the ids in the loop and execute the query outside the loop.

C#
//have a string builder instance before the loop
StringBuilder sb=new StringBuilder();

//Inside the loop just append the ids. Take care the last id no need to append a comma.
sb.append(id); sb.append(",");

//outside the loop use like

string sql=@"UPDATE table2 SET type='expired' WHERE id IN ("+sb.ToString()+")";


Then execute it using the nonquery. So you won't be chatty with the sql server.
 
Share this answer
 
v2
Comments
shms_rony 6-Apr-11 17:22pm    
thank you for answer

but no user updated :(
first method updated one user only but your method updated 0 user
Albin Abel 7-Apr-11 2:03am    
Debug and check your are receiving the ids correctly. I given the strategy for better performance. If you are getting the ids correctly then no point to talk about one user or two user. The update syntax I given will work. But if you say one user updated that mean no error with the updating query. Debug, you will get the answer. Check how many ids pass this condition if(DateTime.Compare(datenow,enddate)>0). Could you try?
Espen Harlinn 6-Apr-11 17:30pm    
Good idea :)
Albin Abel 7-Apr-11 2:04am    
Thanks Espen Harlinn
shms_rony 6-Apr-11 17:38pm    
I HAVE another problem that one user updated when global.asax out bin folder and get message (debug, close program) and worked well
what can i do for this message

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