[RESOLVED] [2005] How to make the prog compare the values from database with the values input

[melvados]

Hi, sorry if I have post this in the wrong section. Anyway I am trying to create a login system where user type in its username and password, the program will then search its databases for the specified username & password inputted. If it succeeds, user will be granted access by a msgbox if not it will be granted access denied.

My database is in access file format. So now I had like to know how to I make the program to validate the username and password from the access file? I am not sure about the coding, can someone direct me...

Thanks!

[Deepak Sakpal]

Well... It seems that you are a fresher in programming. You need to lean vb.net. Start it here http://www.startvbdotnet.com.

[melvados]

I kind of take a look around the web site you have give. Doesn't seem to have any relevent to the one I am asking... Does it? And here I thought it may need some complicated codings...

[MaximilianMayrhofer]

How secure are we talking here? An Access database does not support true multi-user access. You can create a table that contains 'usernames' and 'passwords', but this doesn't make it secure against anyone with access to the database file. You can also put a password on the database file itself, but you'll have to hardcode it into your connection string, which means that anyone with a decompiler will be able to grab it without a problem.

For connecting to an Access database file, take a look at www.connectionstrings.com

Then you will have to do a little bit of research into ADO or perhaps OleDb for connection purposes, and SQL for querying the database.

[CoachBarker]

I got this from a search on logging in here on the forums and use it in a couple of my apps. JMC is the poster for the code:

global variabble

Dim conn As New OleDbConnection()

Public Sub login()
        Dim userName As String = Me.txtUsername.Text.Trim
        Dim userPassword As String = Me.mtbPassword.Text.Trim
 
        Dim connection As New OleDbConnection(conn.ConnectionString)
        Dim command As New OleDbCommand("SELECT COUNT(*) FROM tblUsers WHERE UserName = @UserName AND UserPassword = @UserPassword", _
                                      connection)
        With command.Parameters
            .AddWithValue("@UserID", userName)
            .AddWithValue("@Password", userPassword)
        End With
        connection.Open()
 
        Dim tempBoolean As Boolean = False
 
        If CInt(command.ExecuteScalar()) = 0 Then
            MsgBox("You have not entered the correct password.")
        Else
            tempBoolean = True
        End If
 
        connection.Close()
 
        If tempBoolean Then
            ' do something here
            Else
                ' do something else here
                End If
            End If
        End If
    End Sub

[alex_read]

You will want to read up on how to read and write to databases from this thread: http://www.vbforums.com/showthread.php?t=469872

He's used samples which relate to SQL server there, but if you replace each instance of the characters SQL with OLEDB, you will be able to use these samples against Microsoft Access databases fine.

[melvados]

Hi actually I am not doing a multi-user access. It is just that from the list of login names and password, it must be able to validate its credentials from the access file; afterall, whichever login names/pass used, all will be directed in using the same forms avaliable in the application.

So can only be considered as 'single user thread'

Erm by the way, the code that CoachBarker give, and i have edited and paste it in my form, OleDbConnection and OleDbCommand is considered not defined. Why?

Can someone guide me?

[MaximilianMayrhofer]

Add this to the very top of your code:

Imports System.Data
Imports System.Data.OleDb

[melvados]

Ok, er so how do I know if the above code is validating with the User queries I have made in my access file? Do I have to specify out the location? Cause it seems like no matter what user/pass I have typed, it can still grants me access...

This is what I have do for my login form, please do correct me if i'm wrong...

Imports System.Data
Imports System.Data.OleDb

Public Class Login
    Dim conn As New OleDbConnection()

    Public Sub login()
        Dim userName As String = Me.txtLoginName.Text.Trim
        Dim userPassword As String = Me.txtLoginPassword.Text.Trim
        Dim connection As New OleDbConnection(conn.ConnectionString)
        Dim command As New OleDbCommand("SELECT COUNT(*) FROM Users WHERE Login = @Login AND Password = @Password", _
                                             connection)
        With command.Parameters
            .AddWithValue("@UserID", userName)
            .AddWithValue("@Password", userPassword)
        End With
        connection.Open()
        Dim tempBoolean As Boolean = False
        If CInt(command.ExecuteScalar()) = 0 Then
            MsgBox("You have not entered the correct password.")
        Else
            tempBoolean = True
        End If
        connection.Close()

        If tempBoolean Then
            ' do something here
        Else
            ' do something else here
        End If

    End Sub

    Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click
        Me.Hide()
        Admin.Show()
    End Sub

    Private Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Application.Exit()
    End Sub
End Class

If there is anything needed, please do tell me and I will provide the info. thanks again

[Deepak Sakpal]

It's because you are not calling login() sub anywhere. It needs to be called on click event of OK button. I have done it for you.

Imports System.Data
Imports System.Data.OleDb
 
Public Class Login
    Dim conn As New OleDbConnection()
 
    Public Sub login()
        Dim userName As String = Me.txtLoginName.Text.Trim
        Dim userPassword As String = Me.txtLoginPassword.Text.Trim
        Dim connection As New OleDbConnection(conn.ConnectionString)
        Dim command As New OleDbCommand("SELECT COUNT(*) FROM Users WHERE Login = @Login AND Password = @Password", _
                                             connection)
        With command.Parameters
            .AddWithValue("@UserID", userName)
            .AddWithValue("@Password", userPassword)
        End With
        connection.Open()
        Dim tempBoolean As Boolean = False
        If CInt(command.ExecuteScalar()) = 0 Then
            MsgBox("You have not entered the correct password.")
        Else
            tempBoolean = True
        End If
        connection.Close()
 
        If tempBoolean Then
            ' do something here
            Me.Hide()
            Admin.Show()
        Else
            ' do something else here
        End If
 
    End Sub
 
    Private Sub btnOK_Click( _
        ByVal sender As System.Object, _
        ByVal e As System.EventArgs _
    ) Handles btnOK.Click
        
        login()
    End Sub
 
    Private Sub btnCancel_Click( _
        ByVal sender As System.Object, _
        ByVal e As System.EventArgs _
    ) Handles btnCancel.Click
        
        Application.Exit()
    End Sub
End Class

[MaximilianMayrhofer]

This will crash out because in your SQL statement you specify

WHERE Login = @Login

But you add this parameter to your command:

.AddWithValue("@UserID", userName)

Either change your SQL statement or change your parameter to @Login

[melvados]

I got an error saying 'The ConnectionString property has not been initialized.' in my login code when I am debuggin it. Does it mean that I have to put out the location of my user's query in access, if so how do i do it?

In any case I have attached my code below, please do help me take a look. Got a feeling that i may have written iincorrectly, though it seems to be tally with the values in my queries

Really am sorry for the inconvenience caused. sorry...

[Deepak Sakpal]

That is because you have written

Dim conn As New OleDbConnection()
Dim connection As New OleDbConnection(conn.ConnectionString)

conn.ConnectionString is not initialized anywhere. You need to specify the connection string instead of conn.ConnectionString.

It should be like:

Dim ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=admin;Password=;"
Dim connection As New OleDbConnection(ConnString)

[melvados]

Does that mean I have to write out the location of the file? Sorry, I'm still kinda new to the program.

Is it like:
conn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Vbdata\AddressBook.mdb "
even though I have already set my provider and data source in the properties of the project, will it crash?

[Deepak Sakpal]

Does that mean I have to write out the location of the file? Sorry, I'm still kinda new to the program.

Is it like:
conn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Vbdata\AddressBook.mdb "
even though I have already set my provider and data source in the properties of the project, will it crash?

Write it like this:

Dim ConnString As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Vbdata\AddressBook.mdb"
Dim connection As New OleDbConnection(ConnString)

[alex_read]

In addition to the above advice (especially on the parameters and connectionstring)...

Hi actually I am not doing a multi-user access. It is just that from the list of login names and password, it must be able to validate its credentials from the access file; afterall, whichever login names/pass used, all will be directed in using the same forms avaliable in the application.

Whether multi-user or not, the code you need for this is either as the Coach's above or the link to JMC's thread also given above.

Erm by the way, the code that CoachBarker give, and i have edited and paste it in my form, OleDbConnection and OleDbCommand is considered not defined. Why?

Every class in the .Net framework has a namespace preceeding it. In order to type out commands, you must use the full syntax and namespace path. When you call OLEDBConnection, you are actually accessing the class like this: System.Data.OLEDB.OleDBConnection. Therefore you have 2 options, either to write the System.Data.OleDB out every time you use a call to the OleDBConnection, or you can declare this namespace at the top of the file you are using in order for the .Net framework to "know" where the OleDBConnection class comes from/relates to.

Can someone guide me?

You really need to run through a base tutorial which 2 of us have suggested above. However I suggest you read up on the following to gain an understanding of what they are for and further help you understand the code sample provided:

1. OleDBConnection
2. OleDBCommand
3. ExecuteScalar, ExecuteNonQuery

I got an error saying 'The ConnectionString property has not been initialized.' in my login code

Check out the explaination of what a connectionstring is, and is used for from this post: http://www.vbforums.com/showpost.php...1&postcount=13 and then check out http://www.connectionstrings.com/ in order to help you create the right connectionstring suitable for you.

[alex_read]

Ok, er so how do I know if the above code is validating with the User queries I have made in my access file?

Within the coding the Coach provided, the term ExecuteScalar has been used. To save you looking this part up, there are several Executexxxx methods which can be called from an ADO.Net command object. Here is the ExecuteScalar one:

1. ExecuteScalar: Run the SQL command SQL with it's neccessary parameter values and settings, and from whatever valid information results, return (grab) the first record of the first row only.

What this means is if you call SELECT * FROM Orders using the Northwind database, using an Executescalar call, the first column (OrderID) will be looked at, and the value within the first row of this column returned - everything else is ignored at this point.

In your case, it looks as though you will get back the 1st value of your UserID column if the user exists within your database. MSDN states that the ExecuteScalar method returns a NULL, or in VB terms, a NOTHING value if no items were returned. Therefore, you can use this code to check whether your user is valid in replace of the code you have:

If command.ExecuteScalar() = Nothing Then
    ' User does not exist.
Else
    ' User with userid and password used in where clause filter exists in DB
End If

[alex_read]

One final note then I think everything's explained and I can shut up. This is simply a further note to the correct answer Deepak has already suggested.

        Dim command As New OleDbCommand("SELECT COUNT(*) FROM Users WHERE Login = @Login AND Password = @Password", _
                                             connection)
        With command.Parameters
            .AddWithValue("@UserID", userName)
            .AddWithValue("@Password", userPassword)
        End With

When writing code in .Net, you should never ever write SQL strings such as

SELECT * FROM tblUser WHERE UserID=txtUserName.text

The main reasons are twofold.

1. Firstly, the parameter you use in your WHERE clause (in my example a textbox value) could contain mallicious SQL Injection code in order to screw up the SQL statement being constructed and either retreive or delete data.
2. The second is the escaping of certain symbol characters. If, for example that parameter value contained a single quote, you can appreciate the resulting SQL will be rendered unusable and generate an error upon execution.

Utilising, in your case the OleDbParameters collection will avoid both of these for a start. What you do is simply assign an SQL variable in replace of the value (i.e. taking the example just above):

SELECT * FROM tblUser WHERE UserID=@MyFunkyUserIDHere

Then add a parameter with the SAME name as this (hence the issue you had which Deepak nicely pointed out):

OleDBCommandObject.Parameters.AddWithValue("=@MyFunkyUserIDHere", txtUserName.text)

Always use parameters in this way when writing database code!

[melvados]

Noted your advice, alex. Before that, can someone explain to me what does the code below means?

    Dim tempBoolean As Boolean = False
        If CInt(command.ExecuteScalar()) = 0 Then
            MsgBox("You have not entered the correct password.")
        Else
            tempBoolean = True
        End If
        connection.Close()
        If tempBoolean Then
            ' do something here            
            Me.Hide()
            Admin.Show()
        Else
            ' do something else here        
        End If
    End Sub

I do not understand the part in bold. What is tempBoolean anyway?

Anyway, in the first part of IfElse, since it has stated that the tempboolean = true, in the second part for the tempBoolean IfElse statement, whether the code is written as

 If tempBoolean Then

or

 If tempBoolean = True Then

; it can still works?

I'm not able to see the 'link'.

[Deepak Sakpal]

tempBoolean is storing a Boolean value (True/False). When the user name or password is incorrect it's value will be False and if user name or password is correct then it will be True. When value of tempBoolean variable is True i.e. user name/password is correct then the next code segment displays the admin form and hides the current form.

[alex_read]

The syntax of an IF statement is IF Expression = True. Therefore technically If BooleanVariable Then and If BooleanVaraiable = True Then are the same thing. I always write the second as it makes the statement far more readable and understandable for anyone else who looks at the code.

Within that bold part, indeed all of that section of code you posted, the database is queried and the return value interrogated. The boolean variable is then set dependant upon whether a value is returned from the database or not (a declared boolean's default value is false by the way). This variable assignment is then followed by the closure of the database connection which is good practice (always close the connection as soon as possible). Finally, when the connection is closed, the boolean value is then interrogated and depending upon the result, the current windows form may be hidden and an admin form shown.

That is in theory what this code does, however as above, the ExecuteScalar method call returns a Nothing value, not a zero value if no records are returned which isn't checked for. I imagine ExecuteNonQuery method call was used instead here but then changed to an ExecuteScalar method call without the rest of the code being updated in the same way so I would change this part of the code to the sample given above in post #17.

[CoachBarker]

In my application when logging in there is a choice of two forms to open depending on what button is clicked. So the rest of my code looked like this:

If tempBoolean Then
      If Me.btnSignIn.Focused = True Then
          Dim questions As New frmQuestions
          questions.Show()
          Me.Close()
          Exit Sub
      Else
          If Me.btnUsers.Focused = True Then
              Dim Users As New frmUsers
              Users.Show()
              Me.Close()
              Exit Sub
          End If
      End If
End If

I guess I should have left out this part, sorry if it confused you

[melvados]

Thanks alot everyone!