(VB.NET) How to verify login permissions using a DataSet and QueryBuilder?

Question

I wasnt quite sure how to properly write the question so bear with me please, here i'll explain as much as I can. And sorry if its too long.
I am building a basic school project that allows employees to register their time-in and time-out of the job to an Access Database, but I am currently struggling with the Login Form.
After many attempts I decided to go the easy way and use both the DataSet designer and the Query Builder from Visual Studio.
This is what I have right now:

An "EmpleadosDataSet" with a "_tblUser" DataTable and its TableAdapter, this adapter uses a query which I've named "GetDataLogin". Heres an image for reference:

What the query does is fill the "_tblUserDataTable" like this:

SELECT [ID EMPLEADO], USUARIO, CONTRASEÑA, PERMISOS  
FROM [_tblUser]  
WHERE (USUARIO = ?) AND (CONTRASEÑA = ?)  

(Notice how I'm not using the PERMISOS at all after selecting it because that´s what I need help with)
For this to work on my code I instantiate my _tblUserDataTable from the EmpleadosDataSet and its TableAdapter:

Private dtLogin As New EmpleadosDataSet._tblUserDataTable  
Private taLogin As New EmpleadosDataSetTableAdapters._tblUserTableAdapter  

Then I pass both onto a button click event with this:

Private Sub _Button2_Acceder_Click(sender As Object, e As EventArgs) Handles _Button2_Acceder.Click  
  
    dtLogin = taLogin.GetDataLogin(_TxtUsuario.Text, _TxtContrasena.Text)  
    If dtLogin.Count > 0 Then  
            MsgBox("Bienvenido")  
    Else  
            MsgBox("No esta registrado")  
    End If  
End Sub  

This works just fine, however, I want to open a different form depending on whether the employee is an "user" or an "admin". This information is stored in the same dataset on a column named "PERMISOS", but I have no clue how to make the query that selects the value from said column and verify it.
My best bet is that I´ll have the query do SELECT PERMISOS and somehow assign that value to a variable that I can then verify with an If statement?
Something with this structure (this is just an example of what I need but I do not know if its the correct procedure, also, value from PERMISOS column is not a variable name or anything, im putting it for reference of what i need to be there):

If **value from PERMISOS column** = "admin" Then  
Me.Hide()  
Form2_admin.Show()  
ElseIf **value from PERMISOS column** = "user" Then  
Me.Hide()  
Form3_user.Show()  

I am not sure if that would be correct at all, bear with me, I havent been able to find how to even do that, assign the value of the column that I already selected with the query to a variable. Every info about this that I find is from people who rather code the whole database connection and queries themselves, haven´t found anything using the designer and query builder that refers to user permissions.
So that's basically what I need help with, how to either use a query or something else to verify user permissions like that, in other words, how to get the -value from PERMISOS column-
And just if clarification is needed, I did also use the Data Source wizard to connect to the Access Database, as it was the easier way, my DataSets are connected to that and filled with the info from it

PS: I understand some of my methods are incorrect, such as storing passwords as plain text, but please understand this is a simple practice about WindowsForms in general, not trying to learn security or others as of right now, will eventually do but right now I just need this.

Answer 1

Hello @edbanshee

Everything needed can be done with TableAdapter components. Full source code is in the following GitHub repository.

Notes

• I didn't match up your table and column names as I already had this code and believe you should be able to adapt to your code.
• I used one form for redirection but of course you can change that to two forms.

.xsd

Here the Users table has UserName and UserPassword columns for the login and UserRole which is either (in this case) user or admin

Login query

Login form

In this case the user gets one try even though the standard is to allow multiple attempts which would be easy to do but kept this simple on purpose. Also note the database is password protected where the password is stored in app.config which we could encrypted.

Public Class LoginForm
    Private Sub cmdLogin_Click(sender As Object, e As EventArgs) Handles cmdLogin.Click

        Dim getLoginDetails As AppDataSet.UsersDataTable =
                UsersTableAdapter.GetDataByLogin(txtUserName.Text, txtPassword.Text)

        Dim result As AppDataSet.UsersRow = getLoginDetails.FirstOrDefault()

        If result IsNot Nothing Then
            Hide()
            Dim f As New Form1(result.UserRole)
            f.ShowDialog()
        Else
            MessageBox.Show($"Contact an admin")
            Application.ExitThread()
        End If

    End Sub
End Class

Main Form code

Public Class Form1

    Private UserRole As String

    Public Sub New(pUserRole As String)

        InitializeComponent()

        UserRole = pUserRole

    End Sub
    Private Sub Form1_FormClosed(sender As Object, e As FormClosedEventArgs) Handles Me.FormClosed
        Application.ExitThread()
    End Sub
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        CustomersTableAdapter.Fill(Me.AppDataSet.Customers)
        Text = $"User Role: {UserRole}"
    End Sub
    Private Sub cmdClose_Click(sender As Object, e As EventArgs) Handles cmdClose.Click
        Close()
    End Sub

    Private Sub CustomersBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) _
        Handles CustomersBindingNavigatorSaveItem.Click

        Validate()

        CustomersBindingSource.EndEdit()
        TableAdapterManager.UpdateAll(Me.AppDataSet)

    End Sub
End Class

---

New code

I updated the repository code as follows. Note that rather than using a string for role I changed to an Enum. Let me know if you need more assistance.

Added the following enum

Namespace Enums
    Public Enum UserRoles
        Admin
        User
    End Enum
End Namespace

Then via project properties, application tab click on application events button and replace the code with the following.

Imports WindowsApplication1.Enums

Namespace My
    Partial Friend Class MyApplication
        Public Property UserRole() As UserRoles
    End Class
End Namespace

Login form

Imports WindowsApplication1.Enums

Public Class LoginForm
    Private Sub cmdLogin_Click(sender As Object, e As EventArgs) Handles cmdLogin.Click

        Dim getLoginDetails As AppDataSet.UsersDataTable =
                UsersTableAdapter.GetDataByLogin(txtUserName.Text, txtPassword.Text)

        Dim result As AppDataSet.UsersRow = getLoginDetails.FirstOrDefault()

        If result IsNot Nothing Then
            Hide()

            My.Application.UserRole = DirectCast([Enum].Parse(GetType(UserRoles),
                                                              result.UserRole), UserRoles)

            If My.Application.UserRole = UserRoles.Admin Then
                Dim f As New AdminForm()
                f.ShowDialog()
            Else
                Dim f As New UserForm()
                f.ShowDialog()
            End If


        Else
            MessageBox.Show($"Contact an admin")
            Application.ExitThread()
        End If

    End Sub
End Class

Form1 is now AdminForm

Public Class AdminForm
    Public Sub New()

        InitializeComponent()

    End Sub
    Private Sub Form1_FormClosed(sender As Object, e As FormClosedEventArgs) Handles Me.FormClosed
        Application.ExitThread()
    End Sub
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        CustomersTableAdapter.Fill(Me.AppDataSet.Customers)
        Text = $"User Role: {My.Application.UserRole}"
    End Sub
    Private Sub cmdClose_Click(sender As Object, e As EventArgs) Handles cmdClose.Click
        Close()
    End Sub

    Private Sub CustomersBindingNavigatorSaveItem_Click(sender As Object, e As EventArgs) _
        Handles CustomersBindingNavigatorSaveItem.Click

        Validate()

        CustomersBindingSource.EndEdit()
        TableAdapterManager.UpdateAll(Me.AppDataSet)

    End Sub
End Class

Added a form named UserForm

Public Class UserForm
    Private Sub UserForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Text = $"User Role: {My.Application.UserRole}"
    End Sub
    Private Sub UserForm_FormClosed(sender As Object, e As FormClosedEventArgs) Handles Me.FormClosed
        Application.ExitThread()
    End Sub

    Private Sub cmdClose_Click(sender As Object, e As EventArgs) Handles cmdClose.Click
        Close()
    End Sub
End Class

Answer 2

I think your thinking should be fine.
Now that the logged-in user can be verified, it is enough to check its PERMISOS field.

        dtLogin = taLogin.GetDataLogin(_TxtUsuario.Text, _TxtContrasena.Text)  
        If dtLogin.Count > 0 Then  
            MsgBox("Bienvenido")  
            Dim row = dtLogin.FirstOrDefault()  
            If row("PERMISOS").ToString() = "admin" Then  
                Me.Hide()  
                New Form2().Show()  
            ElseIf row("PERMISOS").ToString() = "user" Then  
                Me.Hide()  
                New Form3().Show()  
            End If  
        Else  
            MsgBox("No esta registrado")  
        End If  

I don't often connect to the database in this way.
If it is just a simple connection when testing by myself, I will use OLEDB:
vb.net as I query Access database and display it in listview?
Otherwise, I might choose to use EF Core. Someone wrote a package for the Access database:
EntityFrameworkCore.Jet

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.