MySQL C# Error; "connection must be valid and open"

Question

Hello I got This application I'm working on but I'm stuck at populating a drop-down box. I want my SQL Data from my database in the drop-down box but SQL says "connection must be valid and open" but I got another feature that includes saving text to my SQL Database with the same code and that works fine so I don't really know what is wrong.

Here is my code.

private void Form1_Load(object sender, EventArgs e)
    {
        try {
            //This is my connection string i have assigned the database file address path  
            string MyConnection2 = "datasource=localhost;username=root;database=game4rent";
            //This is my insert query in which i am taking input from the user through windows forms  
            string Query = "select Klantnummer,voornaam from game4rent.klanten";
            //This is  MySqlConnection here i have created the object and pass my connection string.  
            MySqlConnection conn = new MySqlConnection(MyConnection2);
            //This is command class which will handle the query and connection object.  
            MySqlCommand sc = new MySqlCommand(Query, conn);
            MySqlDataReader MyReader2;
            MyReader2 = sc.ExecuteReader();
            conn.Open();

            DataTable dt = new DataTable();
            dt.Columns.Add("Klantnummer", typeof(string));
            dt.Columns.Add("voornaam", typeof(string));
            dt.Load(MyReader2);

            cbKlantenNummers.ValueMember = "Klantnummer";
            cbKlantenNummers.DisplayMember = "Klantnummer,voornaam";
            cbKlantenNummers.DataSource = dt;

            conn.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
   }

Here is the other code that does work.

 private void cmdOpslaanKlanten_Click(object sender, EventArgs e)
    {
        try
        {
            //This is my connection string i have assigned the database file address path  
            string MyConnection2 = "datasource=localhost;username=root;password=";
            //This is my insert query in which i am taking input from the user through windows forms  
            string Query = "insert into game4rent.klanten(voornaam,achternaam,straat,huisnummer,woonplaats) values('" + this.txtVoornaam.Text + "','" + this.txtAchternaam.Text + "','" + this.txtStraat.Text + "','" + this.txtHuisnummer.Text + "','" + this.txtWoonplaats.Text + "');";
            //This is  MySqlConnection here i have created the object and pass my connection string.  
            MySqlConnection MyConn2 = new MySqlConnection(MyConnection2);
            //This is command class which will handle the query and connection object.  
            MySqlCommand MyCommand2 = new MySqlCommand(Query, MyConn2);
            MySqlDataReader MyReader2;
            MyConn2.Open();
            MyReader2 = MyCommand2.ExecuteReader();     // Here our query will be executed and data saved into the database.  
            MessageBox.Show("Save Data");
            while (MyReader2.Read())
            {
            }
            MyConn2.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }

    }

David · Accepted Answer · 2018-05-14 12:02:53Z

3

You're trying to execute a query before opening the connection:

MyReader2 = sc.ExecuteReader();
conn.Open();

As the error states, the connection has to be open before executing the query:

conn.Open();
MyReader2 = sc.ExecuteReader();

answered May 14, 2018 at 12:02

David

221k42 gold badges245 silver badges337 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL C# Error; "connection must be valid and open"

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related