SQL search query to Access DB?

Question

I'm attempting to query an Access database using a search button from a textbox and insert the results into a listbox. Here's the code I have so far:

    Dim con As New OleDbConnection(DBcon)
    Try
        lbresults.Items.Clear()
        Dim dr As OleDbDataReader
        Dim command As New OleDbCommand("Select I.InstName, S.StuName FROM Instructor I, Student S WHERE I.InstName Like '%" & txtsearch.Text & "%' and S.StuName like '%" & txtsearch.Text & "%'", con)

        con.Open()

        command.Connection = con
        dr = command.ExecuteReader

        While dr.Read()
            lbresults.Items.Add(dr("InstName"))
            lbresults.Items.Add(dr("StuName"))
        End While

    Catch ex As Exception

The problem I'm having is it's returning the both InstName and the StuName multiple times in the listbox. I'm guessing it's because I'm doing the items.add twice? I was trying to use "[oledbcommand variable name].parameters.addwithvalue" but I couldn't figure out how to do it with a "like" function.

Icarus · Accepted Answer · 2011-09-15 00:34:01Z

1

If it's adding the InstName and StuName multiple times to the dropdownlist is probably because the query is returning the records multiple times since you are doing a select ... where ... like...

Try changing your select statement to (notice the word DISTINCT):

Dim command As New OleDbCommand("Select DISTINCT I.InstName, S.StuName FROM Instructor I, Student S WHERE I.InstName Like '%" & txtsearch.Text & "%' and S.StuName like '%" & txtsearch.Text & "%'", con)

answered Sep 15, 2011 at 0:34

Icarus

64k14 gold badges102 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bo Persson · Accepted Answer · 2013-01-07 19:35:21Z

0

Your query is relating two tables without specifying the relationship.

There are multiple ways to skin a cat but I would do this to guarantee that I'm controlling the outcome:

dim SQL as new string = _
    "Select 'Instructor' as NameType, I.InstName as NameValue " _
    "from Instructor I " _
    "where I.InstName Like '%" & txtsearch.Text & "%' " & _
    "union " & _
    "Select 'Student' as NameType, S.StuName as NameValue " _
    "from Student  S " _
    "where S.StuName Like '%" & txtsearch.Text & "%' " 

Dim command As New OleDbCommand(SQL, con)

Using union versus union all returns distinct records across the dataset with instructor being the prevailing table in case of duplicate names.

edited Jan 7, 2013 at 19:35

Bo Persson

92.9k31 gold badges153 silver badges211 bronze badges

answered Jan 7, 2013 at 17:55

Donny McCoy

1307 bronze badges

Collectives™ on Stack Overflow

SQL search query to Access DB?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related