How to Show my My Queried Data using OleDbDataReader vb

Question

I know this a walk in the park for most of you on this site, but for a n00b like me it is giving me a little trouble now, all i'm trying to do is program my "Query" button to show/search data from my access file based on a string entered in a corresponding textbox. For example if your trying to search the access db for all employees named Eric. I want to be able to type Eric in my FirstName txtbox and show a list of all employees named Eric within my access file. So far I have some code that runs without error but when I click the button whatever is in my textbox disappears. I positive im missing something and just need some guidance. here is my code so far.Doing this in VB so please HELP!!!

Using con = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Eric\Documents\Fadv.accdb")
        Dim SQL As String = ("SELECT FirstName, LastName FROM info")

        Dim cmd As New OleDbCommand(SQL, con)
        con.Open()

        Dim reader As OleDbDataReader
        reader = cmd.ExecuteReader()

        While reader.Read()
            txtFirstName.Text = reader(0).ToString()

            Console.WriteLine(" {0} = {1}", reader("FirstName"), 
                reader("LastName"))

            txtFirstName.Text = reader(0).ToString()

        End While
        reader.Close()

End Using

your SQL needs a WHERE clause if your want to restrict the return. you also need something else for the output like a ListBox or DataGridView that will show more than one record, otherwise you just write over the last value as you loop thru — Ňɏssa Pøngjǣrdenlarp, May 30 '14 at 22:55

score 2 · Accepted Answer · edited May 23 '17 at 12:25

Using con = New OleDbConnection("Provider= blah blah blah...")
    Dim SQL As String = "SELECT FirstName, LastName FROM info WHERE FirstName = ?"

    ' OleDbCommand supports Dispose
    Using cmd As New OleDbCommand(SQL, con)
        cmd.Parameters.AddWithValue("@p1", txtWhere.Text)

        con.Open()

        Dim reader As OleDbDataReader
        reader = cmd.ExecuteReader()

        ' a listbox to store more than one result
        lstResults.Items.Clear          ' clear old stuff

        While reader.Read()
            lstResults.Items.Add(String.Format("{0}, {1}",
                 reader.Item(1).ToString, reader.Item(0).ToString())

            Console.WriteLine(" {0} = {1}", reader("FirstName"), 
                reader("LastName"))
        End While
        reader.Close()
   End Using

End Using

The SQL can be modified to search for names LIKE, so that you can find Eric, Erin and Erica all at once. You can also use other data access methods to simple fire the query and bind some controls like a DataGridView to the results.

Finally, Parameters protect you from a visit from Little Bobby Tables. Learn them, know them, love them.

plutonix once again very helpful I will let you know of my progress. — Playergoodi, May 31 '14 at 00:00

How to Show my My Queried Data using OleDbDataReader vb

1 Answers1