C# ExecuteNonQuery "Connection must be valid and open."

Question

I'm trying to add values into database, but every time I try to add some thing i get an error in the ExecuteNonQuery() with the message "Connection must be valid and open." And I don't know what to do!!!

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace ClientesClinica
{
    public partial class frmCadastro : Form
    {

    MySqlConnection conect = new MySqlConnection("server = localhost; user id = root; database = clientes; password = '';");

    public frmCadastro()
    {
        InitializeComponent();
    }

    private void frmCadastro_Load(object sender, EventArgs e)
    {

    }

    private void btnCancela_Click(object sender, EventArgs e)
    {
        Close();
    }

    private void btnSalvar_Click(object sender, EventArgs e)
    {

        int cod;
        string nome;
        string end;
        int tel;
        cod = Convert.ToInt16(txtCodigo.Text);

        nome = txtNome.Text;
        end = txtEndereco.Text;


        if (txtNome.Text == "")
        {
            MessageBox.Show("Favor digitar o nome");
        }
        if (txtCodigo.Text == "")
        {
            MessageBox.Show("Favor digitar o código");
        }




        conect.Close();
        MySqlCommand insere = new MySqlCommand();

        insere.CommandText = "INSERT INTO cliente(cod, nome, endereco) Values(@cod + ,'@nome', '@end');";
        insere.Parameters.AddWithValue("@cod", cod);
        insere.Parameters.AddWithValue("@nome", nome);
        insere.Parameters.AddWithValue("@end", end);

            conect.Open();
            insere.ExecuteNonQuery();// THE ERROR IS HERE!!!!
            conect.Close();
            MessageBox.Show("Salvo");




    }

You need to open your connection and supply it to the command. — Jeremy Thompson, Jun 25 '14 at 03:25
@Bordotti you should remove comment from ur code, it's not understand by any english users. — Dhwani, Jun 25 '14 at 03:43
sorry @Dhwani i just comment in english where is the error, all other comments don't talk anything about the error.. — Bordotti, Jun 25 '14 at 21:22

score 7 · Accepted Answer · answered Jun 25 '14 at 03:28

You need to open your connection and supply it to the command as per MSDN: SQLCommand

string queryString = "SELECT OrderID, CustomerID FROM dbo.Orders;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);  //<- See here the connection is passes to the command
connection.Open();
SqlDataReader reader = command.ExecuteReader();
try
{
    while (reader.Read())
    {
        Console.WriteLine(String.Format("{0}, {1}",
            reader[0], reader[1]));
    }
}
finally
{
    // Always call Close when done reading.
    reader.Close();
}
}

Or for your usage:

conect.Open(); //<- Open Connection first

MySqlCommand insere = new MySqlCommand();

insere.Connection = conect;  //<- Set the Commands connection

insere.CommandText = "INSERT INTO cliente(cod, nome, endereco) Values(@cod + ,'@nome', '@end');";
insere.Parameters.AddWithValue("@cod", cod);
insere.Parameters.AddWithValue("@nome", nome);
insere.Parameters.AddWithValue("@end", end);
insere.ExecuteNonQuery();
conect.Close();

yeah @jeremy i write this line code and all works perfect!! Thanks! — Bordotti, Jun 25 '14 at 21:21

score 1 · Answer 2 · answered Jan 24 '15 at 05:21

this exception often raise cause of call the ExecuteNonQuery() method before connection opening

 MySqlConnection con = new MySqlConnection(connection);
        MySqlCommand command = con.CreateCommand();
        command.CommandText="insert into stactoverflowtable (id,name) values('1','adil')";//suppose table name is stackoverflowtalbe

        try
        {
            con.Open();
            command.ExecuteNonQuery();

        }
        catch (Exception ex)
        {
            textBox1.Text = ex.Message;

        }

C# ExecuteNonQuery "Connection must be valid and open."

2 Answers2