Display Results of SQL Query Using WHERE CONTAINS [variable].

bobwhite · January 8th, 2015, 03:27 PM

I'm surprised that no one has any ideas about this. It seems a perfectly reasonable thing that someone might want to do. What good is database system access using Visual Basic if a user cannot enter a key word (rather than having to know the complete text) to do a search?

I want the user to be able to enter a term in a textbox and have the code search an Access Database using the term as a key word, that is, with the SELECT sql phrase using "WHERE [column name] CONTAINS [variable]. Then the results should be displayed either in a datagrid a combox or even just in another text box. None of the examples in Chapter 20 seem to accomplish that.

I tried an example I found on the Web which the author claims will work (#1) below. It will do the search but simply cycles through the database, displaying just the final record, not the item searched. I can see why this is happening from the WHILE END WHILE. I've tried to get the results to display in a datagrid as per the text examples or in a combo box, and I get an error saying the it is missing a needed item. I suspect this is because the examples use a preset condition in the search process.

[1]

Code:

myConnection.Open()
        SearchString = txtSearchTerm.Text
        Dim str As String = "SELECT * FROM FiddleList WHERE (TUNE CONTAINS '" & SearchString & "')"
        Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
        dr = cmd.ExecuteReader
       While dr.Read()
            txtCD.Text = dr("CD").ToString
            txtARTISTS.Text = dr("ARTISTS").ToString
            txtYEAR.Text = dr("RECORDED").ToString
            txtTUNE.Text = dr("TUNE").ToString
            txtID.Text = dr("ID").ToString
        End While
        myConnection.Close()

The connection to the database works because I can display the entire list in a datagrid view programmatically. I cannot simply create a Query in Access because the people who want this utility do not have Microsoft Access.

Can anyone provide any suggestions? I have been using BASIC for simple things since the original in 1982, but have only done a few complex programs in Visual Basic 2010. Thank you.

Rod Stephens · January 19th, 2015, 07:11 PM

Sorry for the slow reply.

It looks like that code loops through the records and displays them one at a time in the TextBoxes. It all happens too fast to see and you only see the last values they contain from the final record.

One solution would be to add the results to a ListBox or ComboBox. Suppose you have a ListBox named lstResults. Then the key piece of code might look something like this:

Code:

lstResults.Items.Clear()
While dr.Read()
    String result = dr("CD").ToString() & _
        dr("ARTISTS").ToString() & _
        dr("RECORDED").ToString() & _
        dr("TUNE").ToString() & _
        dr("ID").ToString()
    lstResults.Items.Add(result)
End While

The same would work for a ComboBox.

You could also move the values into a DataGrid or other control one at a time, but it would be easier to use LINQ To SQL to load the data into a DataTable and then set the DataGrid's DataSource property to the DataTable. (If I'm remembering correctly.)

Give one of these ideas a try and if it doesn't work email me (RodStephens@vb-helper.com) and I'll help you out.

bobwhite · January 20th, 2015, 01:35 PM

Simple but elegant. Thank you so much.