VB.Net Login form using SQL Server with Roles that redirect to different forms-CodePudding

I'm currently working on an employee management system on VB.net and SQL Server as the database.

This is what my current database table looks like:

This is what the login form looks like:

I want this login form and that single login button to accommodate both admin and employee roles. Each roles go to different forms. This is what my current code looks like:

Private Sub logIn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonLogin.Click
        Dim connectionLogin As New SqlConnection(connectionstring)
        sqlLogin = "SELECT (1000) [Login]
      ,[Password]
      ,[Role]
  FROM [RestaurantDatabase].[dbo].[Login] where  Login ='" & txtUsername.Text & "'"
        connectionLogin.Open()
        sAdapterLogin = New SqlDataAdapter(sqlLogin, connectionLogin)
        SDsLogin = New DataSet()
        sAdapterLogin.Fill(SDsLogin, "login")
        sTableLogin = SDsLogin.Tables("login")
        If sTableLogin.Rows.Count = 0 Then
            MessageBox.Show("Wrong Username", "Unauthorized", MessageBoxButtons.OK, MessageBoxIcon.Stop)
        Else
            If sTableLogin.Rows.Item(0).Item("Password") = txtPassword.Text Then
                formTasks.Show()

            Else
                MessageBox.Show("Wrong Password entered", "Error", MessageBoxButtons.OK, MessageBoxIcon.Stop)
            End If
        End If
    End Sub

How do I do this? My current code doesn't take into consideration the roles. Both admin and employees get redirected to the same form.

CodePudding user response：

I don't know if the answer to the question is Can't we just add the conditional code below?

Private Sub logIn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonLogin.Click
        Dim connectionLogin As New SqlConnection(connectionstring)
        sqlLogin = "SELECT (1000) [Login]
      ,[Password]
      ,[Role]
  FROM [RestaurantDatabase].[dbo].[Login] where  Login ='" & txtUsername.Text & "'"
        connectionLogin.Open()
        sAdapterLogin = New SqlDataAdapter(sqlLogin, connectionLogin)
        SDsLogin = New DataSet()
        sAdapterLogin.Fill(SDsLogin, "login")
        sTableLogin = SDsLogin.Tables("login")
        If sTableLogin.Rows.Count = 0 Then
            MessageBox.Show("Wrong Username", "Unauthorized", MessageBoxButtons.OK, MessageBoxIcon.Stop)
        Else
            If sTableLogin.Rows.Item(0).Item("Password") = txtPassword.Text Then
             
                if  sTableLogin.Rows.Item(0).Item("Role") ="Admin"
                  ''' admin form show
                else
                 ''' order form show
                end if

            Else
                MessageBox.Show("Wrong Password entered", "Error", MessageBoxButtons.OK, MessageBoxIcon.Stop)
            End If
        End If
    End Sub

CodePudding user response：

Regarding my comment to @Think2826 answer, see the example code below:

DataTable table = new DataTable();
table.Columns.Add("Id", typeof(int));
table.Columns.Add("Name", typeof(string));
table.Columns.Add("Role", typeof(string));
table.Rows.Add(1, "john smith", "admin");

var isAdmin = table.Rows[0]["Role"].ToString() == "admin";
Console.WriteLine($"User '{table.Rows[0]["Name"]}' is admin: {isAdmin}");

I think in VB that would be something like:

Dim table As DataTable = New DataTable()
table.Columns.Add("Id", GetType(Integer))
table.Columns.Add("Name", GetType(String))
table.Columns.Add("Role", GetType(String))
table.Rows.Add(New Object() { 1, "john smith", "admin" })
Dim isAdmin As Boolean = table.Rows(0)("Role").ToString() = "admin"
Console.WriteLine(String.Format("User '{0}' is admin: {1}", table.Rows(0)("Name"), isAdmin))

On top of that, what you're doing is very risky - your code is pretty much SQL Injection vulnerable. You should use SQL parameters before sending our your sqlLogin query to the database.

And I agree with @Nick.McDermaid, SSO might be an overkill at this point, but it's worth considering for the future.