user level permission

[cyberdaemon]

good day. i have a problem with my login from.
i have my table under SQL server 2008. which is
tbl_user,with a field of userid,username,password and category
i created a simple login form but that's fine and working as a simple login..
my problem is that i want to add user level for the program. if the category is user he/she will be restricted for some form, but if the user category admin then he/she will access to all form on the system..

here my code

private sub btnOK_Click ()
Dim con As New SqlClient.SqlConnection("Data Source=BRMS-SERVER;Initial Catalog=CavsuDB;Integrated Security=True")

        Dim cmd As New SqlClient.SqlCommand("SELECT username,password,category FROM tbl_user WHERE username = @username AND password = @password", con)


        Dim usernameParam As New SqlClient.SqlParameter("@username", Me.txtUser.Text)
        Dim passwordParam As New SqlClient.SqlParameter("@password", Me.txtPass.Text)

        cmd.Parameters.Add(usernameParam)
        cmd.Parameters.Add(passwordParam)

        cmd.Connection.Open()

        Dim reader As SqlClient.SqlDataReader = cmd.ExecuteReader()

        If reader.HasRows Then
            Main.Show()
            Me.Hide()


            If chkAdmin.Checked = True Then
                cmd.CommandText = "SELECt username, password FROM tbl_user where category  'Admin' "

                Main.AddUserToolStripMenuItem.Enabled = True
                Main.DocumentToolStripMenuItem.Enabled = True
                Main.SubjectsToolStripMenuItem.Enabled = True
                Main.FacultyToolStripMenuItem.Enabled = True
                Main.DepartmentToolStripMenuItem.Enabled = True

                '       

            ElseIf chkAdmin.Checked = False Then
                cmd.CommandText = "SELECT username,password FROM tbl_user WHERE category = 'user'"


                Main.AddUserToolStripMenuItem.Enabled = False
                Main.DocumentToolStripMenuItem.Enabled = True
                Main.SubjectsToolStripMenuItem.Enabled = False
                Main.FacultyToolStripMenuItem.Enabled = False
                Main.DepartmentToolStripMenuItem.Enabled = False

            End If

        Else
            MessageBox.Show("username and or password are not found")
            txtUser.Clear()
            txtPass.Clear()
            txtUser.Focus()

        End If

        con.Close()

[Smith5646]

What is the problem? Is the program blowing up? Is it not returning rows? Is it returning wrong rows?

[Gé48]

Maybe this helps you
In Solution Explorer right click on My Project to open it.
Then click on View Windows Settings is where you can adjust userlevel.

[cyberdaemon]

@Smith5646 nothing seems to be a problem in my code, but when i added a user which is on the other form, the user will is added to the database, but when i input the new username and new password the code cannot read the new username and new password. i already debug my code, but it seems i cant see the errors on it. once the debugger is on the hasrows property it automatically moved to the else statement which i cant understand why it is happening..

do u have any idea regarding the error

[cyberdaemon]

@Smith5646 the program does not returning at the 4 row of the database, the program can only return 3 rows.. when program will read the value in the 4th row it automatically return a invalid messagebox.. but the username and the password is already added on the database. how would i solve this kind of problem..

[Smith5646]

Now I understand the issue but really don't have a good answer. The code in lines 2 - 17 look right to me.

I am assuming they type into txtUser and txtPass and then click OK which sends them into this sub.

I'm not sure what you are doing with lines 23 and 34 and if they are causing you problems somehow. I doubt it but thought it was worth mentioning.

Here are a couple things to try.
1) Write a quick block of code to retrieve all rows from the DB and see if the row in question shows up that way. You can also do a "select count(*) from tbl_user" to see how many rows it thinks are in the DB. If these work, it has to be something in the select.
2) See if the values in the DB have extra spaces or something at the end of them.
3) See if the values in your textboxes have extra spaces or something at the end of them.
4) If you have another way to access your DB, try doing the select command outside of your program to see if it returns the row.

Let me know what you find and we'll keep narrowing it down.

[cyberdaemon]

i already re created that code

Dim con As New SqlClient.SqlConnection("Data Source=BRMS-SERVER;Initial Catalog=C:\DOCUMENTS AND SETTINGS\USER\DESKTOP\WINDOWSAPPLICATION1\PROJECT\CAVSUDB.MDF;Integrated Security=True")
        Dim cmd As New SqlClient.SqlCommand("SELECT username,password,category FROM tbl_user WHERE username = '" & txtUser.Text & "' AND password = '" & txtPass.Text & "'", con)

        Dim usernameParam As New SqlClient.SqlParameter("@username", Me.txtUser.Text)
        Dim passwordParam As New SqlClient.SqlParameter("@password", Me.txtPass.Text)

        cmd.Parameters.Add(usernameParam)
        cmd.Parameters.Add(passwordParam)

        cmd.Connection.Open()


        Dim reader As SqlClient.SqlDataReader = cmd.ExecuteReader()



        If reader.Read Then

            If reader(2) = "admin" Then

                Main.Show()
                Me.Hide()


                Main.AddUserToolStripMenuItem.Enabled = True
                Main.DocumentToolStripMenuItem.Enabled = True
                Main.SubjectsToolStripMenuItem.Enabled = True
                Main.FacultyToolStripMenuItem.Enabled = True
                Main.DepartmentToolStripMenuItem.Enabled = True


            ElseIf reader(2) = "user" Then
                Main.Show()
                Me.Hide()

                Main.AddUserToolStripMenuItem.Enabled = False
                Main.DocumentToolStripMenuItem.Enabled = True
                Main.SubjectsToolStripMenuItem.Enabled = False
                Main.FacultyToolStripMenuItem.Enabled = False
                Main.DepartmentToolStripMenuItem.Enabled = False

            End If


        Else
            MessageBox.Show("username and or password are not found")
            txtUser.Clear()
            txtPass.Clear()
            txtUser.Focus()

        End If

tbl_user
username password category
admin admin admin
user user admin
a a user
s s user

my problem is that they dont return the added row on the table.
the scenario is this.
I have another table w/c will add new user. once they add user on the other form. it will automatically add that user to this table.(actually no problem regarding adding of user). but when i type the latest username and password a message will appear saying that the username and password are not found.. how come that the username and password are not found since i query my tbl_user and i see that the username and the password that i type is actually there..

do i have to instantiate my reader? where should i place it.. i am lost... (head spin)

[Smith5646]

This has me baffled so bear with me for some stupid questions.

If you have 3 records in the DB and then add a 4th record, you say it does not find the 4th record.

If you exit the app and restart, does it find the 4th record?

If you add a 4th and 5th record, does it find the 4th record but not the 5th or does it not find either?

[cyberdaemon]

@Smith5646 the program can only read 3 records, even you add another it will only read 3 records. even if the record is listed on the database it will not read it.. only that 3 records..

once you restart the application it will not read the record.. only 3 record will be read from the database..

[Smith5646]

I'm totally confused now. Can you attach the .MDF file to this thread so I can look at it?

[cyberdaemon]

@Smith4656

Good day,

Attached here is my database and also i want to paste the code for adding the username and password in the table.

Try
            con.ConnectionString = "Data Source=BRMS-SERVER;Initial Catalog=C:\DOCUMENTS AND SETTINGS\USER\DESKTOP\WINDOWSAPPLICATION1\PROJECT\CAVSUDB.MDF;Integrated Security=True"
            con.Open()
            cmd.Connection = con
            cmd.CommandText = "insert into tbl_user (username,password,category) values ('" & txtUsername.Text & " ',' " & txtPassword.Text & " ','" & cmbCategory.SelectedValue & "')"
            cmd.ExecuteNonQuery()
            MessageBox.Show("One User  has been Added", "Add Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            txtUsername.Clear()
            txtPassword.Clear()
            cmbCategory.SelectedIndex = 0

            txtUsername.Focus()
        End Try
        con.Close()

that is my code for adding user in the database, the problem that i encounter is that when i add username and password using that code, it saves the username and password but when i will input the username and password a message will appear says "invalid password"
thanks

[Smith5646]

I didn't realize the .MDF is SQL server (should have looked at the connection statement closer). Right now I don't have access to a machine with SQL server. Can you export to something such as Access, Excel, .csv?

[Begginnerdev]

Is SQL client required? If not, I can try to rewriting using an OLEDB client, which I am more famillar with. But this will require you to test it due to your database being local.

[Smith5646]

I just want an exported copy of the data that I can read. I can't open a .MDF.

[cyberdaemon]

attach here is my database...

[Smith5646]

I am totally lost on this and not sure how to help further. Did you try the other suggestions that I made earlier? What were the results (reply using the question numbers)?

Syntax in the below code will probably need tweeked because I am free typing and not using Studio to edit / verify.

1) Write a block of code to retrieve all rows from the DB and see if the row in question shows up that way.

Dim cmd As New SqlClient.SqlCommand("SELECT * FROM tbl_user", con)
cmd.Connection.Open()
Dim reader As SqlClient.SqlDataReader = cmd.ExecuteReader()
while reader.HasRows
  messagebox.show(reader(1))
end while

2) Do a "select count(*) from tbl_user" to see how many rows it thinks are in the DB.

Dim cmd As New SqlClient.SqlCommand("SELECT count(*) FROM tbl_user", con)
cmd.Connection.Open()
Dim reader As SqlClient.SqlDataReader = cmd.ExecuteReader()
messagebox.show(reader(0))

3) See if the values in the DB have extra spaces or something at the end of them.
Try changing you inserts to be .TEXT.TRIM

4) See if the values in your textboxes have extra spaces or something at the end of them.
Try changing you selects to be .TEXT.TRIM

5) If you have another way to access your DB, see if it returns the row. For example, in visual studio you can "show table data".

[Begginnerdev]

Ok,try this.

This should work fine with SQL Server 2008

'Be sure to import System.Data.OleDB

        Dim constr As String = "Provider=SQLOLEDB;Server=BRMS-SERVER;Database=CavsuDB;Integrated Security=SSPI"
        Dim sstr As String = "SELECT username,password,category FROM tbl_user WHERE username='" & txtUser.Text & " AND password='" & txtPass.Text & "'"

        Dim con As New OleDbConnection(constr)
        Dim cmd As New OleDbCommand(sstr, con)
        Dim da As New OleDbDataAdapter
        Dim ds As New DataSet
        Dim dt As New DataTable

        Try
            con.Open()

            'Set the string to the data adapter
            da.SelectCommand = cmd
            da.Fill(ds, "GetData")
            dt = ds.Tables("GetData")

            If dt IsNot Nothing Then
                If dt.Rows.Count > 0 Then
                    Main.Show()
                    Me.Hide()

                    If chkAdmin.Checked = True Then

                        cmd.CommandText = "SELECT username,password FROM tbl_user WHERE categor='Admin'"
                        da.SelectCommand = cmd
                        da.Fill(ds, ("adminUsers"))
                        dt.Clear()
                        dt = ds.Tables("adminUsers")
                        If dt IsNot Nothing Then
                            If dt.Rows.Count > 0 Then
                                Main.AddUserToolStripMenuItem.Enabled = True
                                Main.DocumentToolStripMenuItem.Enabled = True
                                Main.SubjectsToolStripMenuItem.Enabled = True
                                Main.FacultyToolStripMenuItem.Enabled = True
                                Main.DepartmentToolStripMenuItem.Enabled = True
                            Else
                                MsgBox("There was a problem retrieving the admin users.", MsgBoxStyle.OkOnly)
                            End If
                        Else
                            MsgBox("Admin table is empty.", MsgBoxStyle.OkOnly)
                        End If
                    ElseIf chkAdmin.Checked = False Then
                        cmd.CommandText = "SELECT username,password FROM tbl_user WHERE category='user'"
                        da.SelectCommand = cmd
                        da.Fill(ds, ("Users"))
                        dt.Clear()
                        dt = ds.Tables("Users")
                        If dt IsNot Nothing Then
                            If dt.Rows.Count > 0 Then
                                Main.AddUserToolStripMenuItem.Enabled = False
                                Main.DocumentToolStripMenuItem.Enabled = True
                                Main.SubjectsToolStripMenuItem.Enabled = False
                                Main.FacultyToolStripMenuItem.Enabled = False
                                Main.DepartmentToolStripMenuItem.Enabled = False
                            Else
                                MsgBox("There was a problem retrieving the users.", MsgBoxStyle.OkOnly)
                            End If
                        Else
                            MsgBox("User table is empty.", MsgBoxStyle.OkOnly)
                        End If

                            End If
                        Else
                            MsgBox("**USER NOT FOUND**", MsgBoxStyle.OkOnly)
                        End If
            Else
                MsgBox("Data table is empty.", MsgBoxStyle.OkOnly)
            End If
        Catch ex As Exception
            MsgBox("Exception From: " & ex.Source & vbCrLf & ex.Message & vbCrLf & "Could not connect to server and retrieve data.", MsgBoxStyle.OkOnly)
        End Try
    End Sub