User Privledges in VB.NEt

[Farhan_B]

Hey people hope you are well

Could You please help me. I am designing a system which when users login i want the system recoginse who is a admin and a user ( option of deleting only to admin)

Using mysql and vb.net

Thank you in advance

[hericles]

The simplest way is to add a roles column to your database table (or create a separate table to hold this info) so when they log in you can extract which role they belong to.

[kingsonprisonic]

The simplest way is to add a roles column to your database table (or create a separate table to hold this info) so when they log in you can extract which role they belong to.

And after login you can store the role in a variable. Now you can control the users operation by creating conditions
like

If role="Admin" Then
'Compute the query
Else
MsgBox ("Sorry this operation is restricted!!")
End If

[Farhan_B]

Thanks guy but what im trying to do is get the server (mysql) to recognize that the a user can only do x y z.

I have 3 tables Users, Department and userRights.

In the user table i have: username, password, and departmentID.

In the Department i have : DepartmentID, and the departmentName

In the userRights table i have :id, SecurityRightID ,DepartmentID ,Can_Add ,Can_Edit Can_Delete.

What i am trying to do is get the database to recognize which user can do what and then display it on the interface Using Visual studio 2010

Thanks guys in advance really appreciate it

[kingsonprisonic]

Ok first store the login info from userRights table when a user loged in. Now on the GUI form just enable or disable the buttons like if the user is admin then DeleteButton is enabled otherwise it will disabled ...

Understand???

[Farhan_B]

Thanks i understand that

as i am a newbie is it possible if you could provide me with a snippet of code to guide me or even link which shows me how to do it

i would be grateful thanks

[kingsonprisonic]

Ok so first make a login form with two textboxes and a button.
Then the main form MainFrm (Where the actual GUI will show. And all the delete,update button belongs...)

Now when user press the button just use sqlquery and grab the info from database if the user is authentic then store the data in some variables

Like

If ds.tables(0).rows(0)(1).ToString.Trim="Admin"
MainFrm.BtnDelete.Enable=True
Else
MainFrm.BtnDelete.Enable=False
End If

[Farhan_B]

so far my looks like this could u advise me what i should do from here thanks

' Users login
        Dim conn As MySqlConnection
        conn = New MySqlConnection()
        conn.ConnectionString = "server=k; user =1; password=; database=;"
        ' See if connection failed
        Try
            conn.Open()
        Catch myerror As MySqlException
            MsgBox("Error!")
        End Try
        ' SQL Query
        Dim myAdapter As New MySqlDataAdapter
        Dim sqlquery = "SELECT * FROM User WHERE userName = '" & txtusername.Text & "' AND password = '" & txtpassword.Text & "'"
        Dim myCommand As New MySqlCommand()
        myCommand.Connection = conn
        myCommand.CommandText = sqlquery
        If conn.State = ConnectionState.Open Then Dim MySqlCommand As New MySqlCommand()
        myCommand.Connection = conn
        myCommand.CommandText = sqlquery
        ' Start Query
        myAdapter.SelectCommand = myCommand
        Dim myData As MySqlDataReader
        myData = myCommand.ExecuteReader()
        ' Now let's see if user exsist
        If myData.HasRows = 0 Then
            MsgBox("invalid Info")
        Else
        End If
        MsgBox("Logged In")
        Me.Hide()
        Form2.ShowDialog()
    End Sub

[kingsonprisonic]

I think your code has SQL Injection Hole.

So never select a user with name and password.

First select * from table where username='something'

then

check for password like this

Try
            db.con.Open()
            Dim com As New SqlDataAdapter("select * from User where userName='" + txtusername.Text + "'", db.con)
            Dim ds As New DataSet
            ds.Clear()
            com.Fill(ds)

            If ds.Tables(0).Rows.Count > 0 Then

                If ds.Tables(0).Rows(0)(1).ToString.Trim = txtpassword.Text.Trim Then
                    MsgBox("Login Successfull!")

                    If ds.Tables(0).Rows(0)(2).ToString.Trim = "Admin" Then
                        MainFrm.BtnDelete.Enable = True
                    Else
                        MainFrm.BtnDelete.Enable = False
                    End If

                    MainFrm.Show()


                Else
                    MsgBox("Invalid Username and Password!!")
                End If


            Else
                MsgBox("Invalid Username and Password!!")
            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            db.con.Close()
        End Try

Assuming User table has three field Username,Password and Usertype..

I assume this for your learning ....

Now understand and try with your tables...

OK...

[Farhan_B]

thanks mate i really appreciated that you have taken the time out to have a look at my work and helped thank you very much i am just working on it now to see if this works if i have any problem i will message you on here

Thank You Once again

[kingsonprisonic]

Ok dear if it works please make this thread as solved...

[Farhan_B]

everything seems to be working however i keep getting two errors before i debug the application it says

1)`db` is not declared. it maybe inaccessible due to its protection level
2)Type 'SqlDataAdapter' is not defined.

[kingsonprisonic]

Ok you have to import

System.Data.SqlClient namespace for this..

Then Create a class

Imports System.Data.SqlClient
Public Class DBConnection
public con As New SqlConnection("Your Connection String..")

End Class

Now create a object of this class on the login form..

Imports System.Data.SqlClient
Public Class Form1
Dim db as New DBConnection

'Now try all rest code... 


End Class

[Farhan_B]

that has has seemed to solve this problem but even when i create a new form (mainForm) and add btnDelete i keep getting this error

"Enable' is not a member of 'System.Windows.Forms.Button'"

what is causing this

[kingsonprisonic]

Sorry for that. Actually i type this in the normal editor so mistaken...

Try

Enabled=True

[Farhan_B]

thanks i solved that issue but now when this code is being debugged it wont allow me to connect to the database. sorry for the hassal. i really am grateful for your help.

below is my code could you find where i am going wrong.and advise me thank u

Imports MySql.Data.MySqlClient

Imports System.Data.SqlClient

Public Class Form3


    Public con As New SqlConnection("server=; user =; password=; database=;")


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim conn As MySqlConnection
        conn = New MySqlConnection()
        conn.ConnectionString = "server=; user =; password=; database=;"
        ' See if connection failed
        Try
            conn.Open()
        Catch myerror As MySqlException
            MsgBox("Error1!")
        End Try
        ' SQL Query
        Dim myAdapter As New MySqlDataAdapter




        Try
            con.Open()
            Dim com As New SqlDataAdapter("select * from login where userName='" + txtusername.Text + "'", con)
            Dim ds As New DataSet
            ds.Clear()
            com.Fill(ds)

            If ds.Tables(0).Rows.Count > 0 Then

                If ds.Tables(0).Rows(0)(1).ToString.Trim = txtpassword.Text.Trim Then
                    MsgBox("Login Successfull!")

                    If ds.Tables(0).Rows(0)(2).ToString.Trim = "Admin" Then
                        MainFrm.btnDelete.Enabled = True
                    Else
                        MainFrm.btnDelete.Enabled = False
                    End If

                    MainFrm.Show()


                Else
                    MsgBox("Invalid Username and Password!!")
                End If


            Else
                MsgBox("Invalid Username and Password!!")
            End If

        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            con.Close()
        End Try

    End Sub
End Class

[kingsonprisonic]

Is this code gives you any error now???

[Farhan_B]

the error occurs when the debugging the code once the details are filled in and the login button is clicked a box appears and says

"a network related or instant related specific error occurred while establishing a connection to sql server.the server was not found or was not accessible. Verified that the instant name is correct and the sql server is configured to allow remote connection (provider.Named Pipes Provider, error :40 Could not open a connection to sql server"

I have checked everything all the login details work etc i dont understand why this error

[kingsonprisonic]

You get this error because this is not a full connectionstring

you use

Public con As New SqlConnection("server=; user =; password=; database=;")

Here no server, no user, no password, no databse info... So the error.

Full connection Strings are like

Public con As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\tammy\documents\visual studio 2010\Projects\dbapp\dbapp\exdb.mdf;Integrated Security=True;User Instance=True")

[Farhan_B]

if the server i am using is samp.inf.brad.ac.uk how would i put this in the code the only way i found a connection is via

Public con As New SqlConnection("server=; user =; password=; database=;")

[kingsonprisonic]

All code you use id for

Microsoft SQL Server Database... Not For MySql

If you need mysql please read this...

http://www.vbmysql.com/articles/vbnet-mysql-tutorials/the-vbnet-mysql-tutorial-part-3

[Farhan_B]

I am having no luck at all i cannot seem to conenct it at all i am not using windows mysql but MySql how would i do it please help :(

[kingsonprisonic]

visit the link i previously posted....