-
[RESOLVED] [2005] How to make the prog compare the values from database with the values input
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!
-
Re: [2005] How to make the prog compare the values from database with the values input
Well... It seems that you are a fresher in programming. You need to lean vb.net. Start it here http://www.startvbdotnet.com.
-
Re: [2005] How to make the prog compare the values from database with the values input
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...
-
Re: [2005] How to make the prog compare the values from database with the values input
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.
-
Re: [2005] How to make the prog compare the values from database with the values input
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
vb Code:
Dim conn As New OleDbConnection()
vb Code:
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
-
Re: [2005] How to make the prog compare the values from database with the values input
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.
-
Re: [2005] How to make the prog compare the values from database with the values input
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?
-
Re: [2005] How to make the prog compare the values from database with the values input
Add this to the very top of your code:
Code:
Imports System.Data
Imports System.Data.OleDb
-
Re: [2005] How to make the prog compare the values from database with the values input
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...
Code:
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
-
Re: [2005] How to make the prog compare the values from database with the values input
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.
vb.net Code:
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
-
Re: [2005] How to make the prog compare the values from database with the values input
This will crash out because in your SQL statement you specify
Code:
WHERE Login = @Login
But you add this parameter to your command:
Code:
.AddWithValue("@UserID", userName)
Either change your SQL statement or change your parameter to @Login
-
2 Attachment(s)
Re: [2005] How to make the prog compare the values from database with the values input
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...:o
-
Re: [2005] How to make the prog compare the values from database with the values input
That is because you have written
vb.net Code:
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:
vb.net Code:
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)
-
Re: [2005] How to make the prog compare the values from database with the values input
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?
-
Re: [2005] How to make the prog compare the values from database with the values input
Quote:
Originally Posted by 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?
Write it like this:
vb.net Code:
Dim ConnString As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Vbdata\AddressBook.mdb"
Dim connection As New OleDbConnection(ConnString)
-
Re: [2005] How to make the prog compare the values from database with the values input
In addition to the above advice (especially on the parameters and connectionstring)...
Quote:
Originally Posted by 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.
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.
Quote:
Originally Posted by melvados
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.
Quote:
Originally Posted by melvados
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:
- OleDBConnection
- OleDBCommand
- ExecuteScalar, ExecuteNonQuery
Quote:
Originally Posted by melvados
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.
-
Re: [2005] How to make the prog compare the values from database with the values input
Quote:
Originally Posted by 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?
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:
- 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:
Code:
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
-
Re: [2005] How to make the prog compare the values from database with the values input
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.
Quote:
Originally Posted by melvados
Code:
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
Code:
SELECT * FROM tblUser WHERE UserID=txtUserName.text
The main reasons are twofold. - 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.
- 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):
Code:
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):
Code:
OleDBCommandObject.Parameters.AddWithValue("=@MyFunkyUserIDHere", txtUserName.text)
Always use parameters in this way when writing database code!
-
Re: [2005] How to make the prog compare the values from database with the values input
Noted your advice, alex. Before that, can someone explain to me what does the code below means?
Code:
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
Code:
If tempBoolean Then
or
Code:
If tempBoolean = True Then
; it can still works?
I'm not able to see the 'link'.
-
Re: [2005] How to make the prog compare the values from database with the values input
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.
-
Re: [2005] How to make the prog compare the values from database with the values input
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.
-
Re: [2005] How to make the prog compare the values from database with the values input
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:
vb Code:
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 :p
-
Re: [2005] How to make the prog compare the values from database with the values input