Validate Credentials Against User Record in Database

Printable View

C# version here.

VB.NET Code:

Dim connection As New SqlConnection("connection string here")
 
'Get the count of the records with matching user ID and password.
Dim command As New SqlCommand("SELECT COUNT(*) FROM User WHERE UserID = @UserID AND Password = @Password", _
                              connection)
 
'Add the parameters.  Values might come from TextBoxes or wherever.
With command.Parameters
    .AddWithValue("@UserID", userID)
    .AddWithValue("@Password", password)
End With
 
connection.Open()
 
'Execute the query.
If CInt(command.ExecuteScalar()) = 0 Then
    'Zero matching records means a failed login.
Else
    'The specified credentials do match a record so the login succeeds.
End If
 
connection.Close()

Nov 13th, 2007, 12:38 AM
penagate

Re: Validate Credentials Against User Record in Database

Tpyo

Quote:

AND Password = @Pasword
Nov 13th, 2007, 12:52 AM
jmcilhinney

Re: Validate Credentials Against User Record in Database

Quote:

Originally Posted by penagate

Tpyo

Thknas vrey mcuh.

Re: Validate Credentials Against User Record in Database

can that be used in place of the following code ?

vb.net Code:

Using Command As New SqlCommand("SELECT * FROM LoginInfo WHERE Username='" & My.Forms.FrmLogin.cboUserName.Text & "'", Connection)
 
                Using Reader As SqlDataReader = Command.ExecuteReader
                    While Reader.Read()
 
                        If My.Forms.FrmLogin.txtPassword.Text = Trim(Reader("Password")) Then
 
                            My.Forms.FrmLogin.toolStateStrip.Text = "State: Logged in."
 
                            GetUserData()
 
                            'ADD in a new form call here
 
                        Else
 
                            My.Forms.FrmLogin.toolStateStrip.Text = "State: Invalid Password."
 
                        End If
 
                    End While
 
                End Using
 
            End Using
 
        End Using

Re: Validate Credentials Against User Record in Database

Quote:
Originally Posted by TheUsed
can that be used in place of the following code ?
vb.net Code:
Using Command As New SqlCommand("SELECT * FROM LoginInfo WHERE Username='" & My.Forms.FrmLogin.cboUserName.Text & "'", Connection)
 
                Using Reader As SqlDataReader = Command.ExecuteReader
                    While Reader.Read()
 
                        If My.Forms.FrmLogin.txtPassword.Text = Trim(Reader("Password")) Then
 
                            My.Forms.FrmLogin.toolStateStrip.Text = "State: Logged in."
 
                            GetUserData()
 
                            'ADD in a new form call here
 
                        Else
 
                            My.Forms.FrmLogin.toolStateStrip.Text = "State: Invalid Password."
 
                        End If
 
                    End While
 
                End Using
 
            End Using
 
        End Using
If all you need to know is whether there is a matching user or not, as opposed to get information about that user, then you should do what I've done here. If you actually need information about the user then just getting a count of the matching records is not enough. You actually need to retrieve the matching record.

In your case though, why would you perform a query with a WHERE clause that includes the user name and then compare the password in your VB code? Why would you not include the password in the WHERE clause too?

Also, don't use string concatenation to build SQL statements. My example demonstrates how to do it properly with parameters.

Finally, I don't really see the point of performing one query to validate the user and then performing another to get information about that user. Why not just perform a single query to get all the information about the user that corresponds to the specified credentials? If there is such a user then you validate and get all the info in one query. If there is no such user then no info will be returned and you know that the login failed.

Feb 2nd, 2009, 06:55 PM
psp456

Re: Validate Credentials Against User Record in Database

thanks for this thread
it has helped me to understand vb 2005 more

I am trying to check my access 2007 database to see if the username and password is in it....
If not give them the response that it isnt there
but if it is it should send them to the form depending on the username enetered

here is the code

Private Sub btn_login_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_login.Click
Dim username
Dim pass

username = txtUser_login.Text
pass = txtUser_Pass.Text

Dim connection As New OleDbConnection(connect)
'Get the count of the records with matching user ID and password.
Dim command As New SqlCommand("SELECT COUNT(*) FROM Users WHERE user_name = @UserID AND password = @Password", connection)
'Add the parameters. Values might come from TextBoxes or wherever.
With command.Parameters
.AddWithValue("@UserID", username)
.AddWithValue("@Password", pass)
End With
connection.Open()

'Execute the query.
If CInt(command.ExecuteScalar()) = 0 Then
'Zero matching records means a failed login.
Else
'The specified credentials do match a record so the login succeeds.

End If
connection.Close()

If username = "admin" And pass = "1234" Then
frmAdmin.Show()
Me.Hide()

ElseIf username = "user" And pass = "1234" Then
frmAddClient.Show()
Me.Hide()

Else

MessageBox.Show("Invalid login")

End If

End Sub

could you guys help me out please :thumb:
Feb 6th, 2009, 03:37 PM
youngbucks

Re: Validate Credentials Against User Record in Database
I want to use this to verify the login details but i want to add an extra parameter to check the Key they provided. Also i have a question as to what this line means exactly:
vb Code:
```
Dim command As New SqlCommand("SELECT COUNT(*) FROM User WHERE UserID = @UserID AND Password = @Password", connection)
```
I added this value to the code so far and how it affect the above snippet in its current state?:
vb Code:
```
.AddWithValue("@Key", Key)
```
Feb 6th, 2009, 08:37 PM
jmcilhinney

Re: Validate Credentials Against User Record in Database
Quote:
Originally Posted by youngbucks

I want to use this to verify the login details but i want to add an extra parameter to check the Key they provided. Also i have a question as to what this line means exactly:

vb Code:

Dim command As New SqlCommand("SELECT COUNT(*) FROM User WHERE UserID = @UserID AND Password = @Password", connection)

I added this value to the code so far and how it affect the above snippet in its current state?:

vb Code:

.AddWithValue("@Key", Key)
That second line of code is saying that, wherever the place-holder "@Key" is found in the SQL code, it should be replaced with the value of the 'Key' variable. As you haven't changed the SQL code to include the "@Key" place-holder, that will have no effect. You have to change the SQL code to something like:

Code:

SELECT COUNT(*) FROM User WHERE UserID = @UserID AND Password = @Password AND [Key] = @Key

Re: Validate Credentials Against User Record in Database

Hey JMC,

When I use your code as is, I receive the following error:

Quote:

Invalid object name 'User'.

On the following line:
VB.NET Code:
If CInt(command.ExecuteScalar()) = 0 Then
When I look into the error, from the debugger help section, there are two generic tips:

Quote:

Verify that you are connecting with valid credentials.
Make sure that the credentials you are supplying are valid. For more information, see How to: Access SQL Server Using Predetermined Credentials.

Verify that the server name is correct and that the server is running.
Make sure that you are using the correct server name, and that the server can be reached. For more information, see How to: Create Connections to SQL Server Databases.

I've made sure that the credentials are 100% correct and the connection string was copy and pasted from the SQL database wizard.

For references purposes, the following is the code I'm using:
VB.NET Code:
Dim connection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Ernest\Desktop\dbCredentials.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
 
        Dim command As New SqlCommand("SELECT COUNT(*) FROM User WHERE UserID = @UserID AND Password = @Password", _
                              connection)
 
        With command.Parameters
            .AddWithValue("@UserID", Me.txtUserName.Text)
            .AddWithValue("@Password", Me.txtPasssword.Text)
        End With
 
        connection.Open()
 
        If CInt(command.ExecuteScalar()) = 0 Then
            
        Else
            Me.DialogResult = Windows.Forms.DialogResult.OK
        End If
 
        connection.Close()
I've made sure to name my table, User and the columns, UserID and Password.

Any ideas?

Thanks

Re: Validate Credentials Against User Record in Database

Quote:
Originally Posted by weirddemon
Hey JMC,

When I use your code as is, I receive the following error:

On the following line:
VB.NET Code:
If CInt(command.ExecuteScalar()) = 0 Then
When I look into the error, from the debugger help section, there are two generic tips:

I've made sure that the credentials are 100% correct and the connection string was copy and pasted from the SQL database wizard.

For references purposes, the following is the code I'm using:
VB.NET Code:
Dim connection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Ernest\Desktop\dbCredentials.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
 
        Dim command As New SqlCommand("SELECT COUNT(*) FROM User WHERE UserID = @UserID AND Password = @Password", _
                              connection)
 
        With command.Parameters
            .AddWithValue("@UserID", Me.txtUserName.Text)
            .AddWithValue("@Password", Me.txtPasssword.Text)
        End With
 
        connection.Open()
 
        If CInt(command.ExecuteScalar()) = 0 Then
            
        Else
            Me.DialogResult = Windows.Forms.DialogResult.OK
        End If
 
        connection.Close()
I've made sure to name my table, User and the columns, UserID and Password.

Any ideas?

Thanks
Your SQL code says 'Users' and your error message says 'User' so that doesn't seem quite right.

Jun 28th, 2010, 12:32 AM
weirddemon

Re: Validate Credentials Against User Record in Database

Both my SQL code and error state 'User'.

My table is named Users and was originally copied over as such. I changed it after copying it over... for some reason.... I can't remember now.

My table is named 'Users' and I reference the same in the SQL code.
Jun 28th, 2010, 12:52 AM
jmcilhinney

Re: Validate Credentials Against User Record in Database

I could have sworn that your SQL code was querying a table name 'Users' but now I look at it and it says 'User', so either you changed it or I'm finally loosing it. :) In your most recent post you first say that your SQL code says 'User' and then you say that it says 'Users'. You need to make up your mind and you need to use the actual name of the table in the SQL code.
Jun 28th, 2010, 01:13 AM
weirddemon

Re: Validate Credentials Against User Record in Database

Quote:

Originally Posted by jmcilhinney

I could have sworn that your SQL code was querying a table name 'Users' but now I look at it and it says 'User', so either you changed it or I'm finally loosing it. :) In your most recent post you first say that your SQL code says 'User' and then you say that it says 'Users'. You need to make up your mind and you need to use the actual name of the table in the SQL code.

My code is 'Users' and so is the table. I changed it to 'User' here to replicate the code you have created.

Regardless of the exact name, the SQL code and table both reflect the same name.

Re: Validate Credentials Against User Record in Database

For simplicity's sake, I deleted the table and created a new one as 'User' and tried the code again. Now I'm receiving the following error:

Quote:

Incorrect syntax near the keyword 'User'.

-_-

Exact code:

vb Code:

Dim connection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Ernest\Desktop\dbCredentials.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
 
        Dim command As New SqlCommand("SELECT COUNT(*) FROM User WHERE UserID = @UserID AND Password = @Password", _
                              connection)
 
        With command.Parameters
            .AddWithValue("@UserID", Me.txtUserName.Text)
            .AddWithValue("@Password", Me.txtPasssword.Text)
        End With
 
        connection.Open()
 
        If CInt(command.ExecuteScalar()) = 0 Then
            
        Else
            Me.DialogResult = Windows.Forms.DialogResult.OK
        End If
 
        connection.Close()

Just so there's no confusion on what the table is named and how I'm using it ;)

Jun 28th, 2010, 01:22 AM
penagate

Re: Validate Credentials Against User Record in Database

Isn't 'User' a reserved word in some DBMSs? Try a completely different name.
Jun 28th, 2010, 01:27 AM
jmcilhinney

Re: Validate Credentials Against User Record in Database

If you're using 'User' as the table name then you'll probably have to escape it, i.e. use '[User]' in your SQL code.
Jun 28th, 2010, 02:08 AM
weirddemon

Re: Validate Credentials Against User Record in Database

Quote:

Originally Posted by penagate

Isn't 'User' a reserved word in some DBMSs? Try a completely different name.

Quote:

Originally Posted by jmcilhinney

If you're using 'User' as the table name then you'll probably have to escape it, i.e. use '[User]' in your SQL code.

Right. That makes sense.

However, when I change the table to any other name, and reference it appropriately, I get the original error.

Is there any other things I should have done when setting up the database or table?

One thing I thought was strange, was the SQL databases I could create from the wizard.

When I choose a new data source, I had two options:

Microsoft SQL Server and
Microsoft SQL Server Database File

I chose the latter as it seemed more appropriate.

That wouldn't affect this at all, right?
Jun 28th, 2010, 02:26 AM
jmcilhinney

Re: Validate Credentials Against User Record in Database

Quote:

Originally Posted by weirddemon

Right. That makes sense.

However, when I change the table to any other name, and reference it appropriately, I get the original error.

Is there any other things I should have done when setting up the database or table?

One thing I thought was strange, was the SQL databases I could create from the wizard.

When I choose a new data source, I had two options:

Microsoft SQL Server and
Microsoft SQL Server Database File

I chose the latter as it seemed more appropriate.

That wouldn't affect this at all, right?

If you don't have the word 'User' in your SQL code then I can't see why you'd get an error saying that 'User' is wrong. I have no explanation.
Jun 28th, 2010, 02:31 AM
penagate

Re: Validate Credentials Against User Record in Database

I can only suppose that the database file is not actually being loaded. Can you run a query against it using another tool, such as sqlcmd?

Re: Validate Credentials Against User Record in Database

thanks for this jm..

but can you do a explanation in this code..

vb Code:

Dim command As New OleDb.OleDbCommand("SELECT * FROM tblUserAccount WHERE Username = '" & txtUserName.Text & "' AND Password = '" & txtPassword.Text & "'", _
                                          connection)
 
            With command.Parameters
                .AddWithValue("Username", txtUserName.Text)
                .AddWithValue("Password", txtPassword.Text)
            End With
 
            connection.Open()
 
            'Execute the query.
            If CInt(command.ExecuteScalar()) = 0 Then               
                MsgBox(" failed Login")
            Else
                main.Show()
            End If

sorry new in vb.net
what is the use of this code
command.Parameters
.AddWithValue
CInt(command.ExecuteScalar()) = 0

thanks

Apr 6th, 2011, 09:38 PM
jmcilhinney

Re: Validate Credentials Against User Record in Database

Quote:

Originally Posted by marniel647

what is the use of this code
command.Parameters
.AddWithValue
CInt(command.ExecuteScalar()) = 0

thanks

Follow the Blog link in my signature and check out my post on ADO.NET parameters.

For the last line, follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data.
Apr 6th, 2011, 09:54 PM
marniel647

Re: Validate Credentials Against User Record in Database

Quote:

Originally Posted by jmcilhinney

Follow the Blog link in my signature and check out my post on ADO.NET parameters.

For the last line, follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data.

thanks ok i got what is the use of execute.scalar it returns the value of the first column of the firstrow..
so the execute.scalar need to have a CInt to run...?
Apr 6th, 2011, 10:44 PM
jmcilhinney

Re: Validate Credentials Against User Record in Database

Quote:

Originally Posted by marniel647

so the execute.scalar need to have a CInt to run...?

ExecuteScalar returns an Object reference, because it can return any type of data that you can store in a database. If the actual value is an Integer, it must be cast as type Integer to be used as an Integer, which is what CInt does. With Option Strict Off, which it is by default, such casts can occur implicitly. With Option Strict On, which it should always be, you have to make explicit casts. With Option Strict On, you can't compare an Integer and an Object.

All times are GMT -5. The time now is 05:25 AM.