C#操作MySQL无法执行查询：button4_Click事件代码排查求助

Hey there, let's work through why your MySQL update query isn't executing in the button4_Click event. From the partial code you shared, I can spot several common issues that might be causing the problem:

• Forgot to open the database connection
  You created a MySqlConnection instance, but never called connection.Open() before executing the command. Plus, it's best practice to use a using statement to automatically dispose the connection when done, avoiding resource leaks.

• Incomplete parameter setup
  Your code cuts off at cmd.Parameters.Add("@idloan", My...—make sure you fully define this parameter with the correct type and value. Since you parsed textBox9.Text to an integer, it should look like:
  cmd.Parameters.Add("@idloan", MySqlDbType.Int32).Value = id;

• Redundant connection string settings
  Your connection string has duplicate entries (datasource=localhost and Data Source=localhost) and unnecessary single quotes around the database name. Clean it up to:
  "server=localhost;port=3306;database=liblib;user=root;password=admin"

• Missing command execution
  You built the MySqlCommand but didn't run it with cmd.ExecuteNonQuery()—this method sends the update to the database and returns the number of rows affected, which is useful for verifying success.

• No exception handling
  Database operations can fail for many reasons (invalid input, connection issues, wrong column names). Adding a try-catch block will help you capture specific error messages, which are crucial for debugging.

Corrected Full Code Example #

private void button4_Click(object sender, EventArgs e)
{
    // Use using statement to auto-manage connection lifecycle
    using (MySqlConnection connection = new MySqlConnection("server=localhost;port=3306;database=liblib;user=root;password=admin"))
    {
        try
        {
            string query = "UPDATE loans SET dataRet = @data1 WHERE idloans = @idloan";
            MySqlCommand cmd = new MySqlCommand(query, connection);

            // Safely parse input to avoid crashes from non-numeric values
            if (!int.TryParse(textBox9.Text, out int id))
            {
                MessageBox.Show("Please enter a valid numeric loan ID!");
                return;
            }

            // Add parameters with matching MySQL column types
            cmd.Parameters.Add("@data1", MySqlDbType.Date).Value = dateTimePicker1.Value.Date; // Send only date part for DATE columns
            cmd.Parameters.Add("@idloan", MySqlDbType.Int32).Value = id;

            connection.Open();
            int rowsAffected = cmd.ExecuteNonQuery();

            // Give clear feedback to the user
            if (rowsAffected > 0)
            {
                MessageBox.Show("Loan record updated successfully!");
            }
            else
            {
                MessageBox.Show("No matching loan record found to update.");
            }
        }
        catch (MySqlException ex)
        {
            MessageBox.Show($"Database error: {ex.Message}");
        }
        catch (Exception ex)
        {
            MessageBox.Show($"Unexpected error: {ex.Message}");
        }
    }
}

Extra Tips #

• I swapped Int32.Parse for int.TryParse to handle invalid user input gracefully.
• Used dateTimePicker1.Value.Date to ensure only the date component is sent (important if dataRet is a MySQL DATE type, not DATETIME).
• Checking rowsAffected helps confirm if your update actually matched any records, which is great for debugging and user feedback.

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