Unable to connect to any of the specified MySQL hosts

Question

Here is the problem. I am trying to execute a query and its throwing and exception at connection.Open. Strangely, on the same application I am executing a "Select" query and it works fine. But when I execute the "Update" query it throws this Unable to connect to any of the specified MySQL hosts error. Been stuck on this forever. Can someone spot where I am going wrong.

 private void button1_Click(object sender, EventArgs e)
    {
        if (radioButton1.Checked)
        {
            timerEnabled = 1;
        }

        connection.Open();

        //update the settings to the database table 
        MySqlCommand command = connection.CreateCommand();
        command.CommandText = "update Admin_Settings set Difficulty='" + comboBox3.Text + "'," + "NoOfQuestions='" + comboBox4.Text + "'," + "NoOfChoices='" + comboBox5.Text + "'," +
            "Subject='" + comboBox8.Text + "'," + "Timer='" + comboBox2.Text + "," + "TimerEnabled=" + timerEnabled + "," + "TimerType='" + comboBox1.Text + "'";


        command.ExecuteNonQuery();

        MessageBox.Show("Settings updated");
    }

Your question needs more information. Is the connection already open when you `connection.Open();` executes? Seems like a possible cause. — evanmcdonnal, Feb 27 '13 at 22:56

score 1 · Accepted Answer · answered Feb 27 '13 at 22:58

I'm going to recommend you do the following:

private void button1_Click(object sender, EventArgs e)
        {
            using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connString))
            {
                if (radioButton1.Checked)
                {
                    timerEnabled = 1;
                }

                connection.Open();

                //update the settings to the database table 
                MySqlCommand command = connection.CreateCommand();
                command.CommandText = "update Admin_Settings set Difficulty='" + comboBox3.Text + "'," + "NoOfQuestions='" + comboBox4.Text + "'," + "NoOfChoices='" + comboBox5.Text + "'," +
                    "Subject='" + comboBox8.Text + "'," + "Timer='" + comboBox2.Text + "," + "TimerEnabled=" + timerEnabled + "," + "TimerType='" + comboBox1.Text + "'";


                command.ExecuteNonQuery();

                MessageBox.Show("Settings updated");
            }
        }

I understand that you are thinking to yourself, that you should maintain your connection for ease of use and blah blah, but in my experience, it's wasted effort. What ends up happening its lots of trouble that you don't want or need. You end up not realizing that you have a connection open somewhere else and you spend hours troubleshooting things that you shouldn't. Open your connection, close it when you are done.

If you want to have a single connection object, that's fine, but use the using pattern so that it is disposed of every time, and always start fresh with your connections.

NOTE: replace my connection with yoru MySqlConnection object!

Thanks for the advice and your help. I have managed to fix it. The connection string, I forgot to place the semicolon. Stupid mistake. ahh — user123, Feb 27 '13 at 23:55

Jasmine · Answer 2 · 2013-02-27T23:29:13.677

0

As Mike said, you always better use "using" block as it disposes any connection once it goes out of using block. I used two using blocks below one for connection and other for command object.

Try this

    private void button1_Click(object sender, EventArgs e)
    {
        using (SqlConnection connection = new SqlConnection(connString))
        {
            if (radioButton1.Checked)
            {
                timerEnabled = 1;
            }

           string queryString = "update Admin_Settings set Difficulty='" +      
            comboBox3.Text + "'," + "NoOfQuestions='" + comboBox4.Text + "'," + 
            "NoOfChoices='" + comboBox5.Text + "'," + "Subject='" + comboBox8.Text +  
           "'," + "Timer='" + comboBox2.Text + "," + "TimerEnabled=" + timerEnabled +  
              "," + "TimerType='" + comboBox1.Text + "'";

          using (SqlCommand command = new SqlCommand(queryString, connection))
          {
            //update the settings to the database table 


            command.Connection.Open();
            command.ExecuteNonQuery();
            command.Connection.Close();

            MessageBox.Show("Settings updated");
          }
        }
    }

edited Feb 27 '13 at 23:29

answered Feb 27 '13 at 23:05

Jasmine

5,186
16
62
114

This is true but the problem isn't actually here, it's where ever else he's making a connection. – evanmcdonnal Feb 27 '13 at 23:16
@evanmcdonnal: I have modified the code above. We need to explicitly open the connection before we do executenonquery. If this also doesn't works, he should come up with the error details further – Jasmine Feb 27 '13 at 23:26
Thanks for the advice and your help. I have managed to fix it. The connection string, I forgot to place the semicolon. Stupid mistake. ahh – user123 Feb 27 '13 at 23:55
@user123: No problem, but without semicolon how did your Select query worked :0 – Jasmine Feb 27 '13 at 23:56
@Divine good question. Strangely, when I run the program by debugging it gives me an error. But without debugging it worked. I don't know how. – user123 Feb 28 '13 at 00:16
@user123: Ahh strange...Lot of such strange things we find in VS Debugging..... If you find any clue, let me know too :) Thank you....Cheers... – Jasmine Feb 28 '13 at 00:18
I will do. Thanks again :) – user123 Feb 28 '13 at 00:36

Unable to connect to any of the specified MySQL hosts

2 Answers2

Linked