hello, ive had help from another user on here which im thankful for, but when ive tried the code which i was given i cant seem to extract the data in need from an access database, if i add the code im using can someone have a look please to see whats wrong with it? The access database is called 'MPS-ODS' and is situated in the main documents folder of my computer. The Database table is called 'Login' thanks!! Im guessing its the connection string and the oledbcommand but i cant figure out what it is i need to do:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'Declare the oledbconnection adn oledbcommand
Dim con As New OleDbConnection("MyConnectionString")
Dim cmd As New OleDbCommand("SELECT username,password FROM login WHERE username = ? AND password = ?", con)
'Set up parameters for the oledbcommand
cmd.Parameters.AddWithValue("username", txt_Username.Text)
cmd.Parameters.AddWithValue("password", txt_Password.Text)
'If the username or password is empty then throw an error
If txt_Username.Text = String.Empty Then
MessageBox.Show("Enter Correct Username", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
ElseIf txt_Password.Text = String.Empty Then
MessageBox.Show("Enter Correct Password.", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
Try
'Open the connection and declare an oledbreader
con.Open()
Dim reader As OleDbDataReader = cmd.ExecuteReader
'If our reader has one or more rows then read those rows and compare the text
If reader.HasRows = True Then
reader.Read()
'If the username and password match then it's a success
If txt_Username.Text = reader.Item("username").ToString And txt_Password.Text = reader.Item("password").ToString Then
UserProfile.Show()
Else
'If they don't match than throw an error
MessageBox.Show("Login Failed." & Environment.NewLine & _
"Enter Correct Username and/or Password", _
Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
End If
End If
Catch ex As Exception
MessageBox.Show(ex.ToString, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
con.Close()
End Try
End If
End Sub
Re: Extracting information from an access database
Okay, here is one method which for this demo I simply created two TextBox controls and seeded them with correct user name and password, result, they are allowed to enter, next I seeded user name with a bad name, not llowed,to enter, same with password. So the idea I am getting at is seed each TextBox then run the query.
Seeding at the start of the procedure
Code:
txt_Username.Text = "KnownName"
txt_Password.Text = "Password for user"
Here is what I would suggest which will work as expected, succeed or fail based on data used in the select statement.
Code:
Private txt_Username As New TextBox With {.Text = "KSG"}
Private txt_Password As New TextBox With {.Text = "pass1"}
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim Builder As New OleDb.OleDbConnectionStringBuilder With
{
.Provider = "Microsoft.ACE.OLEDB.12.0",
.DataSource = IO.Path.Combine(Application.StartupPath, "Database1.accdb")
}
If String.IsNullOrWhiteSpace(txt_Username.Text) Then
MessageBox.Show("Please enter Username", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
End If
If String.IsNullOrWhiteSpace(txt_Password.Text) Then
MessageBox.Show("Please enter Password.", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
Exit Sub
End If
Using con As New OleDbConnection(Builder.ConnectionString)
Using cmd As New OleDbCommand("SELECT username,password FROM login WHERE username = ? AND password = ?", con)
cmd.Parameters.AddWithValue("username", txt_Username.Text.Trim)
cmd.Parameters.AddWithValue("password", txt_Password.Text.Trim)
Try
con.Open()
Dim reader As OleDbDataReader = cmd.ExecuteReader
If Not reader.HasRows Then
MessageBox.Show("Login Failed." & Environment.NewLine & _
"Enter Correct Username and/or Password", _
Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
MessageBox.Show("Allowed to enter")
End If
Catch ex As Exception
MessageBox.Show(ex.ToString, Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Using
End Using
End Sub
Re: Extracting information from an access database
Oh gee i really dont get this :/ haha im happy with the code ive already got as its got error messages suitable for my system etc etc its just the connection string bit that im stuck with and how to entract the data from the database
Re: Extracting information from an access database
Ive tried your code but for some reson its coming up with an error message saying it cant find the source on a local computer. where do i go from here? i am really stuck now :/
thanks
Re: Extracting information from an access database
Originally Posted by Sean.Downes
Ive tried your code but for some reson its coming up with an error message saying it cant find the source on a local computer. where do i go from here? i am really stuck now :/
thanks
When you say "can't find the source on a local computer", that can mean alot, be specific next time please. See my reply prior to this one for a working demo
Re: Extracting information from an access database
Its not letting me use that file you sent me.
And with the error message, it was to do with finding the file on the computer.
This is apparently the location for the database so im guessing this goes in the connection string? Im not to sure.
C:\Users\8\Documents
the database file is called 'MPS-ODS' and the table with the usernames and passwords is called 'Login' with username as the first field and password as the second.
Re: Extracting information from an access database
Originally Posted by Sean.Downes
I have no idea, ive lost the will to live! ha!
The main issue is im stuck on the oledb connection string and the command bit, other than that everything else is fine i think
Either the project after unzipping loads or does not load. If it does not load then Visual Studio will give you a message indicating why it can not load the project. So what are the indicators/messages telling you the project will not load or can you load it and there is other issues. If there are other issues than explain in complete details as I am not a mind-reader.
Re: Extracting information from an access database
Forget the folder etc i just wanna know the code for the connection string and how to entract the data on the access database table etc. it sometthing along the lines of provide = microsoft.oledb.4.0 etc etc the databse location is apparently is here: C:\Users\8\Documents and the database is called 'MPS-ODS' so im guessing its gonna be C:\Users\8\Documents\MPS-ODS and in the table, 'Username' is the first field and 'Password' is the second field so i wanna know how to extract the data in those fields and validate it in my system on the log in screen etc
Re: Extracting information from an access database
Originally Posted by Sean.Downes
Forget the folder etc i just wanna know the code for the connection string and how to entract the data on the access database table etc. it sometthing along the lines of provide = microsoft.oledb.4.0 etc etc the databse location is apparently is here: C:\Users\8\Documents and the database is called 'MPS-ODS' so im guessing its gonna be C:\Users\8\Documents\MPS-ODS and in the table, 'Username' is the first field and 'Password' is the second field so i wanna know how to extract the data in those fields and validate it in my system on the log in screen etc
What folder?????
Sorry but you have not replied to why you can not open the project I uploaded which is a fully working project that will answer your questions.
Re: Extracting information from an access database
Originally Posted by Sean.Downes
Oh right got a bit confused erm, honestly i dont know, all i know its just not letting me do anything with it
If it is not letting you do anything than are you saying when you try and load the project NOTHING HAPPENS ???? I would find that hard to believe. In that event I have nothing else to recommend as you are being completely vague.
Re: Extracting information from an access database
Ok, lets forget the file thing as ive fixed that bit.
Now ive added the code you sent me, the bit with the provide = microsoft.ace etc etc is apparently the duff bit. when i debug the system and press log on once i type the username and password it keeps coming up with an exception error message. i cant copy and paste it, its quite long, it says something along the lines of:
System.InvalidOperationException: The 'Microsoft.ACE.OLEDB.12.0' Provider is not registered on a local computer.
It then carrys on with a load of other words which takes up half the screen, so its a pretty bit error message and i cant type it all.
Re: Extracting information from an access database
Originally Posted by Sean.Downes
Ok, lets forget the file thing as ive fixed that bit.
Now ive added the code you sent me, the bit with the provide = microsoft.ace etc etc is apparently the duff bit. when i debug the system and press log on once i type the username and password it keeps coming up with an exception error message. i cant copy and paste it, its quite long, it says something along the lines of:
System.InvalidOperationException: The 'Microsoft.ACE.OLEDB.12.0' Provider is not registered on a local computer.
It then carrys on with a load of other words which takes up half the screen, so its a pretty bit error message and i cant type it all.
Hope this is less 'Vague'
In regards to a long error message, this can be a) copied into a post or you could take a screen shot of the dialog and upload the image here but first try the instructions below.
The following instructions walks you thru using your database in my project if you don't have ms-access 2007 installed. If you have ms-access 2007 installed and running a 64bit OS then another solution is needed.
This is because you don't have MS-Access 2007 installed and have been using a MDB (pre 2007) file rather than a ACCDB. Take your database and copy it to the Bin\Debug folder of my project, delete my ms-access file. Change Builder variable from what it is now to
Code:
Private Builder As New OleDb.OleDbConnectionStringBuilder With
{
.Provider = "Microsoft.Jet.OLEDB.4.0",
.DataSource = IO.Path.Combine(Application.StartupPath, "MPS-ODS.mdb")
}
Re: Extracting information from an access database
What if then I could use a table thats already in Visual Basic if there is such a thing? that way i wouldnt need an access database i could just run it from visual basic completely