Member Avatar for azdonald

Please i'm developing an application with vb.net and mysql. I want to add additional users to the application but with different access levels. this is my original code

sql = "select username, password from users where (username = '" & UsernameTextBox.Text & "') and (password = '" & PasswordTextBox.Text & "')"
        conn.ConnectionString = "server=localhost;" _
                            & "database=cargomanager;" _
                            & "userid=root;" _
                            & "password=admin;"


        Try
            conn.Open()
        Catch myerror As MySqlException
            MsgBox("sorry connection failed")
        End Try
        command.Connection = conn
        command.CommandText = Sql
        adapter.SelectCommand = command
        Dim mydata As MySqlDataReader
        mydata = command.ExecuteReader()
        

        If mydata.HasRows = 0 Then
            MsgBox("Invalid username/password", MsgBoxStyle.Information)
        Else
            Form1.Show()
            Me.Close()
        End If.

If i add a third parameter named level to the sql statement, how do i determine which form shows???

Edited by Nick Evan because: Fixed code-tags
  • 3 Contributors
  • 3 Replies
  • 128 Views
  • 10 Hours Discussion Span
  • Latest Post Latest Post by azdonald

Dani AI

Generated

: the reason HasRows feels useless here is that it only says whether any row exists. To get field values from the result the reader must be advanced to a row (call Read()), then retrieve the level column and branch on it. The following snippet shows a safe, compact pattern that uses parameters, advances the reader, handles DBNull, and uses a Select Case to open the appropriate form.

Using cmd As New MySql.Data.MySqlClient.MySqlCommand("SELECT level FROM users WHERE username=@u AND password=@p", conn)
    cmd.Parameters.AddWithValue("@u", UsernameTextBox.Text)
    cmd.Parameters.AddWithValue("@p", PasswordTextBox.Text) ' store hashed passwords instead of plaintext
    Using rdr As MySql.Data.MySqlClient.MySqlDataReader = cmd.ExecuteReader()
        If rdr.Read() Then
            Dim levelValue As Integer = If(IsDBNull(rdr("level")), 0, Convert.ToInt32(rdr("level")))
            Select Case levelValue
                Case 1
                    AdminForm.Show()
                Case 2
                    NormalUserForm.Show()
                Case Else
                    GuestForm.Show()
            End Select
        Else
            MessageBox.Show("Invalid username/password")
        End If
    End Using
End Using

Build on 's idea by mapping numeric role codes to named constants or an Enum so logic is easy to read and change. As pointed out, database-level permissions can help protect data, but for UI navigation it is often simpler to centralize role checks in the app and enable/disable features rather than maintain many separate forms.

Quick tips: always use parameterized queries to avoid SQL injection; never store plaintext passwords (use PBKDF2/bcrypt and compare hashes); handle DBNull when reading columns; close or dispose connections/readers (Using blocks help). If Read() returns False, verify the WHERE clause and the stored values (including password hashing and column types).

Recommended Answers

All 3 Replies

Member Avatar for zortec

You would add a level field to the users table and use a switch/case or a series of if statements to check what access the user has. Once you determine that, send them off to the appropriate form. I would create a separate query with the username and level to check access.

Edited by zortec because: n/a
Member Avatar for TomW

There are several ways you can accomplish this. You can either define there security through coding as mentioned above and then handle it in the project depending on the users level or you can define it right in Sql Server itself by assigning the proper permissions per table for each of the users.

Member Avatar for azdonald

I already have a field called level in the database. My problem comes from the

mydata.hasrows

this returns true or false so there is no way i know of to read the level.
I know its going to take a couple of ifs statement.. its just how to read the values i havnt figured out yet.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.