Question How to create multiple user accounts in database

[Godis]

I am developing a windows form application project and want to create separate accounts for the Admin, Accountant, Manager and Guest. Inside the project are login dashboards for these users.

The user access control table is created in the sql server 2016 and connected successfully to the project. Login form with the various access has also been successfully created. When each user logs in I want his respective dashboard to show.

When I run the project, an error message shows up. Please I will appreciate if someone can help me fix the problem.

I attach the code and the error message for correction.

Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Data
Imports System.Drawing
Imports System.Linq
Imports System.Text
Imports System.Threading.Tasks
Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Windows.Forms


Public Class frmUserControl

    Dim cmd As New SqlCommand
    Dim da As New SqlDataAdapter
    Dim dt As New DataTable

    Private Sub frmUserControl_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    End Sub
    Private Sub btnExit_Click(sender As Object, e As EventArgs) Handles btnExit.Click
        End
    End Sub

    Private Sub btnLogin_Click(sender As Object, e As EventArgs) Handles btnLogin.Click
        Dim con As SqlConnection = New SqlConnection("Data Source=DAVID;Initial Catalog=Projects;Integrated Security=True")
        con.Open()

        Dim cmd As SqlCommand = New SqlCommand("select * from UsersAccess where username = '" & txtUsername.Text & "' and password = '" & txtPassword.Text & "'", con)


        Dim da As New SqlDataAdapter(cmd)
        Dim dt As New DataTable()
        Dim username = txtUsername
        Dim password = txtPassword
        da.Fill(dt)

        If (dt.Rows.Count > 0) Then
            MessageBox.Show("You Are Successfully Logged In!")
        End If


        txtUsername.Clear()
        txtPassword.Clear()
        Me.Hide()



        cmd = New SqlCommand("select Role from UsersAccess where username = @username", con)
        cmd.Parameters.AddWithValue("@username", username)
        Dim role As String = cmd.ExecuteScalar()

        MsgBox("You Are Welcomed Back" + role)
        con.Close()

        Dim admin As New Admin
        Dim acc As New Accountant
        Dim mg As New Manager
        Dim gt As New Guest

        If role = "Admin" Then
            admin.Show()
        ElseIf role = "Accountant" Then
            acc.Show()
        ElseIf role = "Manager" Then
            mg.Show()
        ElseIf role = "Guest" Then
            gt.Show()
        Else
            MessageBox.Show("Invalid Username or Password; Please Verify!")
          End
            con.Close()
        End If

    End Sub
End Class

 

[jmcilhinney]

Firstly, please don't post screenshots of code or error messages. They are both test so they should be posted as text, formatted appropriately. You have done that with the code, which is good, but the error message was enough to make out on a hi-res monitor, so would have been impossible on a phone. The code editor lets you highlight specific lines, so you can indicate the line that throws an exception, and then all the error information should be posted as text. If you think that an additional screenshot can help then provide it but don't make that the only option.

 

[jmcilhinney]

As for the issue, this seems to be a perfect example of why you should use proper naming conventions. Based on the error message, here:

cmd.Parameters.AddWithValue("@username", username)

username is not a user name at all but rather a TextBox that contains the user name. In that case, the name used should indicate that. You should already know that, in order to get the data into or out of a TextBox you use it's Text property. That means that your code should be this:

Dim username = usernameTextBox.Text

cmd.Parameters.AddWithValue("@username", username)

or even just this:

cmd.Parameters.AddWithValue("@username", usernameTextBox.Text)

 

[chrisjlocke1]

In the first SQL block, you're building a SQL statement... so what happens if the user is called "O'Brien" ?
What happens if their name is "a';drop table users;" - you might want to try that (on a test database, obviously)
You're using parameters in the second block ... why the different styles?

Are you storing passwords in the database as plain text???