No errors in the code, but no changes occur in the table when trying to update it

Question

I'm trying to update my data in C# Win Form. I created a button "update", but whenever I run it, I don't see any changes in the table and any occurring errors

void insertdata() {
    cmd = connection.CreateCommand();
    cmd.CommandText = "SELECT * FROM airport";
    adapter.SelectCommand = cmd;
    table.Clear();
    adapter.Fill(table);
    dgv.DataSource = table;
}

private void button_update_Click(object sender, EventArgs e)
{
    cmd = connection.CreateCommand();
    cmd.CommandText = "UPDATE airport SET  p_name = '"+textBox2.Text+ "',p_age = '" + textBox3.Text + "', c_name = '" + textBox4.Text + "', date = '" + textBox5.Text + "', city_t = '" + textBox6.Text + "', city_f ='" + textBox7.Text + "', trip_num = '" + textBox8.Text + "', plane_type = '" + textBox9.Text+"' WHERE p_id = '"+textBox1+"'";
    cmd.ExecuteNonQuery();
    insertdata();
}

I've tried to add

connection.Open();
connection.Close();

However, I keep getting: "System.InvalidOperationException: "The connection was not closed. The connection is open."

Could there be any change in my code for updating the rows in the table, as whenever I run it I don't get any errors.

This is because a connection is already open. The connection was not closed after update and it is again opened for select probably. Can you post the code where connection is opened and closed? — T.kowshik Yedida, Jul 11 '21 at 06:49
Every time you want to run some SQL, then `new` up a connection, open it and dispose it when you are done. That will solve 98% of your problems. Then read https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection . Then name your textboxes with meaningful names. — mjwills, Jul 11 '21 at 06:54
@T.kowshikYedida connection.Open(); cmd = connection.CreateCommand(); cmd.CommandText = "UPDATE airport SET p_name = '"+textBox2.Text+ "',p_age = '" + textBox3.Text + "', c_name = '" + textBox4.Text + "', date = '" + textBox5.Text + "', city_t = '" + textBox6.Text + "', city_f ='" + textBox7.Text + "', trip_num = '" + textBox8.Text + "', plane_type = '" + textBox9.Text+"' WHERE p_id = '"+textBox1+"'"; cmd.ExecuteNonQuery(); insertdata(); connection.Close(); — qrttttt, Jul 11 '21 at 06:57
(Possible) side note: Do not use string interpolation or concatenation to get values into SQL queries. That's error prone and might make your program vulnerable to SQL injection attacks. Use parameterized queries. See ["How can I add user-supplied input to an SQL statement?"](https://stackoverflow.com/questions/35163361/how-can-i-add-user-supplied-input-to-an-sql-statement). — sticky bit, Jul 11 '21 at 07:00
As a debug step: what exactly *is* that CommandText that you are trying to execute? — Hans Keﬆing, Jul 11 '21 at 14:15

score 2 · Accepted Answer · answered Jul 11 '21 at 07:39

2

Please note the you wrote

WHERE p_id = '"+textBox1+"'

Instead of

WHERE p_id = '"+textBox1.Text+"'

Probably you don't have an ID that equals to the textBox...

answered Jul 11 '21 at 07:39

M. G.

44
5

No errors in the code, but no changes occur in the table when trying to update it

1 Answers1