Close DataReader from TableAdapter

J

joe_pool_is

Well-known member
Joined
Jan 18, 2004
Messages
451
Location
Texas
Using C# 2005 with SQL Server 2000:

Im getting this error:
There is already an open DataReader associated with this Command which must be closed first.
Click to expand...
The solution on forums.microsoft.com is to add "MultipleActiveResultSets=True" for SQL Server 2005.

Since I only have SQL Server 2000, the solution appears to be that I need to close my DataReader.

My question is, How is a DataReader closed in the following code?
Code: 
string fmt = [COLOR=red]"SELECT DISTINCT [{0}] FROM dbo."[/COLOR] + employeeInfo
   + [COLOR=red]" WHERE [GROUP]="[/COLOR] + cboGroup.Text + [COLOR=red]""[/COLOR];
string[] strCbo = { [COLOR=red]"DEPT"[/COLOR], [COLOR=red]"JOBTITLE"[/COLOR], [COLOR=red]"SHIFT"[/COLOR] };
cboDept.Items.Clear();[COLOR=green] // clear ComboBox Items on form[/COLOR]
cboTitle.Items.Clear();[COLOR=#008000] // clear ComboBox Items on form[/COLOR]
cboShift.Items.Clear();[COLOR=#008000] // clear ComboBox Items on form[/COLOR]
for (int i = 0; i < 3; i++) {
  string sql = string.Format(fmt, strCbo[i]);
  try {
    DataSet ds = new DataSet(strCbo[i]);
    [COLOR=green]// TableAdapter taEmpInfo[/COLOR]
    SqlDataAdapter da = new SqlDataAdapter(sql, taEmpInfo.Connection);
    da.Fill(ds, strCbo[i]);
    foreach (DataRow dr in ds.Tables[strCbo[i]].Rows) {
      string item = dr.ItemArray.GetValue(0).ToString().Trim();
      if (item != [COLOR=red]""[/COLOR]) {
        if (i == 0) cboDept.Items.Add(item);
        else if (i == 1) cboTitle.Items.Add(item);
        else if (i == 2) cboShift.Items.Add(item);
      }
    }
  } catch (Exception err) {
    Console.WriteLine(err.Message);
  }
}
 
Are you using a DataReader anywhere else in your code as I cant see any explicit mention of them in that snippet?
If so you are better off making sure that it is closed in the routine that uses it.
 
Ok. Found it.

When the form was loading, it was filling the TableAdapters, which were bound to the DataGridView.

Further, whenever data is selected on the DataGridView, I was populating my ComboBoxes with that information.

Fix was to add a simple Boolean value that gets set to true only after the data has been loaded. Now the ComboBox updating routine only runs if the value is true.
 
Back
Top