none
C# "The connection is already Open (state = Open)

    Question

  • Hey all,

    I'm new to C# development and while trying to create a simple application, I ran into an issue. I am trying to populate a ComboBox based on the SelectedIndexChanged property of the first ComboBox. Visual Studio states at the second cn.Open (in private void cboCities_SelectedIndexChanged), that the connection is already open. However, from my code I believe I have closed it already? What is it I am doing wrong? Any and all help is highly appreciated. Thanks!

     public partial class Form1 : Form
        {
            private SqlCeConnection cn = new SqlCeConnection(@"Data Source = \Program Files\ParkSurvey\ParkSurvey.sdf; Persist Security Info = False; Password = *");
    
            public Form1()
            {
                InitializeComponent();
            }
    
            private void Form1_Load(object sender, EventArgs e)
            {
                cn.Open();
                SqlCeCommand command = cn.CreateCommand();
                command.CommandText = "SELECT Name FROM Cities ORDER BY Name ASC";
                SqlCeDataAdapter da = new SqlCeDataAdapter(command);
                DataSet ds = new DataSet();
                da.Fill(ds);
                cn.Close();
                cboCities.ValueMember = "CityId";
                cboCities.DisplayMember = "Name";
                cboCities.DataSource = ds.Tables[0];
                cboCities.SelectedIndex = -1;
            }
    
            private void cboCities_SelectedIndexChanged(object sender, EventArgs e)
            {
                if (cboCities.SelectedIndex > -1)
                {
                    cn.Open();
                    SqlCeCommand command = cn.CreateCommand();
                    command.CommandText = "SELECT Name FROM Parks WHERE CityId ='" + cboCities.SelectedValue + "'";
                    SqlCeDataAdapter da = new SqlCeDataAdapter(command);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    cn.Close();
                    cboParks.ValueMember = "ParkId";
                    cboParks.DisplayMember = "Name";
                    cboParks.DataSource = ds.Tables[0];
                    cboParks.SelectedIndex = -1;
                }
            }
    
        }


    • Edited by Drinan Thursday, April 26, 2012 10:48 PM
    Thursday, April 26, 2012 10:47 PM

Answers

All replies

  • I don't know, definitely looks like you are closing it, don't know much about SQLCE, just learning it as I go..

    Maybe try to use step by step debug or check if already open before you try to open it again.

    if (cn.State == ConnectionState.Closed) { cn.Open(); }

    your code

                if (cn.State == ConnectionState.Open)
                {
                    cn.Close();
                }




    • Edited by CsabaN Thursday, April 26, 2012 11:26 PM typo
    Thursday, April 26, 2012 11:25 PM
  • Not sure why (may be an exception for example) but here are few tips:

    1. Do not close connection until application exists. 

    2. Use SqlCeResultSet instead of DataSet.

    On a side note: you may want to review your code around SQL access (especially running on servers) and rewrite it using parameters to eliminate possibility of SQL Injection attacks.



    This posting is provided "AS IS" with no warranties, and confers no rights.

    Thursday, April 26, 2012 11:30 PM
    Moderator
  • I figured out the issue. I had to declare the connection string in each method so it would dispose properly. Thank you for the recommendations!
    Friday, April 27, 2012 7:51 PM