-
Nov 13th, 2007, 12:28 AM
#1
Validate Credentials Against User Record in Database
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()
Last edited by jmcilhinney; Nov 23rd, 2008 at 06:39 PM.
-
Nov 13th, 2007, 12:38 AM
#2
Re: Validate Credentials Against User Record in Database
-
Nov 13th, 2007, 12:52 AM
#3
Re: Validate Credentials Against User Record in Database
Originally Posted by penagate
Tpyo
Thknas vrey mcuh.
-
Nov 17th, 2008, 11:37 AM
#4
Fanatic Member
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
-
Nov 17th, 2008, 05:50 PM
#5
Re: Validate Credentials Against User Record in Database
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
#6
New Member
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.
-
Feb 6th, 2009, 03:37 PM
#7
Hyperactive Member
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)
Last edited by youngbucks; Feb 6th, 2009 at 04:03 PM.
-
Feb 6th, 2009, 08:37 PM
#8
Re: Validate Credentials Against User Record in Database
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
-
Jun 28th, 2010, 12:08 AM
#9
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:
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:
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
CodeBank contributions: Process Manager, Temp File Cleaner
Originally Posted by SJWhiteley
"game trainer" is the same as calling the act of robbing a bank "wealth redistribution"....
-
Jun 28th, 2010, 12:12 AM
#10
Re: Validate Credentials Against User Record in Database
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
#11
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
Originally Posted by SJWhiteley
"game trainer" is the same as calling the act of robbing a bank "wealth redistribution"....
-
Jun 28th, 2010, 12:52 AM
#12
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
#13
Re: Validate Credentials Against User Record in Database
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.
CodeBank contributions: Process Manager, Temp File Cleaner
Originally Posted by SJWhiteley
"game trainer" is the same as calling the act of robbing a bank "wealth redistribution"....
-
Jun 28th, 2010, 01:18 AM
#14
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:
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
CodeBank contributions: Process Manager, Temp File Cleaner
Originally Posted by SJWhiteley
"game trainer" is the same as calling the act of robbing a bank "wealth redistribution"....
-
Jun 28th, 2010, 01:22 AM
#15
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
#16
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
#17
Re: Validate Credentials Against User Record in Database
Originally Posted by penagate
Isn't 'User' a reserved word in some DBMSs? Try a completely different name.
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?
CodeBank contributions: Process Manager, Temp File Cleaner
Originally Posted by SJWhiteley
"game trainer" is the same as calling the act of robbing a bank "wealth redistribution"....
-
Jun 28th, 2010, 02:26 AM
#18
Re: Validate Credentials Against User Record in Database
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
#19
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?
-
Apr 6th, 2011, 09:24 PM
#20
Hyperactive Member
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
#21
Re: Validate Credentials Against User Record in Database
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
#22
Hyperactive Member
Re: Validate Credentials Against User Record in Database
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
#23
Re: Validate Credentials Against User Record in Database
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|