-
Apr 15th, 2014, 03:47 PM
#1
Thread Starter
New Member
[RESOLVED] user database
Hi, I am currently working on a project and I have some questions aswell as some issues that I want to be resolved.
First of all, I am going to give some additional information about my project so that the meaning if it becomes clear.
The project is based on an imaginary enterprise, it delivers computerparts and leases servers. The project is connected to an Access 2007 database. the database has different tables calles users, customers, products and suppliers. each table has records with an id. But for this thread I am going to focus on the users table, this one has the following fields:
- id
- username
- password
- first name
- last name
Now in the project I have set up my connection with the database and I already have made the login form, although that I have 1 small question in it.
here is the code:
Code:
Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
If txtUsername.Text = "" Or txtPassword.Text = "" Then
MessageBox.Show("please fill in the required fields.", "Authentication error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
Dim conn As New System.Data.OleDb.OleDbConnection()
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\User\Desktop\VBproject\access\GipDB.accdb;Persist Security Info=True;Jet OLEDB:Database Password=********"
Try
Dim sql As String = "SELECT * FROM users WHERE username='" & txtUsername.Text & "' AND password = '" & txtPassword.Text & "'"
Dim sqlCom As New System.Data.OleDb.OleDbCommand(sql)
sqlCom.Connection = conn
conn.Open()
Dim sqlRead As System.Data.OleDb.OleDbDataReader = sqlCom.ExecuteReader()
If sqlRead.Read() Then
'frmMainForm.Show()
MessageBox.Show(GlobalVars.Username & " " & GlobalVars.firstname & " " & GlobalVars.lastname, "info", MessageBoxButtons.OK, MessageBoxIcon.Information)
Me.Hide()
Else
MessageBox.Show("Username and password do not match.", "Authentication faillure", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
txtPassword.Clear()
txtUsername.Clear()
txtUsername.Focus()
End If
Catch ex As Exception
MessageBox.Show("Failed to connect to Database. System Error Message: " & ex.Message, "Database Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End If
End Sub
The question is the following: after the "If sqlRead.Read() Then" I want to insert certain values of the recordset into the following GlobalVars: username, firstname and lastname, all these variables are public declared in a Module. After that, the variables will be shown in the messagebox that follows after it.
the second part of this thread will be focused on the register form, the name explains it already, I want the user to be able to register himself inside the program. the record also has the following fields: id, username, password, firstname, lastname. But I couldn't find anything to my liking on the internet. if you want to help me with this quetion, you may use this to start, you can base your code on my code, mentionned above:
Code:
Private Sub btnSignup_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSignup.Click
end sub
Thanks in advance
P.S. Sorry for my bad english, as it is not my native language.
-
Apr 15th, 2014, 05:30 PM
#2
Re: user database
One thing you will want to do, since you are using a datareader, is to get the data from the reader and dispose of the reader as promptly as possible. If you leave the reader open (you appear to want to do other things while the reader is still open in the code you showed), then you won't be able to do much of anything else on that connection because of the existence of the open reader. Therefore, I would suggest that you read the record, move the data to the global variables, close the reader, and then go ahead with forms, messageboxes, and anything else you want to do.
My usual boring signature: Nothing
-
Apr 16th, 2014, 06:02 AM
#3
Thread Starter
New Member
Re: user database
Thank you for your reply, the tip comes in useful, as I completely forgot to close the connection, which may cause certain problems to occur. But I still don't know how to read a record and put them in variables. And that is still the main question in this thread.
You gave me quite some interesting information that I can use in the future.
-
Apr 16th, 2014, 10:14 AM
#4
Re: user database
Closing the connection itself may or may not be necessary. After all, you can re-use the connection, and it may not cause any trouble to leave it open (you can have several simultaneous connections to a single database), but the datareader itself does have to be closed.
All the items returned in a datareader are type object, so you will have to do a bit of casting to turn them into the right types. It would look like this (with plenty of assumptions as to data types on my part):
Code:
If sqlRead.Read() Then
globalID = CInt(sqlRead(0))
globalUserName = sqlRead(1).ToString
'etc...
That may be the quickest way, but it assumes that item 0 is the ID and item 1 is the username, and so on. That can be an invalid assumption, so the alternative (which may or may not be ever so slightly slower, though you'll never notice) is:
Code:
If sqlRead.Read() Then
globalID = CInt(sqlRead("ID"))
globalUserName =sqlRead("UserName").ToString
'etc.
My usual boring signature: Nothing
-
Apr 16th, 2014, 10:22 AM
#5
Re: user database
Actually... SQLDataReader has a set of Get methods for returning specific types... GetString, GetChar, GetInteger, GetDateTime, etc...
http://msdn.microsoft.com/en-us/libr...v=vs.110).aspx
It works by ordinal... but sometimes I shuffle fields around ... so I'd like to reference them by name... fortunately the reader also has a GetOrdinal method as well that returns the orinal position of a field:
Code:
Dim someString = myRdr.GetString(myRdr.GetOrdinal("SomeStringField"))
-tg
-
Apr 16th, 2014, 10:29 AM
#6
Re: user database
Oh, I saw the GetStuffed method and ignored the rest.
I don't like the GetOrdinal method, though, even though it probably inlines to a dictionary lookup, so I prefer the way I am doing it.
My usual boring signature: Nothing
-
Apr 16th, 2014, 10:35 AM
#7
Re: user database
I use it mostly because I can't guarantee someone else won't come along and shuffle up the field order to fit their needs because they're re-using the sproc... But more often than not, I'm using a DT or output params, so I don't get to use a reader very often.
-tg
-
Apr 16th, 2014, 01:46 PM
#8
Thread Starter
New Member
Re: user database
Thank you guys for replying to this thread, I used all the code examples and they all seem to work fine, I also found out how to insert a new record into the database. The login system works now. I will show the code I used for my login form and registration form. If you know an easier way to do it then please let me know, because I an still leaning the language.
registration code:
Code:
Private Sub btnSignup_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSignup.Click
If txtNewPassword.Text <> txtNewPasswordCheck.Text Then
MessageBox.Show("Passwords do not match.", "Registration error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Reset()
ElseIf txtNewUsername.Text = "Username" Or txtNewPassword.Text = "PassWord1" Or txtFirstname.Text = "Firstname" Or txtLastname.Text = "Lastname" Then
MessageBox.Show("Please fill in the required fields.", "Registration error", MessageBoxButtons.OK, MessageBoxIcon.Error)
reset()
Else
Try
Dim conn As New System.Data.OleDb.OleDbConnection()
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Program Files (x86)\Lolzies\GipDB.accdb;Persist Security Info=True;Jet OLEDB:Database Password=********"
conn.Open()
Dim sql As String = "SELECT * FROM users WHERE username='" & txtNewUsername.Text & "'"
Dim sqlCom As New System.Data.OleDb.OleDbCommand(sql)
sqlCom.Connection = conn
Dim sqlRead As System.Data.OleDb.OleDbDataReader = sqlCom.ExecuteReader()
If sqlRead.Read() Then
MessageBox.Show("Username already in use.", "Registration error", MessageBoxButtons.OK, MessageBoxIcon.Error)
reset()
conn.Close()
Else
'hashing the password
Dim strPassword As String
Dim cBase64 As String
Dim objSHA1 As New SHA1CryptoServiceProvider()
Dim abBytesToHash() As Byte
Dim strHashedPassword As String
strPassword = txtNewPassword.Text
cBase64 = Convert.ToBase64String(System.Text.Encoding.UTF8.GetBytes(strPassword))
abBytesToHash = System.Text.Encoding.ASCII.GetBytes(cBase64)
strHashedPassword = BitConverter.ToString(objSHA1.ComputeHash(abBytesToHash))
strHashedPassword = Replace(strHashedPassword, "-", "")
'password is a reserved command, use [] instead
Dim sqlQry As String = "INSERT into users (username, [password], firstname, lastname) VALUES (@username, @password, @firstname, @lastname)"
Using cmd As New System.Data.OleDb.OleDbCommand(sqlQry, conn)
cmd.Parameters.AddWithValue("@username", txtNewUsername.Text)
cmd.Parameters.AddWithValue("@password", strHashedPassword)
cmd.Parameters.AddWithValue("@firstname", txtVoornaam.Text)
cmd.Parameters.AddWithValue("@lastname", txtAchternaam.Text)
cmd.ExecuteNonQuery()
conn.Close()
MessageBox.Show("Regisration succesful.", "Registration", MessageBoxButtons.OK, MessageBoxIcon.Information)
MainLoginForm.Show()
Me.Close()
End Using
End If
Catch ex As Exception
MessageBox.Show(ex.Message, "Fout", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End If
End Sub
Login code:
Code:
Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
If txtUsername.Text = "Username" Or txtPassword.Text = "PassWord1" Then
MessageBox.Show("Please fill in the required fields.", "Login error", MessageBoxButtons.OK, MessageBoxIcon.Error)
CleanUp()
Else
'password hashing
Dim strPssword As String
Dim cBase64 As String
Dim objSHA1 As New SHA1CryptoServiceProvider()
Dim abBytesToHash() As Byte
Dim strHashedPassword As String
strPassword = txtPassword.Text
cBase64 = Convert.ToBase64String(System.Text.Encoding.UTF8.GetBytes(strPassword))
abBytesToHash = System.Text.Encoding.ASCII.GetBytes(cBase64)
strHashedPassword = BitConverter.ToString(objSHA1.ComputeHash(abBytesToHash))
strHashedPassword = Replace(strHashedPassword, "-", "")
Dim conn As New System.Data.OleDb.OleDbConnection()
conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Program Files (x86)\Lolzies\GipDB.accdb;Persist Security Info=True;Jet OLEDB:Database Password=********"
Try
Dim sql As String = "SELECT * FROM users WHERE username='" & txtUsername.Text & "' AND password = '" & strHashedPassword & "'"
Dim sqlCom As New System.Data.OleDb.OleDbCommand(sql)
sqlCom.Connection = conn
conn.Open()
Dim sqlRead As System.Data.OleDb.OleDbDataReader = sqlCom.ExecuteReader()
If sqlRead.Read() Then
GlobalVars.gstrUsername = sqlRead("username").ToString
GlobalVars.gstrvoornaam = sqlRead("firstname").ToString
GlobalVars.gstrachternaam = sqlRead("lastname").ToString
MessageBox.Show(GlobalVars.gstrUsername & " " & GlobalVars.gstrvoornaam & " " & GlobalVars.gstrachternaam & ".", "Authentication", MessageBoxButtons.OK, MessageBoxIcon.Information)
MainScreen.Show()
Me.Close()
Else
MessageBox.Show("Username and password do not match.", "Login Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
CleanUp()
End If
conn.Close()
Catch ex As Exception
MessageBox.Show("Failed to connect to Database. System Error Message: " & ex.Message, "Database Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
CleanUp()
End Try
End If
End Sub
The password hashing is still under development, as it is not very safe. but that is for later on.
Thank you for your help, Shaggy Hiker and Techgnome.
-
Apr 16th, 2014, 02:38 PM
#9
Re: user database
Is this a typo or you have a different variable? on the Dim statement the variable name is strPssword but you use strPassword
What is the purpose of GlobalVars.gstrUsername and the like?
More important than the will to succeed, is the will to prepare for success.
Please rate the posts, your comments are the fuel to keep helping people
-
Apr 16th, 2014, 04:02 PM
#10
Re: user database
One comment that is just for information: You are making use of the Using...End Using construct for the command object. You can do the same for the Connection and wouldn not need to worry about closing or disposing of the connection (though you would still need to open the connection). You can do the same for the datareader, too, which removes any worry about remembering to close the reader, though there is less advantage to using it in that case. Note that even if an exception is thrown, the End Using statement will occur, so the object will be correctly cleaned up.
My usual boring signature: Nothing
-
Apr 16th, 2014, 04:18 PM
#11
Thread Starter
New Member
Re: user database
Originally Posted by kaliman79912
Is this a typo or you have a different variable? on the Dim statement the variable name is strPssword but you use strPassword
What is the purpose of GlobalVars.gstrUsername and the like?
yes, as I see now that is actually a typo, it should be strPassword.
GlobalVars.gstrUsername and such are like normal variables, but created to use them in multiple forms. I use them to personalize the user environment. for example in a chat system. But that is still a work in progress.
-
Apr 16th, 2014, 06:19 PM
#12
Re: [RESOLVED] user database
If GlobalVars is a module then there's no need to use the name. If it's just a Shared class, then the name is necessary. It doesn't hurt anything either way.
My usual boring signature: Nothing
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
|