Click here to Skip to main content
15,890,717 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
C#
//This Is my data What ever m using
  SqlDataReader reader;

            DateTime dt1 = Convert.ToDateTime(dateTimePicker1.Text);
            DateTime dt2 = Convert.ToDateTime(dateTimePicker2.Text);

            SqlCommand cmd = new SqlCommand();
            cmd.Connection = getConnection();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = sp_name;

            SqlParameter P1 = new SqlParameter();
            P1.Direction = ParameterDirection.Input;
            P1.SqlDbType = SqlDbType.DateTime;
            P1.ParameterName = "@FromDate";
            P1.Value = dt1;
            cmd.Parameters.Add(P1);

            SqlParameter P2 = new SqlParameter();
            P2.Direction = ParameterDirection.Input;
            P2.SqlDbType = SqlDbType.DateTime;
            P2.ParameterName = "@ToDate";
            P2.Value = dt2;
            cmd.Parameters.Add(P2);
         
            reader = cmd.ExecuteReader();

//I Got error here that "Invalid attempt to call Read when reader is close"

            while (reader.Read())
            {
              

                DateTime dt = (DateTime)reader["TimeStamp"];         
                int milliSeconds = dt.Millisecond;
                s = dt.ToString("dd-MM-yyyy HH:mm:ss.fff");
                checkedListBox1.Items.Add(s);
                
            }
            return 0;
         


Please Give me solution
Posted
Updated 21-Jul-14 1:35am
v2

Well, it looks like you are trying to create a new DataTable for each row in the result set. That sounds like a bad idea. My suspicion is that the Load method disposes of the reader so in the next iteration it complains, but that's just an educated guess.

Either way, you want to get the data out of the reader and close it as quickly as you can. Fill the datatable and use that.
 
Share this answer
 
Comments
Dipika Wani 21-Jul-14 7:12am    
m sorry I forget to give comment to datatable.M not using datatable...anywhere...sorry for that
Rob Philpott 21-Jul-14 7:26am    
There is nothing obviously wrong with the code if all reference to the DataTable are removed. Are you attempting to share the connection with two readers? That will cause problems if you are.

In general you should create a new connection for each call (the story goes that something called connection pooling is used which means overhead is minimal - not quite true in my experience).
Dipika Wani 21-Jul-14 7:33am    
please give solution to check reader is closed somewhere or not
like as we use
if (connect.State == ConnectionState.Closed)
{
connect.Open();
}

like this , Have we any solution for reader to chek
Rob Philpott 21-Jul-14 7:36am    
You don't want to go down that path. Are you attempting to execute multiple commands against the same SqlConnection?
Dipika Wani 21-Jul-14 7:42am    
hye I got my solution...I delete all this stuff unneed datatable objects.
Thak u
Hi Dipika,

You can use Close method for SqlDataReader object to close the current datareader, and this should be very next when you complete your process.

e.g
reader.Close();



in the above comment you mention to check
if(connect.State == ConnectionState.Closed)
{
connect.Open();
}

could you please let me know object "connect" is the object for your SqlConnection class. if so then you are doing right checking for connection.

to close the current open connection for SqlConnection Class use

if(connect.State== ConnectionState.Open)
{
connect.Close();
}
 
Share this answer
 
v2

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