How do I check if a user has a certain role in their profile

[kismetgerald]

Good evening all,

I need some help. I'm trying to query my MySQL database to see if the currently logged on user to my application has certain rights/role. If the queried value does exist, then I want to enable a certain menu item.

Your help would be greatly appreciated.

Here's my code - what am I not doing right?:

[code2=vbnet]Public Sub checkAccessLevel()
Dim dbConn As New MySqlConnection(String.Format("Server={0};Port={1};Uid={2};Password={3};Database=parts", FormLogin.ComboBoxServerIP.SelectedItem, My.Settings.DB_Port, My.Settings.DB_UserID, My.Settings.DB_Password))
Dim dbQuery As String = "SELECT Level = 'Admin' FROM users WHERE username = '" & FormLogin.TextBoxUsername.Text & "'"
Dim dbAdapter As New MySqlDataAdapter(dbQuery, dbConn)
Dim dbData As MySqlDataReader

Try
dbConn.Open()
dbData = dbAdapter.SelectCommand.ExecuteReader
If dbData.HasRows() = True Then
TSMenuItemOptions.Enabled = True
Me.Refresh()
Else
TSMenuItemOptions.Enabled = False
End If
dbData.Close()

Catch ex As Exception
MessageBox.Show("A DATABASE ERROR HAS OCCURED" & vbCrLf & vbCrLf & ex.Message & vbCrLf & _
vbCrLf + "Please report this to the IT/Systems Helpdesk at Ext 131.")
End Try
dbAdapter.Dispose()
dbConn.Close()
End Sub[/code2]

[kismetgerald]

This is what I've tried so far, and it's not working as intended:

[code2=vbnet]Public Sub checkAccessLevel()
Dim dbConn As New MySqlConnection(String.Format("Server={0};Port={1};Uid={2};Password={3};Database=parts", FormLogin.ComboBoxServerIP.SelectedItem, My.Settings.DB_Port, My.Settings.DB_UserID, My.Settings.DB_Password))
Dim dbQuery As String = "SELECT Level FROM users WHERE username = '" & FormLogin.TextBoxUsername.Text & "'"
Dim dbAdapter As New MySqlDataAdapter(dbQuery, dbConn)
Dim dbData As MySqlDataReader

Try
dbConn.Open()
dbData = dbAdapter.SelectCommand.ExecuteReader
dbData.Read()
While dbData.Read
Select Case UCase(dbData(0).ToString)
Case Is = "Admin"
TSMenuItemOptions.Enabled = True
Case Is = "Manager"
TSMenuItemOptions.Enabled = True
Case Is = "User"
TSMenuItemOptions.Enabled = False
End Select
End While
dbData.Close()

Catch ex As Exception
MessageBox.Show("A DATABASE ERROR HAS OCCURED" & vbCrLf & vbCrLf & ex.Message & vbCrLf & _
vbCrLf + "Please report this to the IT/Systems Helpdesk at Ext 131.")
Finally
dbAdapter.Dispose()
dbConn.Close()
End Try

End Sub[/code2]

I have set the initial Enabled property of the menu item (TSMenuItemOptions) to Disabled and it works okay. When the code gets executed, I see in the Text Visualizer during debugging that the proper user's level is being pulled from the DB. This results in the first line of the Select Case being evaluated and the menu item enabled. But after that, it stays enabled even when the entire application is closed and reopened.

Am I doing something wrong?

[kismetgerald]

Hey guys,

I managed to resolve my own question by modifying the Select Case statement....

ORIGINAL (Incorrect):
[code2=vbnet]Select Case UCase(dbData(0).ToString)[/code2]MODIFIED (Correct):
[code2=vbnet]Select Case dbData(0).ToString[/code2]

[brandonio21]

I'm glad you got it all sorted out!

The problem with this snippet of code:
[code2=vbnet]Select Case UCase(dbData(0).ToString)
Case Is = "Admin"
TSMenuItemOptions.Enabled = True
Case Is = "Manager"
TSMenuItemOptions.Enabled = True
Case Is = "User"
TSMenuItemOptions.Enabled = False
End Select[/code2]

Is that UCase actually converts whatever string you input as a parameter into an uppercase string, so to fix the problem you must change the code to this:
[code2=vbnet]Select Case UCase(dbData(0).ToString)
Case Is = "ADMIN"
TSMenuItemOptions.Enabled = True
Case Is = "MANAGER"
TSMenuItemOptions.Enabled = True
Case Is = "USER"
TSMenuItemOptions.Enabled = False
End Select[/code2]

[kismetgerald]

Oh okay, didn't realize that. Thanks.