vb.net - Object reference not set to an instance of an object in con.State = ConnectionState.Closed

Question

Public Class LoginForm
    Dim con As OleDbConnection
    Dim dbProvider As String = "Provider=Microsoft.ACE.OLEDB.12.0;"
    Dim dbSource As String = "Data Source=C:UsersWindows10DesktopdbInventory.accdb;"

    Private Sub btnRegister_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRegister.Click
        Me.Hide()
        RegistrationForm.Show()
    End Sub
    Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
        If txtUserName.Text = Nothing Or txtPassword.Text = Nothing Then
            MessageBox.Show("Please enter correct username or password", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If
        If con.State = ConnectionState.Closed Then
            con.Open()
        End If

        Using login As New OleDbCommand("SELECT COUNT(*) FROM tbl_EmployeesInfo WHERE [UserName] = @UserName AND [Password] = @Password", con)
            login.Parameters.AddWithValue("@UserName", OleDbType.VarChar).Value = txtUserName.Text.Trim
            login.Parameters.AddWithValue("@Password", OleDbType.VarChar).Value = txtPassword.Text.Trim

            Dim logincount = Convert.ToInt32(login.ExecuteScalar())
            If logincount > 0 Then
                Me.Hide()
                InventoryForm.Show()
            Else
                MessageBox.Show("UserName or Password is wrong. Try again", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                txtUserName.Clear()
                txtPassword.Clear()
            End If
        End Using
        con.Close()
    End Sub
End Class

Answer 1

Get rid of the Form level con. Database objects need to be local to the method where they are used so the can be disposed. You should never have to check connection state.

Glad to see the use of Using blocks but your connection needs to be in the Using too. Note the New keyword. Note the comma at the end of the first Using line. This means the Using continues and includes the command.

You have confused .AddWithValue and .Add. It is the .Add method that takes the parameter name and the datatype.

Don't show message boxes or do other things with the connection open. After all the user could have gone to lunch and you are tying up a connection. Declare logincount outside the Using block so it can be seen after End Using. Connections are precious resources. Open the connection directly before the .Execute... and close with the End Using as soon as possible.

Lastly, passwords should never be stored as plain text. This is beyond the scope of this question but look into encryption.

Dim conString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:UsersWindows10DesktopdbInventory.accdb;"

Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
    If txtUserName.Text = Nothing Or txtPassword.Text = Nothing Then
        MessageBox.Show("Please enter correct username or password", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
    End If
    Dim logincount As Integer
    Using con As New OleDbConnection(conString),
        login As New OleDbCommand("SELECT COUNT(*) FROM tbl_EmployeesInfo WHERE [UserName] = @UserName AND [Password] = @Password", con)
        login.Parameters.Add("@UserName", OleDbType.VarChar).Value = txtUserName.Text.Trim
        login.Parameters.Add("@Password", OleDbType.VarChar).Value = txtPassword.Text.Trim
        con.Open()
        logincount = CInt(login.ExecuteScalar())
    End Using
    If logincount > 0 Then
        Me.Hide()
        InventoryForm.Show()
    Else
        MessageBox.Show("UserName or Password is wrong. Try again", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        txtUserName.Clear()
        txtPassword.Clear()
    End If
End Sub