C#操作MySQL报错‘connection must be valid and open’排查求助

Hey there, let's break down why you're hitting this error and fix it right away!

问题根源 #

Your core mistake is super straightforward: you're trying to execute a database query before opening the connection!

Look at your faulty Form1_Load code:

MySqlDataReader MyReader2;
MyReader2 = sc.ExecuteReader(); // You run the query first...
conn.Open(); // ...but only open the connection AFTERWARD!

Compare that to your working cmdOpslaanKlanten_Click code—you got the order right there: open the connection first, then execute the command. That's why it works without issues.

Fixed Form1_Load Code #

I've adjusted the connection order, plus added some best practices to make your code more robust (like using using statements to auto-manage resources, and fixing the DisplayMember issue since you can't list two columns directly):

private void Form1_Load(object sender, EventArgs e)
{
    try
    {
        string MyConnection2 = "datasource=localhost;username=root;database=game4rent";
        // Update query to create a combined display text for the dropdown
        string Query = "select Klantnummer, voornaam, concat(Klantnummer, ' - ', voornaam) as DisplayText from game4rent.klanten";

        // Using statements auto-release resources, no need for manual Close()
        using (MySqlConnection conn = new MySqlConnection(MyConnection2))
        {
            conn.Open(); // Open connection FIRST this time!
            using (MySqlCommand sc = new MySqlCommand(Query, conn))
            {
                using (MySqlDataReader MyReader2 = sc.ExecuteReader())
                {
                    DataTable dt = new DataTable();
                    dt.Load(MyReader2); // Auto-load all columns from the reader

                    cbKlantenNummers.ValueMember = "Klantnummer";
                    cbKlantenNummers.DisplayMember = "DisplayText"; // Use the combined text for display
                    cbKlantenNummers.DataSource = dt;
                }
            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

Quick Extra Tips #

• Always follow this order for database operations: Create connection → Open connection → Execute command → Process results → Release resources
• using statements are the best way to manage database resources in .NET—they prevent connection leaks by auto-disposing objects when done
• Your working save code has a SQL injection risk (directly concatenating textbox values into SQL). Fix that with parameterized queries, like this:

string Query = "insert into game4rent.klanten(voornaam,achternaam,straat,huisnummer,woonplaats) values(@voornaam, @achternaam, @straat, @huisnummer, @woonplaats);";
MySqlCommand MyCommand2 = new MySqlCommand(Query, MyConn2);
MyCommand2.Parameters.AddWithValue("@voornaam", txtVoornaam.Text);
// Add other parameters the same way

内容的提问来源于stack exchange，提问作者stijn debets