not able fetch Data from a database using MySqlClient in VB and Public Async Variables are inaccessable

Question

This the code i have written, at one point it displayed System.Data.Dataset return in Access level but it wasn't what i was looking for. Username is a string, Password is also a string and Access level is an integer. is there an alternative method of doing this that is more efficient? Also could you explain why i am getting the fault so i don't run into similar things in the future. Thanks in advance

[A picture of the error and what access prints][1]

Public Class Login
    Dim Access_level As Integer
    Private Sub Login_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'Parts.User' table. You can move, or remove it, as needed.
        'Me.UserTableAdapter.Fill(Me.Parts.User)
    End Sub
    Public Async Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        If TextBox1.Text = "" Or TextBox2.Text = "" Then
            MsgBox("Oops ¯\_(ツ)_/¯ " + Err.Description(), MsgBoxStyle.OkOnly, "Enter Value")
        Else
            Try
                Await getDataSet(TextBox1.Text, TextBox2.Text, Access_level)
                Print(username)
                Print(password)
                If username = TextBox1.Text And password = TextBox2.Text Then
                    Dim Access As Integer = Access_level
                    Print(Access)
                    If Access = 1 Then
                        Me.Hide()
                        AdminMainMenu.Show()
                    Else
                        Me.Hide()
                        MainMenu.Show()
                    End If
                End If
            Catch ex As Exception
                'MsgBox("Oops " + Err.Description(), MsgBoxStyle.OkOnly, "Failed to Open")
                'MsgBox("Incorrect login details", MsgBoxStyle.OkOnly)
                System.Windows.Forms.MessageBox.Show(ex.Message)
            End Try
        End If
        TextBox1.Clear()
        TextBox2.Clear()
    End Sub

    Public Async Function getDataSet(username As String, password As String, Access_level As Integer) As Task(Of DataSet)
        Return Await Task.Factory.StartNew(
            Function()
                Dim connectionString = "server=localhost; userid=root; password=; database=partstest1; CharSet=utf8;"
                Dim commandText = "SELECT Username, Password, Accesslevel FROM `user` WHERE `Username` = '" & username & "' and `Password` = '" & SHA256(password) & "';"
                Using connDB = New MySqlConnection(connectionString), objCmd = New MySqlCommand(), objAdpt = New MySqlDataAdapter()
                    connDB.Open()
                    objCmd.Connection = connDB
                    objCmd.CommandText = commandText
                    objCmd.CommandType = CommandType.Text
                    objAdpt.SelectCommand = objCmd
                    Dim objDs = New DataSet()
                    objAdpt.Fill(objDs)
                    Console.WriteLine(objDs)
                    Return objDs
                End Using
            End Function)
    End Function


  [1]: https://i.stack.imgur.com/g9CIj.png

Answer 1

You shouldn't need all the Async stuff to retrieve one piece of data.

The Datbase code:

You can pass the .CommandText and the the command connection directly to the constructor of the command.

You already have the user name and password. Don't retrieve data you don't need.

Always use parameters to avoid Sql injection and make sql statements easier to write.

I assume Sha256(password) is a custon Function.

CommandType.Text is the defalut and does not have to be explicity assigned.

You do not need a DataAdapter. You are not going to update this data here. Anyway your adapter falls out of scope so it is of no value.

You do not need a DataSet. You are only dealing with one piece of data, not multiple tables. Use .ExecuteScalar to return the first column of the first row of the result set.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    If TextBox1.Text = "" OrElse TextBox2.Text = "" Then
        MessageBox.Show("Oops ¯\_(ツ)_/¯  both Username and Password must be filled in.", "Enter Value")
        Exit Sub
    End If
    Try
        Access_level = getAccessLevel(TextBox1.Text, TextBox2.Text)
    Catch ex As Exception
        MessageBox.Show(ex.Message)
        Exit Sub
    End Try

    If Access_level = 1 Then
        Me.Hide()
        AdminMainMenu.Show()
    Else
        Me.Hide()
        MainMenu.Show()
    End If
    TextBox1.Clear()
    TextBox2.Clear()
End Sub

Private Function getAccessLevel(username As String, password As String) As Integer
    Dim retVal As Integer
    Dim connectionString = "server=localhost; userid=root; password=; database=partstest1; CharSet=utf8;"
    Using connDB = New MySqlConnection(connectionString),
                    objCmd = New MySqlCommand("SELECT  Accesslevel FROM `user` WHERE `Username` = @Username and `Password` = @Password", connDB)
        objCmd.Parameters.Add("@Username", MySqlDbType.VarChar, 100).Value = username
        objCmd.Parameters.Add("@Password", MySqlDbType.VarChar, 100).Value = SHA256(password)
        connDB.Open()
        retVal = CInt(objCmd.ExecuteScalar())
    End Using
    Return retVal
End Function

EDIT

Private Function getAccessLevel(username As String, password As String) As Integer
    Dim retVal As Object
    Dim connectionString = "server=localhost; userid=root; password=; database=partstest1; CharSet=utf8;"
    Using connDB = New MySqlConnection(connectionString),
                objCmd = New MySqlCommand("SELECT  Accesslevel FROM `user` WHERE `Username` = @Username and `Password` = @Password", connDB)
        objCmd.Parameters.Add("@Username", MySqlDbType.VarChar, 100).Value = username
        objCmd.Parameters.Add("@Password", MySqlDbType.VarChar, 100).Value = SHA256(password)
        connDB.Open()
        retVal = objCmd.ExecuteScalar()
    End Using
    If retVal Is Nothing Then
        Return -1
    Else
        Debug.Print(CInt(retVal).ToString)
        Return CInt(retVal)
    End If
End Function

The button code...

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    If TextBox1.Text = "" OrElse TextBox2.Text = "" Then
        MessageBox.Show("Oops ¯\_(ツ)_/¯  both Username and Password must be filled in.", "Enter Value")
        Exit Sub
    End If
    Try
        Access_level = getAccessLevel(TextBox1.Text, TextBox2.Text)
    Catch ex As Exception
        MessageBox.Show(ex.Message)
        Exit Sub
    End Try

    If Access_level = 1 Then
        Me.Hide()
        AdminMainMenu.Show()
    ElseIf Access_Level = 0 Then
        Me.Hide()
        MainMenu.Show()
    Else
        MessageBox.Show("Sorry, No match")
    End If
    TextBox1.Clear()
    TextBox2.Clear()
End Sub

Changed retVal to Object.

Removed CInt from objCmd.ExecuteScalar()

Checked for Nothing and returned -1 Else CInt(RetVal)