Results 1 to 23 of 23

Thread: Validate Credentials Against User Record in Database

  1. #1

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Validate Credentials Against User Record in Database

    C# version here.

    VB.NET Code:
    1. Dim connection As New SqlConnection("connection string here")
    2.  
    3. 'Get the count of the records with matching user ID and password.
    4. Dim command As New SqlCommand("SELECT COUNT(*) FROM User WHERE UserID = @UserID AND Password = @Password", _
    5.                               connection)
    6.  
    7. 'Add the parameters.  Values might come from TextBoxes or wherever.
    8. With command.Parameters
    9.     .AddWithValue("@UserID", userID)
    10.     .AddWithValue("@Password", password)
    11. End With
    12.  
    13. connection.Open()
    14.  
    15. 'Execute the query.
    16. If CInt(command.ExecuteScalar()) = 0 Then
    17.     'Zero matching records means a failed login.
    18. Else
    19.     'The specified credentials do match a record so the login succeeds.
    20. End If
    21.  
    22. connection.Close()

  2. #2
    I'm about to be a PowerPoster!
    Join Date
    Jan 2005
    Location
    Everywhere
    Posts
    13,647

    Re: Validate Credentials Against User Record in Database

    Tpyo

    AND Password = @Pasword

  3. #3

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Validate Credentials Against User Record in Database

    Quote Originally Posted by penagate
    Tpyo
    Thknas vrey mcuh.

  4. #4
    Fanatic Member
    Join Date
    Sep 2005
    Location
    Toledo, OH
    Posts
    785

    Re: Validate Credentials Against User Record in Database

    can that be used in place of the following code ?

    vb.net Code:
    1. Using Command As New SqlCommand("SELECT * FROM LoginInfo WHERE Username='" & My.Forms.FrmLogin.cboUserName.Text & "'", Connection)
    2.  
    3.                 Using Reader As SqlDataReader = Command.ExecuteReader
    4.                     While Reader.Read()
    5.  
    6.                         If My.Forms.FrmLogin.txtPassword.Text = Trim(Reader("Password")) Then
    7.  
    8.                             My.Forms.FrmLogin.toolStateStrip.Text = "State: Logged in."
    9.  
    10.                             GetUserData()
    11.  
    12.                             'ADD in a new form call here
    13.  
    14.                         Else
    15.  
    16.                             My.Forms.FrmLogin.toolStateStrip.Text = "State: Invalid Password."
    17.  
    18.                         End If
    19.  
    20.                     End While
    21.  
    22.                 End Using
    23.  
    24.             End Using
    25.  
    26.         End Using

  5. #5

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    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:
    1. Using Command As New SqlCommand("SELECT * FROM LoginInfo WHERE Username='" & My.Forms.FrmLogin.cboUserName.Text & "'", Connection)
    2.  
    3.                 Using Reader As SqlDataReader = Command.ExecuteReader
    4.                     While Reader.Read()
    5.  
    6.                         If My.Forms.FrmLogin.txtPassword.Text = Trim(Reader("Password")) Then
    7.  
    8.                             My.Forms.FrmLogin.toolStateStrip.Text = "State: Logged in."
    9.  
    10.                             GetUserData()
    11.  
    12.                             'ADD in a new form call here
    13.  
    14.                         Else
    15.  
    16.                             My.Forms.FrmLogin.toolStateStrip.Text = "State: Invalid Password."
    17.  
    18.                         End If
    19.  
    20.                     End While
    21.  
    22.                 End Using
    23.  
    24.             End Using
    25.  
    26.         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.

  6. #6
    New Member
    Join Date
    Feb 2009
    Posts
    4

    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
    Last edited by psp456; Feb 3rd, 2009 at 07:33 PM.

  7. #7
    Hyperactive Member
    Join Date
    Jun 2008
    Location
    Nowhere
    Posts
    427

    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:
    1. 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:
    1. .AddWithValue("@Key", Key)
    Last edited by youngbucks; Feb 6th, 2009 at 04:03 PM.
    "Programming is like sex. One mistake and you have to support it for the rest of your life." ~Michael Sinz


    Code Snippets/Usefull Links:
    WinRAR DLL|Vista Style Form|Krypton Component Library|Windows Form Aero|Parsing XML files|Calculate File's CRC 32|Download a list of files.

  8. #8

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    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:
    1. 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:
    1. .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

  9. #9
    Wait... what? weirddemon's Avatar
    Join Date
    Jan 2009
    Location
    USA
    Posts
    3,828

    Re: Validate Credentials Against User Record in Database

    Hey JMC,

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

    Invalid object name 'User'.
    On the following line:

    VB.NET Code:
    1. If CInt(command.ExecuteScalar()) = 0 Then

    When I look into the error, from the debugger help section, there are two generic tips:

    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:
    1. Dim connection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Ernest\Desktop\dbCredentials.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
    2.  
    3.         Dim command As New SqlCommand("SELECT COUNT(*) FROM User WHERE UserID = @UserID AND Password = @Password", _
    4.                               connection)
    5.  
    6.         With command.Parameters
    7.             .AddWithValue("@UserID", Me.txtUserName.Text)
    8.             .AddWithValue("@Password", Me.txtPasssword.Text)
    9.         End With
    10.  
    11.         connection.Open()
    12.  
    13.         If CInt(command.ExecuteScalar()) = 0 Then
    14.            
    15.         Else
    16.             Me.DialogResult = Windows.Forms.DialogResult.OK
    17.         End If
    18.  
    19.         connection.Close()

    I've made sure to name my table, User and the columns, UserID and Password.

    Any ideas?

    Thanks
    CodeBank contributions: Process Manager, Temp File Cleaner

    Quote Originally Posted by SJWhiteley
    "game trainer" is the same as calling the act of robbing a bank "wealth redistribution"....

  10. #10

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Validate Credentials Against User Record in Database

    Quote Originally Posted by weirddemon View Post
    Hey JMC,

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



    On the following line:

    VB.NET Code:
    1. 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:
    1. Dim connection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Ernest\Desktop\dbCredentials.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
    2.  
    3.         Dim command As New SqlCommand("SELECT COUNT(*) FROM User WHERE UserID = @UserID AND Password = @Password", _
    4.                               connection)
    5.  
    6.         With command.Parameters
    7.             .AddWithValue("@UserID", Me.txtUserName.Text)
    8.             .AddWithValue("@Password", Me.txtPasssword.Text)
    9.         End With
    10.  
    11.         connection.Open()
    12.  
    13.         If CInt(command.ExecuteScalar()) = 0 Then
    14.            
    15.         Else
    16.             Me.DialogResult = Windows.Forms.DialogResult.OK
    17.         End If
    18.  
    19.         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.

  11. #11
    Wait... what? weirddemon's Avatar
    Join Date
    Jan 2009
    Location
    USA
    Posts
    3,828

    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.
    CodeBank contributions: Process Manager, Temp File Cleaner

    Quote Originally Posted by SJWhiteley
    "game trainer" is the same as calling the act of robbing a bank "wealth redistribution"....

  12. #12

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    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.

  13. #13
    Wait... what? weirddemon's Avatar
    Join Date
    Jan 2009
    Location
    USA
    Posts
    3,828

    Re: Validate Credentials Against User Record in Database

    Quote Originally Posted by jmcilhinney View Post
    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.
    CodeBank contributions: Process Manager, Temp File Cleaner

    Quote Originally Posted by SJWhiteley
    "game trainer" is the same as calling the act of robbing a bank "wealth redistribution"....

  14. #14
    Wait... what? weirddemon's Avatar
    Join Date
    Jan 2009
    Location
    USA
    Posts
    3,828

    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:

    Incorrect syntax near the keyword 'User'.
    -_-

    Exact code:

    vb Code:
    1. Dim connection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Ernest\Desktop\dbCredentials.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
    2.  
    3.         Dim command As New SqlCommand("SELECT COUNT(*) FROM User WHERE UserID = @UserID AND Password = @Password", _
    4.                               connection)
    5.  
    6.         With command.Parameters
    7.             .AddWithValue("@UserID", Me.txtUserName.Text)
    8.             .AddWithValue("@Password", Me.txtPasssword.Text)
    9.         End With
    10.  
    11.         connection.Open()
    12.  
    13.         If CInt(command.ExecuteScalar()) = 0 Then
    14.            
    15.         Else
    16.             Me.DialogResult = Windows.Forms.DialogResult.OK
    17.         End If
    18.  
    19.         connection.Close()

    Just so there's no confusion on what the table is named and how I'm using it
    CodeBank contributions: Process Manager, Temp File Cleaner

    Quote Originally Posted by SJWhiteley
    "game trainer" is the same as calling the act of robbing a bank "wealth redistribution"....

  15. #15
    I'm about to be a PowerPoster!
    Join Date
    Jan 2005
    Location
    Everywhere
    Posts
    13,647

    Re: Validate Credentials Against User Record in Database

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

  16. #16

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    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.

  17. #17
    Wait... what? weirddemon's Avatar
    Join Date
    Jan 2009
    Location
    USA
    Posts
    3,828

    Re: Validate Credentials Against User Record in Database

    Quote Originally Posted by penagate View Post
    Isn't 'User' a reserved word in some DBMSs? Try a completely different name.
    Quote Originally Posted by jmcilhinney View Post
    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?
    CodeBank contributions: Process Manager, Temp File Cleaner

    Quote Originally Posted by SJWhiteley
    "game trainer" is the same as calling the act of robbing a bank "wealth redistribution"....

  18. #18

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Validate Credentials Against User Record in Database

    Quote Originally Posted by weirddemon View Post
    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.

  19. #19
    I'm about to be a PowerPoster!
    Join Date
    Jan 2005
    Location
    Everywhere
    Posts
    13,647

    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?

  20. #20
    Hyperactive Member marniel647's Avatar
    Join Date
    Aug 2010
    Location
    MSDN Library
    Posts
    259

    Re: Validate Credentials Against User Record in Database

    thanks for this jm..

    but can you do a explanation in this code..

    vb Code:
    1. Dim command As New OleDb.OleDbCommand("SELECT * FROM tblUserAccount WHERE Username = '" & txtUserName.Text & "' AND Password = '" & txtPassword.Text & "'", _
    2.                                           connection)
    3.  
    4.             With command.Parameters
    5.                 .AddWithValue("Username", txtUserName.Text)
    6.                 .AddWithValue("Password", txtPassword.Text)
    7.             End With
    8.  
    9.             connection.Open()
    10.  
    11.             'Execute the query.
    12.             If CInt(command.ExecuteScalar()) = 0 Then              
    13.                 MsgBox(" failed Login")
    14.             Else
    15.                 main.Show()
    16.             End If

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

    thanks

  21. #21

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Validate Credentials Against User Record in Database

    Quote Originally Posted by marniel647 View Post
    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.

  22. #22
    Hyperactive Member marniel647's Avatar
    Join Date
    Aug 2010
    Location
    MSDN Library
    Posts
    259

    Re: Validate Credentials Against User Record in Database

    Quote Originally Posted by jmcilhinney View Post
    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...?

  23. #23

    Thread Starter
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: Validate Credentials Against User Record in Database

    Quote Originally Posted by marniel647 View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width