VS 2010 Extracting information from an access database-VBForums
Results 1 to 22 of 22

Thread: Extracting information from an access database

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Oct 2012
    Posts
    153

    Extracting information from an access database

    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

  2. #2
    PowerPoster kevininstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    5,649

    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

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Oct 2012
    Posts
    153

    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

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Oct 2012
    Posts
    153

    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

  5. #5
    PowerPoster kevininstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    5,649

    Re: Extracting information from an access database

    Please see the attached VS2010 project.
    Attached Files Attached Files

  6. #6
    PowerPoster kevininstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    5,649

    Re: Extracting information from an access database

    Quote Originally Posted by Sean.Downes View Post
    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

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Oct 2012
    Posts
    153

    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.

  8. #8
    PowerPoster kevininstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    5,649

    Re: Extracting information from an access database

    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.

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    Oct 2012
    Posts
    153

    Re: Extracting information from an access database

    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

  10. #10
    PowerPoster kevininstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    5,649

    Re: Extracting information from an access database

    Quote Originally Posted by Sean.Downes View Post
    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.

  11. #11

    Thread Starter
    Addicted Member
    Join Date
    Oct 2012
    Posts
    153

    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

  12. #12
    PowerPoster kevininstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    5,649

    Re: Extracting information from an access database

    Quote Originally Posted by Sean.Downes View Post
    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.

  13. #13

    Thread Starter
    Addicted Member
    Join Date
    Oct 2012
    Posts
    153

    Re: Extracting information from an access database

    Oh right got a bit confused erm, honestly i dont know, all i know its just not letting me do anything with it

  14. #14
    PowerPoster kevininstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    5,649

    Re: Extracting information from an access database

    Quote Originally Posted by Sean.Downes View Post
    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.

  15. #15

    Thread Starter
    Addicted Member
    Join Date
    Oct 2012
    Posts
    153

    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.

    Hope this is less 'Vague'

  16. #16

    Thread Starter
    Addicted Member
    Join Date
    Oct 2012
    Posts
    153

    Re: Extracting information from an access database

    Im guessing ive got to find the provider which is registered to the local computer?
    Last edited by Sean.Downes; Jan 18th, 2013 at 07:39 AM.

  17. #17
    PowerPoster kevininstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    5,649

    Re: Extracting information from an access database

    Quote Originally Posted by Sean.Downes View Post
    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")
        }

  18. #18

    Thread Starter
    Addicted Member
    Join Date
    Oct 2012
    Posts
    153

    Re: Extracting information from an access database

    Ahh okay makes sense I think.

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

  19. #19
    PowerPoster dunfiddlin's Avatar
    Join Date
    Jun 2012
    Posts
    8,241

    Re: Extracting information from an access database

    Quote Originally Posted by Sean.Downes View Post
    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!)
    As the 6-dimensional mathematics professor said to the brain surgeon, "It ain't Rocket Science!"

    Reviews: "dunfiddlin likes his DataTables" - jmcilhinney

    Please be aware that whilst I will read private messages (one day!) I am unlikely to reply to anything that does not contain offers of cash, fame or marriage!

  20. #20
    PowerPoster kevininstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    5,649

    Re: Extracting information from an access database

    Quote Originally Posted by Sean.Downes View Post
    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.

  21. #21

    Thread Starter
    Addicted Member
    Join Date
    Oct 2012
    Posts
    153

    Re: Extracting information from an access database

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

  22. #22

    Thread Starter
    Addicted Member
    Join Date
    Oct 2012
    Posts
    153

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.