VB.NET-MYSQL, ERROR the connection is already open

1

Hello good afternoon everyone, I turn to you for help, guidance ... I have a function that is responsible for finding a data in the Database in MySQL , if you find that data, I load the values in controls of the form (txtbox, lables, etc), and in case I do not find the data, it gives me the possibility to choose whether I want to enter it or not (through a messagebox with two buttons, one accept the other cancel).

The search works well for me, but when I want to register a new data (I know I do not have it) it throws me an error:

  

the connection is already open

The code of the function is as follows

 Public Sub buscarRut()

    conexionMYSQL = New MySqlConnection

    conexionMYSQL.ConnectionString = ("server=localhost;User Id=soporte;database=db_incidencias_muniquel;password=123")

    conexionMYSQL.Open()

    Try
        'conexionMYSQL.Open()

        Dim confirmacion As DialogResult

        Dim queryBusqueda As String = "SELECT * FROM tbl_funcionario_soporte WHERE rut_Funcionario=@rut_funcionario"

        comando = New MySqlCommand(queryBusqueda, conexionMYSQL)

        comando.Parameters.AddWithValue("@rut_funcionario", txtRut.Text)

        da.SelectCommand = comando

        da.Fill(dt)

        If (dt.Rows.Count <> 0) Then
            cmdEliminar.Enabled = True
            cmdActualizar.Enabled = True
            Button5.Enabled = False
            Button1.Enabled = False
            txtNombre.Text = dt.Rows(0)(4).ToString
            txtIdDpto.Text = dt.Rows(0)(1).ToString
            cmbDepartamento.SelectedItem = dt.Rows(0)(5).ToString
            'cmbDepartamento.SelectedValue = dt.Rows(0)(1).ToString
            'txtIdProfesion.Text = dt.Rows(0)(1).ToString
            ComboBox1.SelectedValue = dt.Rows(0)(1).ToString
            txtDireccion.Text = dt.Rows(0)(6).ToString
            txtCelular.Text = dt.Rows(0)(7).ToString
            txtAnexo.Text = dt.Rows(0)(8).ToString
            txtEmail.Text = dt.Rows(0)(9).ToString
            txtcontraseña.Text = dt.Rows(0)(10).ToString
            TextBox1.Text = dt.Rows(0)(2).ToString
            'cmbTipoPermiso.SelectedValue = dt.Rows(0)(6).ToString

            lblBuscador.Visible = False

            desbloquearEdicion()

            'conexionMYSQL.Close()
        Else
            confirmacion = MessageBox.Show("Rut no Existe en la Base de Datos ¿Desea Registrarlo Ahora?", "Sistema de Gestión de Incidencias", MessageBoxButtons.OKCancel, MessageBoxIcon.Error)

            If confirmacion = Windows.Forms.DialogResult.OK Then
                conexionMYSQL.Open()
                Try

                    txtIdDpto.Text = "44"

                    'TextBox1.Text = "0"

                    'conexionMYSQL.Open()

                    desbloqearControles()

                    Dim query As String = "INSERT INTO tbl_funcionario_soporte(id_departamento, nivel_acceso, rut_funcionario, nombre_funcionario, profesion, direccion, celular, anexo, email, password)VALUES(@id_departamento,@nivel_acceso,@rut_funcionario,@nombre_funcionario,@profesion,@direccion,@celular,@anexo,@email,@password)"


                    comando = New MySqlCommand(query, conexionMYSQL)

                    comando.Parameters.AddWithValue("@id_departamento", txtIdDpto.Text)
                    comando.Parameters.AddWithValue("@nivel_acceso", TextBox1.Text)
                    comando.Parameters.AddWithValue("@rut_funcionario", txtRut.Text)
                    comando.Parameters.AddWithValue("@nombre_funcionario", txtNombre.Text)
                    comando.Parameters.AddWithValue("@profesion", ComboBox1.SelectedItem)
                    comando.Parameters.AddWithValue("@direccion", txtDireccion.Text)
                    comando.Parameters.AddWithValue("@celular", txtCelular.Text)
                    comando.Parameters.AddWithValue("@anexo", txtAnexo.Text)
                    comando.Parameters.AddWithValue("@email", txtEmail.Text)
                    comando.Parameters.AddWithValue("@password", txtcontraseña.Text)

                    dr = comando.ExecuteReader

                    'MENSAJE DE CONFIRMACION
                    MsgBox("CUENTA DE FUNCIONARIO DE SOPORTE/ASISTENCIA CREADA CON EXITO")

                Catch ex As Exception
                    MsgBox(ex.Message)
                Finally
                    conexionMYSQL.Dispose()
                End Try
            ElseIf confirmacion = Windows.Forms.DialogResult.Cancel Then
                conexionMYSQL.Close()
                txtRut.Text = ""
                txtRut.Focus()
                Return
            End If
        End If

    Catch ex As Exception
        MsgBox(ex.Message)
    Finally
        conexionMYSQL.Dispose()
    End Try
End Sub

I thank everyone from now on and your comments.

    
asked by Nicolas Ezequiel Almonacid 25.04.2018 в 18:45
source

1 answer

2

The error is evident and is explained only: You have the connection open when you try to open it again.

The third line of your method is the following:

conexionMYSQL.Open()

Which opens the connection. Subsequently, in the% share% share, you have:

If confirmacion = Windows.Forms.DialogResult.OK Then
   conexionMYSQL.Open()

so you try again to open the connection and throw the exception. Simply that you comment the third line of the code should work correctly.

P.D. You are using a Else to read the data. Keep in mind that they do not need you to open the connection beforehand to use them. DataAdapter is automatically responsible for opening and closing the connection.

    
answered by 26.04.2018 в 09:04