在数据库中插入记录时，连接必须有效且打开。

Question

我试图将客户端详细信息插入数据库，但得到了此异常。

连接必须有效且打开。在MySql.Data.MySqlClient.MySqlCommand.Throw(Exception ex)在MySql.Data.MySqlClient.MySqlCommand.CheckState() at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)在MySql.Data.MySqlClient.MySqlCommand.ExecuteReader() at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery() at book_online.saveClient_Click(对象发送者，EventArgs e)

MySqlConnection con = new MySqlConnection();
con.ConnectionString = ConfigurationManager.ConnectionStrings("conio").ConnectionString();
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "INSERT INTO clientsDetails (clientName, companyAddress, city, contactPersonName, contactNumber1) Values(@clientName, @companyAddress, @city, @contactPersonName, @contactNumber1)";
cmd.Parameters.AddWithValue("@clientName", companyName.Text);
cmd.Parameters.AddWithValue("@companyAddress", companyAddress.Text);
cmd.Parameters.AddWithValue("@city", companyCity.Text);
cmd.Parameters.AddWithValue("@contactPersonName", concernPersonName.Text);
cmd.Parameters.AddWithValue("@contactNumber1", countryCodeWithNumber);
con.Open();
cmd.ExecuteNonQuery();
con.Close();

Answer 1

您已经打开了连接并执行了命令，但是您忘记了将连接分配给命令，那么只有代码可以像您预期的那样正常工作。强烈建议您也使用。然后，代码将如下所示：

using (MySqlConnection con = new MySqlConnection())
{
    con.ConnectionString = ConfigurationManager.ConnectionStrings("conio").ConnectionString();
    string queryText = "INSERT INTO clientsDetails (clientName, companyAddress, city, contactPersonName, contactNumber1) Values(@clientName, @companyAddress, @city, @contactPersonName, @contactNumber1)";
    using (MySqlCommand cmd = new MySqlCommand(queryText, con))
    {
        cmd.Parameters.AddWithValue("@clientName", companyName.Text);
        cmd.Parameters.AddWithValue("@companyAddress", companyAddress.Text);
        cmd.Parameters.AddWithValue("@city", companyCity.Text);
        cmd.Parameters.AddWithValue("@contactPersonName", concernPersonName.Text);
        cmd.Parameters.AddWithValue("@contactNumber1", countryCodeWithNumber);
        con.Open();
        cmd.ExecuteNonQuery();
    }
}

Answer 2

命令缺少连接字符串.

MySqlConnection con = new MySqlConnection();
con.ConnectionString = ConfigurationManager.ConnectionStrings("conio").ConnectionString();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = con;
cmd.CommandText = "INSERT INTO clientsDetails (clientName, companyAddress, city, contactPersonName, contactNumber1) Values(@clientName, @companyAddress, @city, @contactPersonName, @contactNumber1)";
cmd.Parameters.AddWithValue("@clientName", companyName.Text);
cmd.Parameters.AddWithValue("@companyAddress", companyAddress.Text);
cmd.Parameters.AddWithValue("@city", companyCity.Text);
cmd.Parameters.AddWithValue("@contactPersonName", concernPersonName.Text);
cmd.Parameters.AddWithValue("@contactNumber1", countryCodeWithNumber);
con.Open();
cmd.ExecuteNonQuery();
con.Close();

Answer 3

您没有为命令指定连接对象：

cmd.Connection = con;

另外，最好使用一个using块，当它退出连接时，它会自动关闭连接：

using(MySqlConnection con = new MySqlConnection())
{
   con.ConnectionString =
                       ConfigurationManager.ConnectionStrings("conio").ConnectionString();

   MySqlCommand cmd = new MySqlCommand();
   cmd.CommandText = @"INSERT INTO clientsDetails
              (clientName, companyAddress, city, contactPersonName, contactNumber1)
               Values
              (@clientName, @companyAddress, @city, @contactPersonName, @contactNumber1)";

   cmd.Connection = con;
   cmd.Parameters.AddWithValue("@clientName", companyName.Text);
   cmd.Parameters.AddWithValue("@companyAddress", companyAddress.Text);
   cmd.Parameters.AddWithValue("@city", companyCity.Text);
   cmd.Parameters.AddWithValue("@contactPersonName", concernPersonName.Text);
   cmd.Parameters.AddWithValue("@contactNumber1", countryCodeWithNumber);
   con.Open();
   cmd.ExecuteNonQuery();
}

为了获取信息，using关键字允许编译为封闭的块生成一个try...finally，并在生成的最终块中对using中的对象调用Dispose (在您的示例中是con)。因此，在所有最终得到保证的情况下，都保证关闭连接)