error in function insert (connexction must be valid and open)

0

Hello good afternoon everyone, I have a small problem in VB, I'm working with mysql, I have a button that resionarlo makes a search in a table, if that value does not exist, I wonder if I want to register, this is where the problem comes , since I get the message "connection must be valid and open" and do not leave the insert, with the search function works well ... this is the code of the function I use:

Public Sub buscarRut()

    conexionMYSQL = New MySqlConnection

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

    Try

        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

            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
                'Llamar al metodo guardar registro
                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 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
    conexionMYSQL.Close()
End Sub

I thank everyone from now on for your help

    
asked by Nicolas Ezequiel Almonacid 21.04.2018 в 23:42
source

0 answers