Connection must be valid and open in visual basic .net

Question

So I'm trying to connect to a database through MySql and Visual Studio but i get that error, i've tried everything I could so asking here is my last resource. Here's my code

Imports MySql.Data.MySqlClient

Public Class Login

Friend conexionBD = New MySqlConnection
Friend sServidor As String = "localhost"
Friend susuario As String = "root"
Friend sclave As String = ""
Friend sBaseDatos As String = "bd_usuarios"
Friend cmdUsuarios As MySqlCommand
'Friend dtUsuarios As New DataTable
Friend sSqlusuario, slogin, sNombres, sApellidos, scontrasena As String
Private Property sNombre As Object

Private Sub btn_aceptar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_aceptar.Click
    'Dim cmdUsuarios As MySqlConnection
    'Dim dtUsuarios As New DataTable
    slogin = ""
    sclave = ""
    If txt_usuario.Text = "" Then
        MsgBox("El login no puede estar en blanco")
        txt_usuario.Focus()
    ElseIf txt_clave.Text = "" Then
        MsgBox("La clave no puede estar en blanco")
        txt_clave.Focus()
    Else
        ' conexion a la base de datos bd_usuarios()
        Try
            sSqlusuario = "SELECT * FROM usuario WHERE usuario = '" & txt_usuario.Text & "';"
            cmdUsuarios = New MySqlCommand()
            cmdUsuarios.Connection = conexionBD
            cmdUsuarios.CommandType = CommandType.Text
            Dim ConsultaSQL As System.Data.IDataReader
            ConsultaSQL = cmdUsuarios.ExecuteReader()
            While ConsultaSQL.Read
                'Ejecuto la consulta y asigno el resultado
                slogin = ConsultaSQL("Usuario")
                sclave = ConsultaSQL("clave")
            End While

            If slogin = txt_usuario.Text Then
                If sclave = txt_clave.Text Then
                    MessageBox.Show("Bienvenido al Sistema", "Sistema")
                Else
                    MsgBox("clave errada o no encontrada")
                    txt_clave.Focus()
                    ConsultaSQL.Close()
                    conexionBD.Close()
                End If
            Else
                MessageBox.Show("Usuario no existe")
                txt_usuario.Focus()
                ConsultaSQL.Close()
                conexionBD.Close()
            End If
        Catch ex As Exception
            conexionBD.Close()
            MsgBox("Error" & vbCrLf & ex.Message.ToString)
        End Try
    End If
End Sub
Private Sub btn_cancelar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_cancelar.Click
    conexionBD.Close()
    Application.Exit()
End Sub
Public Function bd_usuario()
    Try
        'Dim conexion = New MySqlConnection()
        conexionBD.ConnectionString = "server=" & sServidor & ";" & "user id=" & susuario & ";" & "password=" & sclave & "; database= " & sBaseDatos & "; port=3306;"
        conexionBD.Open()
        Return True
    Catch ex As Exception
        MessageBox.Show("No se puede conectar con la base de datos")
        End
    End Try
End Function

Private Sub Frm_login_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

End Sub
End Class`

A small note:

Before the first "try", i tried putting conexionBD.Open() and that resulted in just getting "connection property is not set or null", from there i also tried to see if i could fix that one but nope. I hope you can help me, it's my first time using VB

Answer 1

You have this:

Friend conexionBD = New MySqlConnection

and this:

cmdUsuarios = New MySqlCommand()
cmdUsuarios.Connection = conexionBD

and this:

ConsultaSQL = cmdUsuarios.ExecuteReader()

It should be obvious what the issue with that code is. You set the Connection property of your command but you never set the ConnectionString of that connection or call Open on it. You have this method:

Public Function bd_usuario()
    Try
        'Dim conexion = New MySqlConnection()
        conexionBD.ConnectionString = "server=" & sServidor & ";" & "user id=" & susuario & ";" & "password=" & sclave & "; database= " & sBaseDatos & "; port=3306;"
        conexionBD.Open()
        Return True
    Catch ex As Exception
        MessageBox.Show("No se puede conectar con la base de datos")
        End
    End Try
End Function

that does both but you never call that method, so neither gets done. If you're going to have that method at all, call it. Call it before you call ExecuteReader.

A much better idea would be to create all your ADO.NET objects where you use them, including the connection. Put your connection string in a common location but then create your connection, command, data adapter and data reader objects where you want to use them, e.g.

Private connectionString As String = New MySqlConnectionStringBuilder With {.Server = "server name",
                                                                            .Database = "database name",
                                                                            .UserID = "user name",
                                                                            .Password = "password"}.ConnectionString

Private Sub DoSomething()
    Using connection As New MySqlConnection(connectionString),
          command As New MySqlCommand("SELECT * FROM SomeTable WHERE SomeColumn = @SomeColumn", connection)
        command.Parameters.Add("@SomeColumn", MySqlDbType.VarChar, 50).Value = someValue

        connection.Open()

        Using reader = command.ExecuteReader()
            While reader.Read()
                '...
            End While
        End Using
    End Using
End Sub

Notice the Using blocks, the use of a connection string builder and the use of parameters rather than string concatenation to insert values into the SQL code. Because the connection and data reader are created with Using statements, they will be disposed and closed implicitly at the end of the corresponding blocks.