C# Connection must be valid and open Mysql

Question

is there something wrong with my codes. I already try another codes but the problem is still the same. I've been solving this error for a couple of weeks now, and i can't figure it out how to solve it. And also I already try some another code but the problem is still the same.

I want to save a multiple row from dataGrid to my database.

here's the codes that i use to save a multiple row

    private void button1_Click(object sender, EventArgs e)
    {

        MySqlConnection conString = new MySqlConnection("datasource = localhost; port = 3306; Initial catalog = dbnewsystem; username = root;password = 1234");
        MySqlCommand command1 = new MySqlCommand("INSERT INTO purchaseorder (orNo, ProdNo, Quantity, total)" +
        "VALUES(@ORNo,@ProductNo,@quantity,@total )", conString);
        command1.Parameters.AddWithValue("@ORNo", dataGridView1.Rows.Count);
        command1.Parameters.AddWithValue("@ProductNo", dataGridView1.Rows.Count);
        command1.Parameters.AddWithValue("@quantity", dataGridView1.Rows.Count);
        command1.Parameters.AddWithValue("@total", textBox6.Text);


        conString.Open();
        command1.ExecuteNonQuery();
        command1.Connection = conString;
        conString.Close();
        command1.CommandType = CommandType.StoredProcedure;
        command1.CommandText = "pos_save";

        if (command1.ExecuteNonQuery() == 1)
        {
            MessageBox.Show("saved");
        }
        else
        {
            MessageBox.Show("Sorry Nothing to be Update");
        }
        conString.Close();

    }

You might want to remove the conString.Close(); in the middle of the function, i think thats the problem. — Ricardo Ortega Magaña
– Ricardo Ortega Magaña, Commented Mar 5, 2017 at 19:51
@OldProgrammer What do you mean by stack trace?.. Sorry im not very fluent in english. And i'm still learning programming. — joey dinopol
– joey dinopol, Commented Mar 5, 2017 at 19:55

Fatikhan Gasimov · Accepted Answer · 2017-03-05 19:54:30Z

1

change sequence

    command1.Connection = conString;
    command1.ExecuteNonQuery();

answered Mar 5, 2017 at 19:54

Fatikhan Gasimov

9632 gold badges16 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dmitrii Bychenko · Accepted Answer · 2017-03-05 19:56:39Z

You have implemented wrong sequence:

...
conString.Open();                   // Connection opened
command1.ExecuteNonQuery();         // Try executing (fail) 
command1.Connection = conString;    // Connection assigned

Change to

conString.Open();                   // Connection opened
command1.Connection = conString;    // Connection assigned
command1.ExecuteNonQuery();         // Try executing (fail)

A better design is

// Wrap IDisposable into using
using (MySqlConnection conString = new MySqlConnection("...")) {
   conString.Open();

   // Make SQL Readable
   string sql = 
     @"INSERT INTO purchaseorder(
         orNo, 
         ProdNo, 
         Quantity, 
         total)
       VALUES(
         @ORNo,
         @ProductNo,
         @quantity,
         @total)";

  // Wrap IDisposable into using
  using (MySqlCommand command1 = new MySqlCommand(sql, conString))  {
    command1.Parameters.AddWithValue("@ORNo", dataGridView1.Rows.Count);
    command1.Parameters.AddWithValue("@ProductNo", dataGridView1.Rows.Count);
    command1.Parameters.AddWithValue("@quantity", dataGridView1.Rows.Count);
    command1.Parameters.AddWithValue("@total", textBox6.Text);

    command1.ExecuteNonQuery();
  }
}

Collectives™ on Stack Overflow

C# Connection must be valid and open Mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related