CODEWITHSUNDEEP
c#datareaderinvalidoperationexception
mani
mani

Reputation: 155

Autocomplete textbox in c#

I tried autocomplete text in c# and i tried this code,

try
{
   textBox1.AutoCompleteMode = AutoCompleteMode.Suggest;
   textBox1.AutoCompleteSource = AutoCompleteSource.CustomSource;
   AutoCompleteStringCollection col = new AutoCompleteStringCollection();
   sqlconn.Open();
   string query = "select id from cmp_det where id =" + textBox1.Text;
   SqlCommand command = new SqlCommand(query, sqlconn);
   SqlDataReader sdr = command.ExecuteReader();
   while (sdr.Read())
   {
       col.Add(sdr["Column_Name"].ToString());    
   }
   sdr.Close();
   textBox1.AutoCompleteCustomSource = col;
   sqlconn.Close();
}
catch(Exception ex)
{
    Console.WriteLine("exception=="+ex);
}

when ) execute the code the following error appears:

exception==System.InvalidOperationException: There is already an open DataReader associated with this Command which must be closed first.

Upvotes: 0

Views: 1140

Answers (3)

RakotVT
RakotVT

Reputation: 21

Try to replace your code:

SqlDataReader sdr = command.ExecuteReader();
while (sdr.Read())
{
   col.Add(sdr["Column_Name"].ToString());    
}
sdr.Close();

to this:

using(SqlDataReader sdr = command.ExecuteReader())
{
   while (sdr.Read())
   {
       col.Add(sdr.GetValue(0).ToString());    
   }
}

where 0 is the zero-based column ordinal of your query

Upvotes: 1

Bhupendra
Bhupendra

Reputation: 360

This is due to a change in the default setting for MARs. It used to be on by default and we changed it to off by default post RC1. So just change your connection string to add it back (add MultipleActiveResultSets=True to connection string).

Upvotes: 0

Trido
Trido

Reputation: 545

Would enabling MARS fix this? Adding "MultipleActiveResultSets=True" to your connection string should resolve that type of error.

http://msdn.microsoft.com/en-us/library/h32h3abf(v=vs.80).aspx

Upvotes: 0

Related Questions