[RESOLVED] Getting error that connection to database is already open...

[HardD99]

ok im trying to connect to a database and then update it from a datagrid. when i start debugging i instantly get an error telling me that the connection is already open, so if i take the code out where i am opening the connection it gives me an error saying connection is closed, lol. i have highlighted in yellow where the error is comming up. please help

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Configuration;

namespace Database
{
	/// <summary>
	/// Summary description for WebForm1.
	/// </summary>
	public class WebForm1 : System.Web.UI.Page
	{
		protected System.Web.UI.WebControls.Button Button1;
		protected System.Web.UI.WebControls.Button Button2;
		protected System.Web.UI.WebControls.RadioButton RadioButton1;
		protected System.Web.UI.WebControls.RadioButton RadioButton2;
		protected System.Web.UI.WebControls.Label lblName;
		protected System.Web.UI.WebControls.TextBox TextBox2;
		protected System.Web.UI.WebControls.Button btnProcess;
		protected System.Web.UI.WebControls.TextBox TextBox1;
		protected System.Web.UI.WebControls.Button btnAddNew;
		protected System.Web.UI.WebControls.DataGrid DataGrid1;
		protected System.Web.UI.WebControls.Label Label2;
		protected System.Web.UI.WebControls.Label lblTitle;
		private SqlConnection myConnection;

  		private void Page_Load(object sender, System.EventArgs e)
  		{
			myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"].ToString());
			if(!Page.IsPostBack)
			{
				BindData();
			} 
		}
		override protected void OnInit(EventArgs e)
		{
			//
			// CODEGEN: This call is required by the ASP.NET Web Form Designer.
			//
			InitializeComponent();
			base.OnInit(e);
		}
        /// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{    
			this.DataGrid1.CancelCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.DataGrid1_CancelCommand_1);
			this.DataGrid1.EditCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.DataGrid1_EditCommand_1);
			this.DataGrid1.UpdateCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.DataGrid1_UpdateCommand_1);
			this.DataGrid1.SelectedIndexChanged += new System.EventHandler(this.DataGrid1_SelectedIndexChanged);
			this.Load += new System.EventHandler(this.Page_Load);
		}
		private void DataGrid1_EditCommand_1(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
		{
			// We use CommandEventArgs e to get the row which is being clicked
			// This also changes the DataGrid labels into Textboxes so user can edit them
			DataGrid1.EditItemIndex = e.Item.ItemIndex;
			// Always bind the data so the datagrid can be displayed.
			BindData(); 
		}
		private void DataGrid1_UpdateCommand_1(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
		{
			System.Web.UI.WebControls.TextBox cName = new System.Web.UI.WebControls.TextBox();
			cName = (System.Web.UI.WebControls.TextBox) e.Item.Cells[1].Controls[0];
			SqlCommand myCommand = new SqlCommand("SP_UpdatePerson",myConnection);
			myCommand.CommandType = CommandType.StoredProcedure;
			myCommand.Parameters.Add(new SqlParameter("@PersonName",SqlDbType.NVarChar,50));
			myCommand.Parameters["@PersonName"].Value = cName.Text;
			myConnection.Open();
			myCommand.ExecuteNonQuery();
			myConnection.Close();
			DataGrid1.EditItemIndex = -1;
			BindData(); 
		}
		private void DataGrid1_CancelCommand_1(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
		{
			// All we do in the cancel method is to assign '-1' to the datagrid editItemIndex
			// Once the edititemindex is set to '-1' the datagrid returns back to its original condition
			DataGrid1.EditItemIndex = -1;
			BindData();			
		}
		private void BindData() 
		{
			SqlCommand myCommand = new SqlCommand("SP_SELECT_Customers",myConnection);
			myCommand.CommandType = CommandType.StoredProcedure;
			SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
			DataSet ds = new DataSet();
			myAdapter.Fill(ds, "Customers");
			myConnection.Open();
			myCommand.ExecuteNonQuery();
			DataGrid1.DataSource = ds;
			BindData();
			myConnection.Close();
		}
		private void DataGrid1_SelectedIndexChanged(object sender, System.EventArgs e)
		{
			// prints the value of the first cell in the DataGrid
			Label2.Text += DataGrid1.SelectedItem.Cells[0].Text; 
		}
	}
}

[jmcilhinney]

Your call to Fill is opening and then closing the database connection. You should not open the connection, then close it, then open it, then close it again. Your issue is probably that the connection has not settled to a closed state before you call Open, but if you remove that line then it is not properly open when you call ExecuteNonQuery. That is just intuition though, i.e. I don't know that for a fact. Regardless, you should be opening the connection BEFORE the call to Fill.

Having said that, why are you calling ExecuteNonQuery at all? The call to Fill is executing the query and populating the DataTable. Calling ExecuteNonQuery simply executes the same SQL statement again but it just discards the result set because you have specified that it is not a query and therefor e returns no results. What you actually need to do is get rid of the Open, ExecuteNonQuery and Close lines altogether. Your connection will be implicitly opened and closed by the call to Fill.

[HardD99]

hmm... i took ur advice and removed those 3 lines of code... still no go, so i tried a variation of removing lines, the only one that didnt give and err is when i removed the BindData(); line, what i think its doing is starting the BindData event on the pageload, then before it finishes and closes the connection its calling BindData(); again so the connection hasnt closed yet, but its trying to open it, i believe this is the reason for the error. but now the question is, how do i actually make it binddata so i can actually see the datagrid, lol?

[jmcilhinney]

OK, I just looked at your code again and you're exactly right. You're calling the BindData method from inside the BindData method, so if you weren't getting an exception about an open connection you'd get a stack overflow exception because BindData would call BindData would call BindData and so on to infinity until it all fell in a heap. You should not be calling the BindData method recursively like that. Remove the lines I mentioned because they are unnecessary anyway, but also remove the call to BindData from the BindData method.