CODEWITHSUNDEEP
mysqlvb.net
Swastik Bhattacharyya
Swastik Bhattacharyya

Reputation: 105

Fix the VB.NET error "Connection must be valid and open"

I am getting an error in this login form which I have created which is "Connection must be valid and open, can someone help me, here is the code. I am unable to understand what this error is and I need your help.

Imports MySql.Data.MySqlClient
Public Class LoginForm1

    Dim Username As String
    Dim Password As String
    Public StudentName As String
    'MySql
    Dim connection As New MySqlConnection("Server=server;User ID=id;Password=pass;Database=data;SSLMode=None")

    Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click

        Username = UsernameTextBox.Text
        Password = PasswordTextBox.Text
        StudentName = NameTextBox.Text

        Dim command As New MySqlCommand("SELECT `Username`, `Password`, `FirstName` FROM `people` WHERE `Username` = @username AND `Password` = @password And `FirstName` = @name", connection)
        command.Parameters.Add("@username", MySqlDbType.VarChar).Value = Username
        command.Parameters.Add("@password", MySqlDbType.VarChar).Value = Password
        command.Parameters.Add("@name", MySqlDbType.VarChar).Value = StudentName


        Dim adapter As New MySqlDataAdapter(command)
        Dim table As New DataTable()
        Dim reader As MySqlDataReader = command.ExecuteReader

        adapter.Fill(table)

        If table.Rows.Count = 0 Then
            MessageBox.Show("Invalid Username Or Password", "Invalid Login", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Else

            MessageBox.Show("Logged In", "Logged in Successfully", MessageBoxButtons.OK, MessageBoxIcon.Information)
            MainMenuForm.Show()
            Me.Close()
        End If
    End Sub

    Private Sub Cancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Cancel.Click
        Me.Close()
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim webAddress As String = "http://windowsadvancedstudy.com"
        Process.Start(webAddress)
    End Sub
End Class

Upvotes: 1

Views: 1125

Answers (2)

Oscar
Oscar

Reputation: 13960

Try opening connection before using it.

And use a "using" block for it, so it gest disposed properly.

Also, it's a good practice NOT to store credentials in code, use a config file for it.

Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.Click
Dim connection As MySqlConnection
    Using(connection As New MySqlConnection("Server=server;User ID=id;Password=pass;Database=data;SSLMode=None")
            connection.Open()
            Username = UsernameTextBox.Text
            Password = PasswordTextBox.Text
            StudentName = NameTextBox.Text

            Dim command As New MySqlCommand("SELECT `Username`, `Password`, `FirstName` FROM `people` WHERE `Username` = @username AND `Password` = @password And `FirstName` = @name", connection)
            command.Parameters.Add("@username", MySqlDbType.VarChar).Value = Username
            command.Parameters.Add("@password", MySqlDbType.VarChar).Value = Password
            command.Parameters.Add("@name", MySqlDbType.VarChar).Value = StudentName


            Dim adapter As New MySqlDataAdapter(command)
            Dim table As New DataTable()
            Dim reader As MySqlDataReader = command.ExecuteReader

            adapter.Fill(table)

            If table.Rows.Count = 0 Then
                MessageBox.Show("Invalid Username Or Password", "Invalid Login", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Else

                MessageBox.Show("Logged In", "Logged in Successfully", MessageBoxButtons.OK, MessageBoxIcon.Information)
                MainMenuForm.Show()
                Me.Close()
            End If
    End Using
End Sub

Upvotes: 0

SouXin
SouXin

Reputation: 1564

You don't need to declare reader if you are using adapter.Fill. Remove this line:

Dim reader As MySqlDataReader = command.ExecuteReader

Exactly line above thrown the exception. Because reader required an open connection.

And add the line:

command.CommandType = CommandType.Text

Upvotes: 2

Related Questions