Login via Access Database help
Hi,
I recently got VB 2005 and really like the new features. However, I am on an intermediate level learning Visual Basic. For a test project, I plan to create a login form that validates users through an access database located on a server. Basically, there is a set username and password in the access database and I want my program to execute the main program once the user is validated via the database.
I was struggling on this for about a week, but I have no idea on how to do it. I can arrange the controls on my form, but I just need to know how to validate the users.
Any ideas?
hey AnonymousI,
here's one solution:
1. add an odbcconnection object in your design view and assign the database that you'll be accessing.
2. create two textboxes, txtUsername and txtPassword and a command button, btnLogin
here's the code:
Imports System.Data.Odbc ' reference odbc
Public Class Form1
Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e System.EventArgs) AsHandles btnLogin.Click
OdbcConnection1.Open() ' open connection
Dim sql = "select * from tblusers where Username = '" + txtUsername.Text + "'"
Dim myAccessComm As New OdbcCommand(sql, OdbcConnection1)
Dim myAccessRead As OdbcDataReader = myAccessComm.ExecuteReader
While myAccessRead.Read
Dim Password As String= myAccessRead.GetValue(1).ToString '1 is the index value of password in database structure if there are two tables, first column is username, second is password
If txtPassword.Text = Password Then
MessageBox.Show("Login Successful")
'add more code to open your main program after successful login
Else
MessageBox.Show("Failed to Login")
End If
End While
OdbcConnection1.Close()
End Sub
End Class
good luck!
dave
a better approach:
Dim oleDbCommand as new OleDbCommand("SELECT [ID] FROM [TableName] WHERE [username] = ? AND [Password] = ?", new OleDbCommand(connectionString))
Dim param1 as new OleDbParameter("@username", "usernameHere")
Dim param2 as new OleDbParameter("@password", "passwordHere")
oleDbCommand.Parameters.Add(param1)
oleDbCommand.Parameters.Add(param2)
oleDbCommand.Connection.Open()
object result = oleDbCommand.ExecuteScaler()
oleDbCommand.Connection.Close()
if result is DBNull.Value = false then
'we have the correct user in the database
end if
sweet shorter code, i'll use this example too lol. thanks ahmedilyas!
hehe any time buddy! :-) That's what I'm here for, to try to help you and to improve your code for the better ;-)
Hi,
I had to write my code differently than the ones above because errors would appear in VB2005, but mine seems to have similar functions.. So, I tried to take a crack at it and after I ran my code, I get an error saying, "Request for the permission of type 'System.Data.OleDb.OleDbPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=xxxxxxxxxx' failed." How do I fix this?
I'm not sure if I did the code right. You're free to correct it.
Here's my code (my code is based on one of the snippets and the database file is real and has no password protection):
Imports
System.Data.OleDbPublic
Class Form1
Public Function ValidateIdentity() As Boolean
Dim AccessDatabase As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\myserver\share\TestUserPassword.mdb;Persist Security Info=False")AccessDatabase.Open()
Dim User_Entered As StringDim Password_Entered As StringUser_Entered = UserEntry.Text
Password_Entered = PasswordEntry.Text
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\myserver\share\TestUserPassword.mdb;Persist Security Info=False"Dim SQLCommand As String = "Select UserPassword.Student, UserPassword.Password from UserPassword where ((" & User_Entered & " = UserPassword.Student) AND (" & Password_Entered & " = UserPassword.Password))"Dim adapter As New OleDbDataAdapter(SQLCommand, connString)Dim UserPassword As New DataSetadapter.Fill(UserPassword)
AccessDatabase.Close()
If UserPassword Is DBNull.Value = False Then
Return TrueForm2.Show()
Me.Hide()Else
Return FalseMsgBox(
"Unable to authenticate using supplied user credentials." & ControlChars.NewLine & "Please check user credentials.", MsgBoxStyle.Critical = MsgBoxStyle.OkCancel, "Login Failure")If MsgBoxResult.Ok ThenUserEntry.Text =
""PasswordEntry.Text =
""ElseIf MsgBoxResult.Cancel Then
EndEnd IfEnd IfEnd FunctionPrivate Sub CheckLogin_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles CheckLogin.ClickValidateIdentity()
End SubEnd
Class
are you running your app over the network? (running your app from a server somewhere else on your computer)
Yeah, I am running it over the network. I have two computers in my home: XP Pro SP2 and Server 2003 (Primary domain controller). I am accessing the database from my server through the XP computer.
Hi thanks for the advice. Now that I know that my app can access the database, I have another problem. When the sql command is executed, I get a syntax error saying missing operator. However, when I test the sql query using Access with my database loaded, it works when I entered in the criteria for a student and its password. Can you help me?
Here is the sql command:
Dim SQLCommand As String = "Select UserPassword.Student, UserPassword.Password from UserPassword where ((" & User_Entered & " = UserPassword.Student) AND (" & Password_Entered & " = UserPassword.Password))"
SQL and MS Access have some differences. in addition, your query is incorrect...Try this...
Dim SQLCommand As String = "Select UserPassword.Student, UserPassword.Password from UserPassword where " UserPassword.Student = '" & User_Entered & " AND UserPassword.Password = '" & Password_Entered & "'"
now, you are best to use parameterized queries since they are safer and can cause less confusion to you. So instead of giving it direct values in your sql string, just give it the names of parameters so it can look at them in the parameter collection of the sqlcommand object. In the sql parameter object, give it the parameter name as well as the length of the value and finally the value itself via the .Value property
Hello, sorry to bother you... After reading your post, I realized that your previous code makes sense to me now. So, I threw out my code and used yours. I was so glad to find out that it works. However, when I try to login, the next form always shows up weather or not the username and password are correct - it even goes through when I enter nothing at all. Am I missing something?
Heres the code:
Imports
System.Data.OleDbPublic
Class Form1
Public Function ValidateIdentity() As Boolean
Dim oledbcommand As New OleDbCommand(("SELECT UserPassword.* FROM UserPassword WHERE UserPassword.Student = ? AND UserPassword.Password = ?"), New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\192.168.0.100\ftp server\TestUserPassword.mdb;Persist Security Info=False"))Dim param1 As New OleDbParameter("@Username", "UserEntry.text")Dim param2 As New OleDbParameter("@Password", "PasswordEntry.text")oledbcommand.Parameters.Add(param1)
oledbcommand.Parameters.Add(param2)
oledbcommand.Connection.Open()
Dim result As Objectresult = oledbcommand.ExecuteScalar()
oledbcommand.ExecuteScalar()
oledbcommand.Connection.Close()
If result Is DBNull.Value = False Then
Return TrueElse
Return FalseEnd IfEnd FunctionPrivate Sub CheckLogin_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles CheckLogin.ClickValidateIdentity()
If ValidateIdentity() = True ThenForm2.Show()
Me.Hide()ElseIf ValidateIdentity() = False Then
EndEnd IfEnd SubEnd
ClassI would like to start by saying Thank you all for the Post you have helped me out a Lot here is what i came up with and it works Well
Imports
System.Data.OleDbPublic
Class LoginForm1Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles OK.ClickDim myConnection As OleDbConnectionmyConnection =
New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=my password.mdb;Persist Security Info=False")Dim myCommand As OleDbCommandmyCommand =
New OleDbCommand("SELECT [UserName],[UserPassword] FROM login WHERE [UserName] = @UserName AND [UserPassword] = @UserPassword", myConnection)Dim uName As New OleDbParameter("@UserName", SqlDbType.NVarChar)Dim uPassword As New OleDbParameter("@UserPassword", SqlDbType.NVarChar)uName.Value = UsernameTextBox.Text
uPassword.Value = PasswordTextBox.Text
myCommand.Parameters.Add(uName)
myCommand.Parameters.Add(uPassword)
myCommand.Connection.Open()
Dim myReader As OleDbDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)Dim Login As Object = 0If myReader.HasRows ThenmyReader.Read()
Login = myReader(Login)
End IfIf Login = Nothing ThenMessageBox.Show(
"Sorry, username/password details were incorrect")UsernameTextBox.Text =
""PasswordTextBox.Text =
""UsernameTextBox.Focus()
ElseMessageBox.Show(
"Login successful")Me.Hide()Form1.Show()
End IfEnd SubPrivate Sub Cancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Cancel.ClickMe.Close()End SubPrivate Sub LoginForm1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.LoadEnd SubEnd
ClassHi Dave,
Thank you for your help...it was really very helpful.
Here i have one more query..
can you please tell me about crystal report.. ? how to creat it and connect it with databse and sql queries...?
Thanks
Vimal
Dim
myReader As OleDbDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)Has anyone seen an OLE exception error from this line? I can't get it to work on VB 2005
