Check username variable in database

[lutonian]

Hello all,

I have connect a project with an access database. That database has a table with logins.

What I need it is that if the username is into the database.column then hide a button. I have use two methods but never works.

If RecordCount = 1 Then
            DBaccbt.Hide()
           End If


If Environment.UserName IsNot cmndTL Then
        DBaccbt.Hide()
       End If

I have make this code in vb.net, using both methods and do not work any of them.

'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"
Public Conex As New OleDbConnection(DBConnStr)
Dim cmndTL As OleDbCommand = New OleDbCommand("select count(*) from TL_LoginTB WHERE LOGINTL = @Username", Conex)
'Dim UserFoundCountTL As Integer = CInt(cmndTL.ExecuteScalar())
Dim RecordCount As Integer


Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
RecordCount = CInt(cmndTL.ExecuteScalar())
cmndTL.Parameters.AddWithValue("@Username", Environment.UserName)
Try
Conex.Open()
Catch
MsgBox("Data Base cannot be found." + vbNewLine + "Please contact to a L2", MsgBoxStyle.OkOnly)
End Try
'TESTING... IF THE USERNAME IS ON DATABASE. IF NOT HIDE DB BUTTON AND TABCONTROL
'Create another application for TL in case L1 
If RecordCount = 1 Then
DBaccbt.Hide()
'ElseIf UserFoundCountL1 = 1 Then
' DBaccbt.Hide()
' Me.TabControl.TabPages.Remove(QualityTab)
End If
'ANOTHER METHOD USED
'If Environment.UserName IsNot cmndTL Then
' DBaccbt.Hide()
' End If
Conex.Close()

Any one can tell me what it is happing or how to do it?

Thank you.
Jose Luis.

 

[IanRyder]

Hi,

Because of the limited vocabulary in your posts I am a bit lost now since you seem to be moving the "goal posts" of your requirements and I am not quite sure what is and what is not working.

The best thing to do now is to decide if the Database query and the hiding of the Button is now solved. If not, then continue this thread to solve those two SPECIFIC issues, but if they are solved, then mark this thread as completed and then create a New Thread to ask your additional questions.

Whichever way you decide is the right way to go then please take Loads of time to Explain and Type, in DETAIL, exactly what it is you are trying to achieve, what you have tried, what you are expecting and what is actually happening. The more that you do to help us means the more that we can do to help you.

I am hoping for some better narrative from you next time round.

Cheers,

Ian

 

[lutonian]

Hi Ian,

I did exactly what you told me put it into a button and did not work.

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

I still have the same problem.

 

[IanRyder]

Hi,

Just so that you know, I am getting tired of trying to figure out what "something..... and did not work" means every time you post.

Firstly, get rid of that second block of code. What are you trying to achieve by NOT Executing the Command against the Database and just testing a value of type String with on object of type Command? That means nothing!

Secondly, you say:-

If RecordCount < 0 Then
  DBaccbt.Visible = False
End If

So what I think you are thing to do is Hide the Button if the Current User is NOT in the Database. If so, when you execute that command against the Database and the User is NOT Present in the Database then Count(*) will return Zero which means that RowCount will be Zero so you should be saying:-

If RecordCount = 0 Then
  DBaccbt.Visible = False
End If

Cheers,

Ian

 

[lutonian]

Hi Ian,

The code works fine in a button, but not in a module and into the form.load.

I will continue working on this on monday. Do you mind if I still mantain open this thread, just to put my comment in case I found the error. If you dont want I can close in case you tell me to close it.

Thank you for your effort.

 

[lutonian]

Hello Ian,

I have found a method to make it work.

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Dim testName1 As String = Environment.UserName
        Dim sql1 As String
        sql1 = "select count(*) from Employees WHERE lastname ='" & testName1 & "'"
        'Read table data
        Dim cmndTL As SqlCommand
        Dim RecordCount As Integer
        Try
            DBConnStr.Open()
            cmndTL = New SqlCommand(sql1, DBConnStr)
            RecordCount = CInt(cmndTL.ExecuteScalar)
            If Not RecordCount < 0 Then
                Me.DBaccbt.Enabled = False
            End If
            cmndTL.Dispose()
            'let's check for the second name
            cmndTL = New SqlCommand(sql2, DBConnStr)
            RecordCount = CInt(cmndTL.ExecuteScalar)
            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

Thank you for your help.