deb_holmes
asked on
ExecuteReader requires an open and available Connection. The connection's current state is closed
My datagrid has a large number of dropdown lists that are displayed when the user chooses to edit a row. Something about the procedure I've tried to add to generically populate each list is conflicting with the connection state. I can't see what so would appreciate help. Exceptoin thrown is below, as is the code of the procedure run on databind and the procedure that one calls for populating the dropdown lists.
Error occurs after the call loadDropDown(e, "pet_type", colPetType2, "ddPetType");
on the line: SqlDataReader reader = cmd.ExecuteReader();
Just as a note, at one point I tried passing the open conn in to the loadDropDown call rather than opening and closing a connection in the sub-proc but that had the same problem.
System.InvalidOperationExc eption was unhandled by user code
Message="ExecuteReader requires an open and available Connection. The connection's current state is closed."
Source="System.Data"
StackTrace:
at System.Data.SqlClient.SqlC onnection. GetOpenCon nection(St ring method)
at System.Data.SqlClient.SqlC onnection. ValidateCo nnectionFo rExecute(S tring method, SqlCommand command)
at System.Data.SqlClient.SqlC ommand.Val idateComma nd(String method, Boolean async)
at System.Data.SqlClient.SqlC ommand.Run ExecuteRea der(Comman dBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlC ommand.Run ExecuteRea der(Comman dBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlC ommand.Exe cuteReader (CommandBe havior behavior, String method)
at System.Data.SqlClient.SqlC ommand.Exe cuteReader ()
at Webkinz.MasterItems.loadDr opDown(Dat aGridItemE ventArgs e, String queryfield, Int32 col_index, String ddl_name) in C:\All\Visual Studio 2008\Projects\Webkinz\Webk inz\Master Items.aspx .cs:line 240
at Webkinz.MasterItems.gridIt ems_ItemDa taBound(Ob ject sender, DataGridItemEventArgs e) in C:\All\Visual Studio 2008\Projects\Webkinz\Webk inz\Master Items.aspx .cs:line 212
at System.Web.UI.WebControls. DataGrid.O nItemDataB ound(DataG ridItemEve ntArgs e)
at System.Web.UI.WebControls. DataGrid.C reateItem( Int32 itemIndex, Int32 dataSourceIndex, ListItemType itemType, Boolean dataBind, Object dataItem, DataGridColumn[] columns, TableRowCollection rows, PagedDataSource pagedDataSource)
at System.Web.UI.WebControls. DataGrid.C reateContr olHierarch y(Boolean useDataSource)
at System.Web.UI.WebControls. BaseDataLi st.OnDataB inding(Eve ntArgs e)
at System.Web.UI.WebControls. BaseDataLi st.DataBin d()
at Webkinz.MasterItems.loadGr id() in C:\All\Visual Studio 2008\Projects\Webkinz\Webk inz\Master Items.aspx .cs:line 166
at Webkinz.MasterItems.gridIt ems_Edit(O bject source, DataGridCommandEventArgs e) in C:\All\Visual Studio 2008\Projects\Webkinz\Webk inz\Master Items.aspx .cs:line 264
at System.Web.UI.WebControls. DataGrid.O nEditComma nd(DataGri dCommandEv entArgs e)
at System.Web.UI.WebControls. DataGrid.O nBubbleEve nt(Object source, EventArgs e)
at System.Web.UI.Control.Rais eBubbleEve nt(Object source, EventArgs args)
at System.Web.UI.WebControls. DataGridIt em.OnBubbl eEvent(Obj ect source, EventArgs e)
at System.Web.UI.Control.Rais eBubbleEve nt(Object source, EventArgs args)
at System.Web.UI.WebControls. Button.OnC ommand(Com mandEventA rgs e)
at System.Web.UI.WebControls. Button.Rai sePostBack Event(Stri ng eventArgument)
at System.Web.UI.WebControls. Button.Sys tem.Web.UI .IPostBack EventHandl er.RaisePo stBackEven t(String eventArgument)
at System.Web.UI.Page.RaisePo stBackEven t(IPostBac kEventHand ler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePo stBackEven t(NameValu eCollectio n postData)
at System.Web.UI.Page.Process RequestMai n(Boolean includeStagesBeforeAsyncPo int, Boolean includeStagesAfterAsyncPoi nt)
InnerException:
Error occurs after the call loadDropDown(e, "pet_type", colPetType2, "ddPetType");
on the line: SqlDataReader reader = cmd.ExecuteReader();
Just as a note, at one point I tried passing the open conn in to the loadDropDown call rather than opening and closing a connection in the sub-proc but that had the same problem.
System.InvalidOperationExc
Message="ExecuteReader requires an open and available Connection. The connection's current state is closed."
Source="System.Data"
StackTrace:
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at System.Data.SqlClient.SqlC
at Webkinz.MasterItems.loadDr
at Webkinz.MasterItems.gridIt
at System.Web.UI.WebControls.
at System.Web.UI.WebControls.
at System.Web.UI.WebControls.
at System.Web.UI.WebControls.
at System.Web.UI.WebControls.
at Webkinz.MasterItems.loadGr
at Webkinz.MasterItems.gridIt
at System.Web.UI.WebControls.
at System.Web.UI.WebControls.
at System.Web.UI.Control.Rais
at System.Web.UI.WebControls.
at System.Web.UI.Control.Rais
at System.Web.UI.WebControls.
at System.Web.UI.WebControls.
at System.Web.UI.WebControls.
at System.Web.UI.Page.RaisePo
at System.Web.UI.Page.RaisePo
at System.Web.UI.Page.Process
InnerException:
protected void gridItems_ItemDataBound(object sender, DataGridItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.EditItem)
{
string curr_val = e.Item.Cells[colItemType2].Text;
DropDownList ddl = null;
ddl = (DropDownList)e.Item.FindControl("ddItemType");
SqlConnection conn = new SqlConnection(c.getConnectionString());
using (conn)
{
conn.Open();
SqlCommand cmd = new SqlCommand("webkinzGetCategories", conn);
cmd.Parameters.Clear();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Clear();
SqlDataReader reader = cmd.ExecuteReader();
try
{
ddl.Items.Clear();
while (reader.Read())
{
ListItem li = new ListItem(reader["category"].ToString(),reader["category"].ToString());
if (curr_val == reader["category"].ToString())
li.Selected = true;
ddl.Items.Add(li);
}
}
finally
{
reader.Close();
}
}
conn.Close();
loadDropDown(e, "pet_type", colPetType2, "ddPetType");
loadDropDown(e, "theme", colTheme2, "ddTheme");
loadDropDown(e, "item type", colItemClass2, "ddClassification");
loadDropDown(e, "Source", colSource2, "ddlSource");
loadDropDown(e, "Gift Occasion", colGift2, "ddGift");
loadDropDown(e, "GEV Category", colGEVCategory2, "ddGEVCategory");
loadDropDown(e, "GEV Rule", colGEVRule2, "ddGEVRule");
TextBox txt = (TextBox)e.Item.FindControl("txtFileName");
curr_val = e.Item.Cells[colImage2].Text;
if (curr_val.Length < 5 || curr_val == " ") return;
txt.Text = curr_val;
}
}
protected void loadDropDown( DataGridItemEventArgs e, string queryfield, int col_index,string ddl_name)
{
SqlConnection conn = new SqlConnection(c.getConnectionString());
string curr_val = e.Item.Cells[col_index].Text;
DropDownList ddl = (DropDownList)e.Item.FindControl(ddl_name);
using (conn)
{
string queryString = "select [" + queryfield + "] from WebKinzMasterItems where [" + queryfield + "] is not null group by [" + queryfield + "] order by [" + queryfield + "]";
SqlCommand cmd = new SqlCommand(queryString, conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
SqlDataReader reader = cmd.ExecuteReader();
try
{
ddl.Items.Clear();
while (reader.Read())
{
ListItem li = new ListItem(reader[queryfield].ToString(), reader[queryfield].ToString());
if (curr_val == reader[queryfield].ToString())
li.Selected = true;
ddl.Items.Add(li);
}
}
finally
{
reader.Close();
}
}
conn.Close();
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER