How to validate login using MySQL Database in VB?

Question

I have a form with two textboxes and one Login button, the textbox for the username is called txtUsername, and the textbox for the password is called txtPasswd, with the button being btnLogin. I have inserted a username and password into my database called accountinfo, and now I would like to login and show a specific form(being frmProduct, if the user successfully logs in) if their login matches any login in my database, and if it doesn't match any login in the database, a msgbox is shown saying "Oops! The requested username does not exist in the database!" I'm stuck on how to set this up properly, here's what I have tried so far.

Imports MySql.Data.MySqlClient

Public Class frmLogin


Private Sub frmLogin_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim ServerString As String = "Server=localhost:8080;User Id=root;Password=;Database=accountinfo"
Dim SQLConnection As MySqlConnection = New MySqlConnection
End Sub

Private Sub btnLogin_Click(sender As Object, e As EventArgs) Handles btnLogin.Click

Dim dbconnection As MySqlConnection

Dim cmd As MySqlCommand = New MySqlCommand
Dim reader As MySqlDataReader



    dbconnection.Open()



    cmd.CommandText = "SELECT Status FROM accountinfo WHERE UserName = ?UserName AND Password = ?Password"
    cmd.Parameters.Add(New MySqlParameter("?UserName", txtUsername.Text))
    cmd.Parameters.Add(New MySqlParameter("?Password", txtPasswd.Text))
    cmd.Connection = dbconnection
    reader = cmd.ExecuteReader
    dbconnection.dispose()



    If reader.HasRows() Then
        MessageBox.Show("Login successful!", "Welcome")
        frmProduct.Show()
    Else 
        MessageBox.Show("Oops! Login unsuccessful!")
    End If

End Sub
End Class

Answer 1

For Form load:

Private Sub frmLogin_Load(sender As Object, 
                e As EventArgs) Handles MyBase.Load
' no code here.
' nothing interesting happens
End Sub

Click event/Log in:

Dim conStr = "Server=localhost:8080;User Id=root;Password=;Database=accountinfo"
Dim SQL = "SELECT `Status` FROM accountinfo WHERE UserName = @uname AND `Password` = @pword"

Dim frmP As New frmProduct

' this object will be closed and dispose @ End Using
Using dbCon As New MySqlConnection(conStr)
    ' the command object likewise
    Using cmd As New MySqlCommand(SQL, dbCon)

        dbCon.Open()
        cmd.Parameters.Add(New MySqlParameter("@uname", txtUsername.Text))
        cmd.Parameters.Add(New MySqlParameter("@pword", txtPasswd.Text))

        ' create a Using scope block for the reader
        Using rdr As MySqlDataReader = cmd.ExecuteReader

            If rdr.HasRows Then
                MessageBox.Show("Login successful!", "Welcome")
                frmP.Show()
            Else
                MessageBox.Show("Oops! Login unsuccessful!")
            End If
        End Using
    End Using           ' close/dispose command

End Using               ' close/dispose connection

Important Notes

Do not store passwords as plain text. Hash them.

You should create new accounts for the app to use to connect to the DB. Exposing the admin/root passwords on the World Wide Web to the Whole Wide World is a bad idea.

Do Not use Default form instances If the form class is named frmProduct create a New instance of it for use in the app. Forms are just classes. It says so at the top of each one:

Public Class frmLogIn

So use them to create form objects as shown.

Scope
Dim/Private declares variables and their Type. Thus, Dim dbconnection As MySqlConnection does not create a connection object. It just says 'I want to use a MySql connection and it will be named 'dbconnection''.

Where the Dim/Private/Public line occurs determines the Scope or where that variable/object exists. When declared inside an event sub as with your dbconnection, then that object exists only there.

To create a form/class level variable, declare them at the form level:

Public Class Form1
    Private SomethingINeedLater As String

SomethingINeedLater will be available anywhere in the form.

Code such as this:

Dim frmP As New frmProduct

Does both: Dim declares the object variable, New creates an instance of that class.

In VB just about everything that results in indentation creates a new Block Scope. So, anything you declare inside a Using / End Using block (e.g. Dim foo As String = "Ziggy") will exist only there. That is why I declared the frmProduct well before the code uses it.

Finally, both "Status" and "Password" are reserved words for MySql and all/nearly all databases, so I escaped it in the SQL. Avoid columns and tables which are Reserved Words to avoid having to escape them.