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,

You are setting the Username Parameter AFTER you have executed the query against the Database. You need to set that parameter first.

Hope that helps.

Cheers,

Ian

 

[lutonian]

Hi IanRyder,

I dont understand quite well.
I suppose you meant to move

this two variables after conex.open()

RecordCount = CInt(cmndTL.ExecuteScalar())
cmndTL.Parameters.AddWithValue("@Username", Environment.UserName)

I would appreciate a lot if you can put me an example as a code.

Thank you very much.

 

[IanRyder]

Hi,

Actually, I missed that little bit, but yes. The actual complete logic to follow is:-

1) Create the Connection Object
2) Create the Command Object
3) SET the Parameters of the Command Object
4) Open the Connection
5) Execute the Command against the Database
6) Close the Connection

Cheers,

Ian

 

[lutonian]

Helo Ian.

I think i am doing what you mentioned.

1) Create the Connection Object & 2) Create the Command Object

 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
[\CODE]

3) SET the Parameters of the Command Object

[CODE]
RecordCount = CInt(cmndTL.ExecuteScalar())
cmndTL.Parameters.AddWithValue("@Username", Environment.UserName)
[\CODE]

4) Open the Connection

[CODE]
Try
Conex.Open()
Catch
MsgBox("Data Base cannot be found." + vbNewLine + "Please contact to a L2", MsgBoxStyle.OkOnly)
End Try
[\CODE]

5) Execute the Command against the Database

[CODE]
If RecordCount = 1 Then
DBaccbt.Hide()
End If

'ANOTHER METHOD USED
'If Environment.UserName IsNot cmndTL Then
' DBaccbt.Hide()
' End If
[\CODE]

6) Close the Connection

conex.closed()


Am i doing something wrong?

Thank you ian,

 

[IanRyder]

I think i am doing what you mentioned.

Hmmm, Not quite! You do not seem to be understanding what your own statements are doing.

1) Create the Connection Object & 2) Create the Command Object

 Dim DBConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\sfs.corp\Projects\ICT\SERVICEDESK_SHARE\S D_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
[\CODE][/QUOTE]
 
These declarations are fine so delete that commented out line calling ExecuateScalar to erase any confusion that it may cause you.
 
[QUOTE]3) SET the Parameters of the Command Object

[CODE]
RecordCount = CInt(cmndTL.ExecuteScalar())
cmndTL.Parameters.AddWithValue("@Username", Environment.UserName)
[\CODE][/QUOTE]
 
Where does "Set the parameters of the command object" say to call ExecuteScalar of the command object?
 
[QUOTE]4) Open the Connection

[CODE]
Try
Conex.Open()
Catch
MsgBox("Data Base cannot be found." + vbNewLine + "Please contact to a L2", MsgBoxStyle.OkOnly)
End Try
[\CODE][/QUOTE]
 
Fine although you should be catching Exception Types here.
 
[QUOTE]5) Execute the Command against the Database

[CODE]
If RecordCount = 1 Then
DBaccbt.Hide()
End If[/QUOTE]
 
Errrr, so where is the Execution of the Command object?
 
[QUOTE]6) Close the Connection

conex.closed()[/QUOTE]
 
Fine.
 
I hope that you can now see where you are going wrong so that you can get this fixed.
 
Hope that helps.
 
Cheers,
 
Ian

BTW, you need to use the other Backslash to encase code in Code Tags.

 

[lutonian]

Hi ian,

I cant understand quite well, I dont know a lot of programming. I supposed you mean this:

All this code is into Public Class Form1

'Connexion to database
Dim DBConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data  Source=\\sfs.corp\Projects\ICT\SERVICEDESK_SHARE\S  D_Only\Tools\JLTool\DataBase\LOGIN.accdb"
Public Conex As New OleDbConnection(DBConnStr)
'select command to database
Dim cmndTL As OleDbCommand = New OleDbCommand("select count(*) from TL_LoginTB WHERE LOGINTL = @Username", Conex)
'This is an integer to convert database data to integer
Dim RecordCount As Integer

These declarations are fine so delete that commented out line calling ExecuateScalar to erase any confusion that it may cause you.

Do you mean remove RecordCount = CInt(cmndTL.ExecuteScalar()) in this part of the code? 3) SET the Parameters of the Command Object

cmndTL.Parameters.AddWithValue("@Username", Environment.UserName)

Where does "Set the parameters of the command object" say to call ExecuteScalar of the command object?

Do you mean put the RecordCount = CInt(cmndTL.ExecuteScalar()) in here? 4) Open the Connection

Try
Conex.Open()
RecordCount = CInt(cmndTL.ExecuteScalar())
Catch
MsgBox("Data Base cannot be found." + vbNewLine + "Please contact to a L2", MsgBoxStyle.OkOnly)
End Try

Fine although you should be catching Exception Types here.

5) Execute the Command against the Database

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

Errrr, so where is the Execution of the Command object?

The execution command is RecordCount which has been converted into int from cmndTL.ExecuteScalar(), the cmndTL is the SQL command.

6) Close the Connection

conex.closed()

 

[IanRyder]

Hi,

Yeah, ignoring some minor specifics, that is more or less correct and at least it is in the right order of doing things. So, the question is, did it work?

Cheers,

Ian

 

[lutonian]

Hi Ian,

It does not work. I use this code, I see the msgbox of the catch.

Try
Conex.Open()
RecordCount = CInt(cmndTL.ExecuteScalar())
Catch
MsgBox("Data Base cannot be found." + vbNewLine + "Please contact to a L2", MsgBoxStyle.OkOnly)
End Try

I have put this RecordCount = CInt(cmndTL.ExecuteScalar()) in other places and nothing happens.

 

[IanRyder]

Hi,

I have put this RecordCount = CInt(cmndTL.ExecuteScalar()) in other places and nothing happens.

Well don't, the worst thing you can do in programming is just drop snippets of code around a project hoping that it will magically work.

Two things for you now:-

1) The code you have posted is in the correct order so that is fine but I cannot confirm if you have got the rest of this code block right now since you did not post it? So, assuming that you got the other code right:-

2) You need to declare that Try / Catch Block correctly to be able to trap the error messages that you are getting and then act on them accordingly. So you need to change what you have there to capture an Object of Type Exception and then display that message if an error occurs.

So, knowing the above and hoping you have got it all right now, you should have something like:-

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

I have given you a few extra tips there but give that a try and hopefully you should be good to go.

Hope that helps.

Cheers,

Ian

 

[lutonian]

Hi ian,

We are really close. But I still have the same error. It show the ex catch message when I enable the line recordcount = cint(cmndTL.ExecuteScalar)
If if disable it works but the conditional do not work.

 

[IanRyder]

OK, So what's the Error?

Only you can actually see what is going on in your project. We can't. We only know what you tell us so if you would like to tell us what the error is we may be able to help you further.

Cheers,

Ian

 

[lutonian]

Hi Ian,

I dont have any error I can access to the database, but supposely a button should dissapear with this code:

IF[/FONT] RecordCount > 0 Then
       button.hide()
End If

But do not work, the code you put me is same as you give me, in fact I copied just in case mine was not right.

 

[IanRyder]

Hi,

Have you actually tried to Hide your Button on your Form since this looks like you have typed directly to the Forum rather than copying and pasting a section of code that you have already tried in your project.

In all essence that should work as long as you have correctly qualified the name of the Button on your Form and you have structured the If statement correctly. i.e:-

If RecordCount > 0 Then
  YourButtonName.Hide()
End If

Cheers,

Ian

 

[lutonian]

Hi ian,

it works fine, but the contional I cannot.

What I need to do is to hide a button and tab in case username is not on a list and also will change the variable of label.text.

Do you know another method to do it? The other method I thought it is to create three applications one for each group, cause I dont think it is the best idea.