13 Replies - 1046 Views - Last Post: 02 December 2013 - 11:15 AM Rate Topic: -----

#1 jose.rodriguez.an   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 58
  • Joined: 18-April 13

username and access database problem

Posted 28 November 2013 - 12:05 PM

Hello all,

I have a problem with this code, I created an application with severals programs inside. Some of those programs are banned to some people, those people are clasified into an MS access database.

What I want to do is that in case the username "Environment.UserName" is into the database.table hide some buttons and tabpages.

I dont have any error show in this code, just does not work, I mean the button still appear.

I have created the code in a button so you see better in case there is any error.

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
      Dim DBConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\sfs.corp\Projects\ICT\SERVICEDESK_SHARE\SD_Only\Tools\JLTool\DataBase\LOGIN.accdb"
      Dim Conex As New OleDbConnection(DBConnStr)
      Dim cmndTL As OleDbCommand = New OleDbCommand("select count(*) from TL_LoginTB WHERE LOGINTL = @Username", Conex)
      Dim RecordCount As Integer
      
      cmndTL.Parameters.AddWithValue("@Username", Environment.UserName)
      
      Try
        'Try to open the Database and Execute the Command
        Conex.Open()
        RecordCount = CInt(cmndTL.ExecuteScalar)
        If RecordCount > 0 Then
          'There is a valid User so do something
        End If
      Catch ex As Exception
        'If something fails then display what the Error Message is
        MsgBox(ex.Message)
      Finally
        'Regardless of Success or Failure always Close the database if it Opened Successfully
        If Conex.State = ConnectionState.Open Then
          Conex.Close()
        End If
      End Try
    End Sub



Thank you,
Jose Luis,

Is This A Good Question/Topic? 0
  • +

Replies To: username and access database problem

#2 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2588
  • View blog
  • Posts: 10,369
  • Joined: 03-December 12

Re: username and access database problem

Posted 28 November 2013 - 12:21 PM

Going to need more code and possibly you db structure. All you show there is if the user exists in the database and they don't need a password to gain entry.
Was This Post Helpful? 0
  • +
  • -

#3 jose.rodriguez.an   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 58
  • Joined: 18-April 13

Re: username and access database problem

Posted 28 November 2013 - 01:17 PM

Hello astonecipher,

This is the main code that should do what I want.

The database have three entities without relationship.

This entity is:

TL_LOGINTB(logintl,nametl,surnametl,emailtl)

In the vb code I need to check if the enviorament.username that represent the username of the person who is login in the computer is into the database.

It the user it is log, must to make unvisible a button that have access to a database button.hide() or button.visible = false. But does not work.

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
02	  Dim DBConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\sfs.corp\Projects\ICT\SERVICEDESK_SHARE\SD_Only\Tools\JLTool\DataBase\LOGIN.accdb"
03	  Dim Conex As New OleDbConnection(DBConnStr)
04	  Dim cmndTL As OleDbCommand = New OleDbCommand("select count(*) from TL_LoginTB WHERE LOGINTL = @Username", Conex)
05	  Dim RecordCount As Integer
06	   
07	  cmndTL.Parameters.AddWithValue("@Username", Environment.UserName)
08	   
09	  Try
10	    'Try to open the Database and Execute the Command
11	    Conex.Open()
12	    RecordCount = CInt(cmndTL.ExecuteScalar)
13	    If RecordCount > 0 Then
14	      button.hide()
15	    End If
16	  Catch ex As Exception
17	    'If something fails then display what the Error Message is
18	    MsgBox(ex.Message)
19	  Finally
20	    'Regardless of Success or Failure always Close the database if it Opened Successfully
21	    If Conex.State = ConnectionState.Open Then
22	      Conex.Close()
23	    End If
24	  End Try
25	End Sub

Was This Post Helpful? 0
  • +
  • -

#4 andrewsw   User is offline

  • So Smart! S-M-R-T!
  • member icon

Reputation: 6578
  • View blog
  • Posts: 26,722
  • Joined: 12-December 12

Re: username and access database problem

Posted 28 November 2013 - 01:53 PM

Do any of the errors show? Which ones.

And is your button actually named 'button'?! Change its name as Button is the name of a standard form-control (and VB isn't case-sensitive).

Also use a MessageBox to confirm the username:
MessageBox.Show(Environment.UserName)

and compare this to what's in the table. (You've probably already done this ;))
Was This Post Helpful? 0
  • +
  • -

#5 jose.rodriguez.an   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 58
  • Joined: 18-April 13

Re: username and access database problem

Posted 28 November 2013 - 02:29 PM

Hi andrewsw,

You are right. I did it. There is no error. Just the code below do not work.
 
If RecordCount > 0 Then
       button.hide()
End If



I need to find the way to identify the username into a database, with that I will avoid to create three applications for each group.

The code is not into a button objetc, the following code is into public class

 Dim DBConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\sfs.corp\Projects\ICT\SERVICEDESK_SHARE\SD_Only\Tools\JLTool\DataBase\LOGIN.accdb"
   Dim Conex As New OleDbConnection(DBConnStr)
   Dim cmndTL As OleDbCommand = New OleDbCommand("select count(*) from TL_LoginTB WHERE LOGINTL = @Username", Conex)
   Dim RecordCount As Integer



And the rest of the code is into form.load function.

cmndTL.Parameters.AddWithValue("@Username", Environment.UserName)
    
    Try
      'Try to open the Database and Execute the Command
     Conex.Open()
      RecordCount = CInt(cmndTL.ExecuteScalar)
      If RecordCount > 0 Then
       button.hide()
     End If
    Catch ex As Exception
      'If something fails then display what the Error Message is
      MsgBox(ex.Message)
    Finally
      'Regardless of Success or Failure always Close the database if it Opened Successfully
      If Conex.State = ConnectionState.Open Then
        Conex.Close()
     End If
    End Try
 End Sub




Was This Post Helpful? 0
  • +
  • -

#6 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2588
  • View blog
  • Posts: 10,369
  • Joined: 03-December 12

Re: username and access database problem

Posted 28 November 2013 - 02:41 PM

If you are trying to setup user roles you need to 1 have a category defined as such. and 2 get more information from the database than just if the name is in the database.
Was This Post Helpful? 0
  • +
  • -

#7 jose.rodriguez.an   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 58
  • Joined: 18-April 13

Re: username and access database problem

Posted 28 November 2013 - 02:47 PM

Hi astonecipher,

It would be possible for you to put me an example in a code so I can understand better what you are saying.

I dont want to setup user roles just read a column in a access data base that has all the login, and check with a conditional that if it is into the column, hide an object.
Was This Post Helpful? 0
  • +
  • -

#8 astonecipher   User is offline

  • Senior Systems Engineer
  • member icon

Reputation: 2588
  • View blog
  • Posts: 10,369
  • Joined: 03-December 12

Re: username and access database problem

Posted 28 November 2013 - 03:29 PM

It sounds like you need user roles. You speak of three different applications, which means you have three different types of users. You are testing for a count if Environment.UserName is in the data table. That translates to either they are of group a or group b. User roles means one application with different access areas (hiding buttons).

The other side of this is testing it. Conceivably you are in the database because you are creating the system. There for you will always have access to the system because you are testing you name verse the database, make sense?
Was This Post Helpful? 0
  • +
  • -

#9 jose.rodriguez.an   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 58
  • Joined: 18-April 13

Re: username and access database problem

Posted 29 November 2013 - 01:13 AM

Hi astonecipher,

You are right, thats what I have to do. The database have three entities. In one of them it is my username and the username of other people to test in their computers with their logins.

As you mentioned there are three groups of people,

Group A: is going to manage the database.
Group B:
Do not have access to manage,so the button wil be in hide attribute.
This group will use different signature and differents templates from one tabpage.
Group C:
Do not have access to manage,so the button wil be in hide attribute.
This group will use different signature.
And a tabpage will be remove.

Private Sub test_Click(sender As System.Object, e As System.EventArgs) Handles test.Click
        'Database connection and commands.
        Dim DBConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\sfs.corp\Projects\ICT\SERVICEDESK_SHARE\SD_Only\Tools\JLTool\DataBase\LOGIN.accdb"
        Dim Conex As New OleDbConnection(DBConnStr)
        Dim cmndTL As OleDbCommand = New OleDbCommand("select count(*) from TL_LoginTB WHERE LOGINTL = @Username", Conex)
        Dim RecordCount As Integer
 
        cmndTL.Parameters.AddWithValue("@Username", Environment.UserName)
        Try
            Conex.Open()
            RecordCount = CInt(cmndTL.ExecuteScalar)
         
            If RecordCount < 0 Then
                DBaccbt.Visible = False
            End If
 
        Catch ex As ExternalException
            MsgBox(ex.Message)
            MsgBox("Data Base cannot be found." + vbNewLine + "Please contact to a L2", MsgBoxStyle.OkOnly)
        Finally
            'Regardless of success or failure always close the conection.
            If Conex.State = ConnectionState.Open Then
                Conex.Close()
            End If
        End Try
 
        Conex.Close()
    End Sub



I have also use this other one with the same results:

Private Sub test_Click(sender As System.Object, e As System.EventArgs) Handles test.Click
        'Database connection and commands.
        Dim DBConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\sfs.corp\Projects\ICT\SERVICEDESK_SHARE\SD_Only\Tools\JLTool\DataBase\LOGIN.accdb"
        Dim Conex As New OleDbConnection(DBConnStr)
        Dim cmndTL As OleDbCommand = New OleDbCommand("select * from TL_LoginTB WHERE LOGINTL = @Username", Conex)
         
        cmndTL.Parameters.AddWithValue("@Username", Environment.UserName)
        Try
            Conex.Open()
                        If Environment.UserName IsNot cmdnTL Then
                     DBaccbt.Visible = False
           End if 
 
        Catch ex As ExternalException
            MsgBox(ex.Message)
            MsgBox("Data Base cannot be found." + vbNewLine + "Please contact to a L2", MsgBoxStyle.OkOnly)
        Finally
            'Regardless of success or failure always close the conection.
            If Conex.State = ConnectionState.Open Then
                Conex.Close()
            End If
        End Try
 
        Conex.Close()
    End Sub


Was This Post Helpful? 0
  • +
  • -

#10 ebolisa   User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 160
  • Joined: 22-September 09

Re: username and access database problem

Posted 29 November 2013 - 10:32 AM

If I understood you right, you want to disable/hide button2 if a name exists in your DB.
If so, try this (I used Northwind to test it):
    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click

        Dim testName As String = "Davolio" 'or Environment.UserName

        'Read Davolio's data
        Dim cmndTL As SqlCommand = New SqlCommand("select count(*) from Employees WHERE lastname ='" & testName & "'", DBConnStr)
        Dim RecordCount As Integer

        Try
            DBConnStr.Open()
            RecordCount = CInt(cmndTL.ExecuteScalar)

            If RecordCount > 0 Then
                'If Davolio exists, desable btn2
                Me.DBaccbt.Enabled = False
            End If

        Catch ex As ExternalException
            MsgBox(ex.Message)
            MsgBox("Data Base cannot be found." + vbNewLine + "Please contact to a L2", MsgBoxStyle.OkOnly)
        Finally
            'Regardless of success or failure always close the conection.
            If DBConnStr.State = ConnectionState.Open Then
                DBConnStr.Close()
            End If
        End Try

        DBConnStr.Close()
    End Sub



Was This Post Helpful? 0
  • +
  • -

#11 jose.rodriguez.an   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 58
  • Joined: 18-April 13

Re: username and access database problem

Posted 29 November 2013 - 11:45 AM

Hello ebolisa,

I will try this change on Monday.

But it is similar than on the one that I created, which it is working fine as a button, but not at the load of the main form and neither as a module.

I will your code and I will comment here.

Thank you.
Was This Post Helpful? 0
  • +
  • -

#12 ebolisa   User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 160
  • Joined: 22-September 09

Re: username and access database problem

Posted 29 November 2013 - 03:19 PM

Hum, it works for me...

   Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Dim testName1 As String = "Davolio" 'or Environment.UserName
        Dim testName2 As String = "Davolio1"
        Dim sql1, sql2 As String
        sql1 = "select count(*) from Employees WHERE lastname ='" & testName1 & "'"
        sql2 = "select count(*) from Employees WHERE lastname ='" & testName2 & "'"

        'Read table data
        Dim cmndTL As SqlCommand
        Dim RecordCount As Integer

        Try
            DBConnStr.Open()

            cmndTL = New SqlCommand(sql1, DBConnStr)
            RecordCount = CInt(cmndTL.ExecuteScalar)

            If RecordCount > 0 Then
                'If Davolio exists, enable btn (DBaccbt)
                Me.DBaccbt.Enabled = True
            End If

            cmndTL.Dispose()

            'let's check for the second name
            cmndTL = New SqlCommand(sql2, DBConnStr)
            RecordCount = CInt(cmndTL.ExecuteScalar)

            If Not RecordCount > 0 Then 'Ħojo al Not!
                'If Davolio1 doen't exists
                Me.DBaccbt.Enabled = False
            End If

            cmndTL.Dispose()
            DBConnStr.Close()

        Catch ex As ExternalException
            MsgBox(ex.Message)
            MsgBox("Data Base cannot be found." + vbNewLine + "Please contact to a L2", MsgBoxStyle.OkOnly)
        Finally
            'Regardless of success or failure always close the conection.
            If DBConnStr.State = ConnectionState.Open Then
                DBConnStr.Close()
            End If
        End Try

    End Sub


Was This Post Helpful? 0
  • +
  • -

#13 jose.rodriguez.an   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 58
  • Joined: 18-April 13

Re: username and access database problem

Posted 29 November 2013 - 03:25 PM

Hi ebolisa,

Thank you very much. Let me check that on monday and I will tell you something. If it works for you shold to work for me.

Thanks for your effort.
Jose Luis,
Was This Post Helpful? 0
  • +
  • -

#14 jose.rodriguez.an   User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 58
  • Joined: 18-April 13

Re: username and access database problem

Posted 02 December 2013 - 11:15 AM

Hi ebolisa,

It is working. Thank you very much I was with this problem time ago and did not find any method to make it work.

Thank you again.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1