[naveed_786]

I am using this code to login a user to access database from vb.net
Problem is this i want to make two kind of user one user can only add and search records and other should be administrator how can i do this please guide me thanks in advance for yuor help.

Dim cnString As String = "Data Source=ITS;Initial Catalog=Payment;Integrated Security=True"

        'Create connection
        Dim conn As SqlConnection = New SqlConnection(cnString)
        Dim sql = "SELECT Username,Pass FROM Login WHERE Username = '" & UsernameTextBox.Text & "' AND Pass = '" & PasswordTextBox.Text & "'"
        conn.Open()
        Dim cmd As SqlCommand = New SqlCommand(sql, conn)
        Dim dr As SqlDataReader = cmd.ExecuteReader

        Try

        Catch ex As InvalidOperationException
            MsgBox(ex.Message)

        End Try
        Try
            If dr.Read = False Then
                MessageBox.Show("Please try again!", "ALI ENTERPRISES", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Else
                MessageBox.Show("Login Successfull......!", "ALI ENTERPRISES", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Me.Close()
                Form11.Show()
            End If
        Catch ex As Exception
            MsgBox(ex.Message)

        End Try

        If conn.State <> ConnectionState.Closed Then
            conn.Close()
        End If

[demausdauth]

Quick and easy you could add a column ( something like IsAdmin of a bit type ) to your Login table and as part of the login routine you could set an application wide Boolean variable. Then on each form you check the variable to see what gets shown, enabled, etc..


The really cool way that I found takes a little more understanding. It uses the ASP.Net roles and membership providers in a Windows application!!

[naveed_786]

Please can you provide me some example.

[camckee316]

I have used this code to login users:

dsusers usersbindingsource usertableadapter tableadaptermanager


Public Class frmlogin

    Private Sub frmlogin_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
  
        'TODO: This line of code loads data into the 'Dsuser.users' table. You can move, or remove it, as needed.
        UsersTableAdapter.Update(Me.Dsusers)
        Me.UsersTableAdapter.Fill(Me.Dsusers.users)

    End Sub

    Private Sub btnlogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnlogin.Click
        Dim i 'As String

        i = Me.UsersTableAdapter.userlogin(txtusername.Text, txtpassword.Text)
        If i = txtusername.Text Then
            frmoptions.Show()
            Me.Hide()
            txtusername.Clear()
            txtpassword.Clear()

        Else
            MessageBox.Show("Username and/or password is not valid, or you are not registered. Try again or register", "Login Error")
            txtusername.Clear()
            txtpassword.Clear()
            txtusername.Focus()

        End If
    End Sub

    Private Sub btnregister_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnregister.Click
        frmregister.Show()
        Me.Hide()

Then if I wanted the user whom is logged in to access a form I would use form.show else I would send a message saying that the user is not authorized.

[naveed_786]

I understand your logic but you are not understanding me i want to assign all the privilages like add,delete,search, edit to administrator and the other user are created should be granted privilages by administrator.

[camckee316]

Do you mean something like the way the fourms are setup? Certain people can do certain things will others cannot.

[naveed_786]

Yea ofcouse you are right

[demausdauth]

First thing I would suggest to do is to clean up the logging and make it more simple. Let's take your code for logging in and put it in its own function. The purpose of this function is to determine if the values (username and password) that are passed to it are valid or not. Nothing else will matter to it.

Let's put that in an application wide available place, a Module called GlobalStuff.

Module GlobalStuff
     Public Function IsLoggedIn(ByVal PassUserName As String, ByVal PassPassword As String) As Boolean
          Dim ReturnBool As Boolean = True
     
          Dim cnString As String = "Data Source=ITS;Initial Catalog=Payment;Integrated Security=True"
     
          Dim conn As SqlConnection = New SqlConnection(cnString)   
          Dim sql = "SELECT Username,Pass FROM Login WHERE Username = '" & PassUserName & "' AND Pass = '" & PassPassword & "'"
          Dim cmd As SqlCommand = New SqlCommand(sql, conn)
          Try
               conn.Open()
               Dim dr As SqlDataReader = cmd.ExecuteReader
          
               ReturnBool = dr.Read()          
          
          Catch Exp As SqlException
               ReturnBool = False
               'Put other code to handle, log, etc... errors here
          Catch Exp As Exception
               ReturnBool = False
               'Put other code to handle, log, etc... errors here
          Finally
               If conn IsNot Nothing AndAlso conn.State <> ConnectionState.Closed Then
                    conn.Close()
               End If          
          End Try

          Return ReturnBool

     End Function

End Module

Now we have a nice little compact function that only worries about determining whether the 2 values passed to it make a valid username / password combination. And here is how we would use this particular function, in say a login form button click event.

Public Sub btnLogin_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnLogin.Click
     
     'add checking to make sure the textboxes are filled out
     If GlobalStuff.IsLoggedIn(UsernameTextBox.Text,PasswordTextBox.Text) Then
          'logged in ok
          ' now do what ever you need to 
          Dim frm As Form1
          frm.Show()
     Else
          'an error ocurred
          ' here you can handle the error
          MessageBox.Show("An error occurred logging in check your username / password again.")
          UsernameTextBox.Focus()
     End If
End Sub

So how do this apply or help with the admin issue? Well now in our Module we create a boolean variable called IsAdminUser. Now we can access this variable from any form and determine if the currently logged in user is an admin user or not.

First thing is we need to add a column to the database Login table. We'll call it IsAdmin, give a datatype of bit, do not allow null values and make it automatically default to false.

Module GlobalStuff
     Public Function IsLoggedIn(ByVal PassUserName As String, ByVal PassPassword As String) As Boolean
          Dim ReturnBool As Boolean = True
     
          Dim cnString As String = "Data Source=ITS;Initial Catalog=Payment;Integrated Security=True"
     
          Dim conn As SqlConnection = New SqlConnection(cnString)   
          Dim sql = "SELECT Username,Pass,IsAdmin FROM Login WHERE Username = '" & PassUserName & "' AND Pass = '" & PassPassword & "'"
          Dim cmd As SqlCommand = New SqlCommand(sql, conn)
          Try
               conn.Open()
               Dim dr As SqlDataReader = cmd.ExecuteReader
          
               'ReturnBool = dr.Read()
               ' since the select statement is going to return a value that 
               ' we want to read we will go ahead and read it in
               If dr.Read() Then
                    GlobalStuff.IsAdminUser = Convert.ToBoolean(dr("IsAdmin").ToString())
               Else
                    GlobalStuff.IsAdminUser = False
                    ReturnBool = False
               End If   
          Catch Exp As SqlException
               ReturnBool = False
               'Put other code to handle, log, etc... errors here
          Catch Exp As Exception
               ReturnBool = False
               'Put other code to handle, log, etc... errors here
          Finally
               If conn IsNot Nothing AndAlso conn.State <> ConnectionState.Closed Then
                    conn.Close()
               End If          
          End Try

          Return ReturnBool

     End Function

End Module

Now our global IsAdminUser is set True/False and our function still works with the button click event that we had created earlier. We didn't have to change anything!!

How does this work for our other forms in the application? Well now when we load a form we can set the Enabled property of either all the controls or only those that make a difference whether the user is an admin or not.

Public Class Form1

     Public Sub Load_Event(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
          btnDeleteEverything.Enable = GlobalStuff.IsAdminUser
     End Sub
End Class

Here are some links that you might want to check out to help improve your code:

Commands and Parameters

Using statement

[naveed_786]

Here my code now it gives me error "IsAdmin user is not member of PMS"
what should i do now to resolve this problem.

Dim ReturnBool As Boolean = True
        Dim cnString As String = "Data Source=ITS;Initial Catalog=Payment;Integrated Security=True"
        Dim conn As SqlConnection = New SqlConnection(cnString)
        Dim sql = "SELECT Username,Pass FROM Login WHERE Username = '" & PassUserName & "' AND Pass = '" & PassPassword & "'"
        Dim cmd As SqlCommand = New SqlCommand(sql, conn)
        Try

            conn.Open()

            Dim dr As SqlDataReader = cmd.ExecuteReader
            ReturnBool = dr.Read()
            If dr.Read() Then
                GlobalStuff.IsAdminUser = Convert.ToBoolean(dr("IsAdmin").ToString())
            Else
                GlobalStuff.IsAdminUser = False
                ReturnBool = False
            End If
        Catch Exp As SqlException
            ReturnBool = False
        Finally
            If conn IsNot Nothing AndAlso conn.State <> ConnectionState.Closed Then
                conn.Close()
            End If
        End Try
        Return ReturnBool
    End Function

[naveed_786]

No body has any idea about it.

[AdamSpeight2008]

You have class named PMS and IsAdminUser isn't a member of it. Simple.

[naveed_786]

Then what should i do to resolve this problem.

[demausdauth]

demausdauth, on 27 August 2010 - 01:28 PM, said:

First thing is we need to add a column to the database Login table. We'll call it IsAdmin, give a datatype of bit, do not allow null values and make it automatically default to false.

Module GlobalStuff
     Public Function IsLoggedIn(ByVal PassUserName As String, ByVal PassPassword As String) As Boolean
          Dim ReturnBool As Boolean = True
     
          Dim cnString As String = "Data Source=ITS;Initial Catalog=Payment;Integrated Security=True"
     
          Dim conn As SqlConnection = New SqlConnection(cnString)   
          Dim sql = "SELECT Username,Pass,IsAdmin FROM Login WHERE Username = '" & PassUserName & "' AND Pass = '" & PassPassword & "'"
          Dim cmd As SqlCommand = New SqlCommand(sql, conn)
          Try
               conn.Open()
               Dim dr As SqlDataReader = cmd.ExecuteReader
          
               'ReturnBool = dr.Read()
               ' since the select statement is going to return a value that 
               ' we want to read we will go ahead and read it in
               If dr.Read() Then
                    GlobalStuff.IsAdminUser = Convert.ToBoolean(dr("IsAdmin").ToString())
               Else
                    GlobalStuff.IsAdminUser = False
                    ReturnBool = False
               End If   
          Catch Exp As SqlException
               ReturnBool = False
               'Put other code to handle, log, etc... errors here
          Catch Exp As Exception
               ReturnBool = False
               'Put other code to handle, log, etc... errors here
          Finally
               If conn IsNot Nothing AndAlso conn.State <> ConnectionState.Closed Then
                    conn.Close()
               End If          
          End Try

          Return ReturnBool

     End Function

End Module

The problem you are running into now is that you haven't modified the database table Login. The SELECT statement is expecting to find a column IsAdmin in the database but either you haven't added it physically to the database or you have not modified the select statement to get the value. Attention Line #8 in the code above.

[naveed_786]

I have modified the Select statement but no luck actually the problem is in "GlobalStuff.IsadminUser" when i debug the code it says "IsAdminUser" is not member of PMS.

If dr.Read() Then
                GlobalStuff.IsAdminUser = Convert.ToBoolean(dr("IsAdmin").ToString())
            Else
                GlobalStuff.IsAdminUser = False
                ReturnBool = False
            End If

[demausdauth]

Looking back over my posts and the code that I posted... I now see that I didn't specifically write the code that puts a boolean variable in the GlobalStuff module. However, I did mention the adding of this variable while explaining the reason for it and the way that it would be used in the forms.

demausdauth, on 27 August 2010 - 01:28 PM, said:

So how do this apply or help with the admin issue? Well now in our Module we create a boolean variable called IsAdminUser. Now we can access this variable from any form and determine if the currently logged in user is an admin user or not.

So...

Module GlobalStuff
     Public IsAdminUser As Boolean = False 'default to non-admin user


... other code that we have already added

End Module