How can i insert data again when it keeps saying open and close connection?-CodePudding

what is wrong with this code. i cannot insert data anymore, i kept saying close and open the connection but when i open it i says . the connection was already open. hope u help me

For employee_id As Integer = 0 To DataGridView1.Rows.Count - 1
            If txt_employee_id.Text = DataGridView1.Rows(employee_id).Cells(0).Value.ToString Then
                MessageBox.Show("Duplicate ID")
            Else
                cmd = con.CreateCommand()
                cmd.CommandType = CommandType.Text
                cmd.CommandText = "INSERT INTO employeelist VALUES('"   txt_employee_id.Text   "', '"   txt_password.Text   "', '"   txt_name.Text   "', '"   txt_address.Text   "', '"   txt_contact_info.Text   "', '"   txt_department.Text   "', '"   txt_position.Text   "', '"   txt_basic_salary.Text   "', '"   txt_usertype.Text   "' )"

                cmd.ExecuteNonQuery()


                txt_employee_id.Text = ""
                txt_password.Text = ""
                txt_name.Text = ""
                txt_address.Text = ""
                txt_contact_info.Text = ""
                txt_department.Text = ""
                txt_position.Text = ""
                txt_basic_salary.Text = ""
                txt_usertype.Text = ""

                disp_data()

                MessageBox.Show("Data Inserted")
            End If
        Next

CodePudding user response：

You really ought to avoid having a common connection object in the first place, so this sort of thing can't happen. Store your connection string in a common location but then create, configure, open, use and destroy your connection object where it's needed, which will be the same place you create the command object, e.g.

Using connection As New MySqlConnection(connectionString),
      command As New MySqlCommand(query, connection)
    connection.Open()
    command.ExecuteNonQuery()
End Using

Because you just created the connection, you know that it is not open so opening it will not be an issue. It gets closed implicitly at the end of the Using block.

That said, if you really want to use a common connection then you can. If you're being told to open it and that it was already open then obviously you're not closing it again after using it. Open, use, close. Simple:

con.Open()
cmd.ExecuteNonQuery()
con.Close()

If you do that every time you execute a command over the connection then it will work as expected. Don't open the connection anywhere that you're not using it and always close it afterwards.

Note that you don't need to open or close if you are calling Fill or Update on a data adapter, as it will do so implicitly. That said, if you are calling more than one method that will use the connection, e.g. ExecuteNonQuery multiple times or Fill or Update on multiple data adapters, you should call Open once at the start and Close once at the end. If you don't do that with data adapters then the connection will be implicitly closed and reopened between calls and that is inefficient.

CodePudding user response：

can't open same connection object multiple time, for reopen you need to close it.

So just write below line above each execute command (cmd.ExecuteNonQuery()). It will check connection is open or not if closed then open it

If con.State = ConnectionState.Closed Then con.Open()