Extracting information from an access database

[Sean.Downes]

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

[kevininstructor]

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

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.

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

[Sean.Downes]

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

[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

[kevininstructor]

Please see the attached VS2010 project.

[kevininstructor]

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

[Sean.Downes]

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.

[kevininstructor]

What do you mean it will not let you use the files I sent. The project is a simple VS2010 project which reads from a simple MS-Access 2007 database.

[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

[kevininstructor]

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.

[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

[kevininstructor]

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.

[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

[kevininstructor]

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.

[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'

[Sean.Downes]

Im guessing ive got to find the provider which is registered to the local computer?

[kevininstructor]

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

Private Builder As New OleDb.OleDbConnectionStringBuilder With
    {
        .Provider = "Microsoft.Jet.OLEDB.4.0",
        .DataSource = IO.Path.Combine(Application.StartupPath, "MPS-ODS.mdb")
    }

[Sean.Downes]

Ahh okay makes sense I think.

Im using the new Beta 2013 Access would that be any different?

[dunfiddlin]

Ahh okay makes sense I think.

Im using the new Beta 2013 Access would that be any different?

Desktop or Online? (Not that I've been able to figure out what's required for either but someone might know!)

[kevininstructor]

Ahh okay makes sense I think.

Im using the new Beta 2013 Access would that be any different?

Absolutely, this is what happens when you provide vague details. Our company has a full/complete 20+ subscription to MSDN (all Microsoft products and OS) software for the past 10+ years and I have never even thought about trying beta software.

So was this post nears 20 replies much could have been avoided if you were to had supplied this and other information. In any event I have provided the solution, how you work it out is up to you including the connection string. Good luck.

[Sean.Downes]

Okay, so how will it be different or dont you know that?

[Sean.Downes]

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