View Single Post
  #1 (permalink)  
Old January 8th, 2015, 02:27 PM
bobwhite bobwhite is offline
Registered User
Points: 8, Level: 1
Points: 8, Level: 1 Points: 8, Level: 1 Points: 8, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2015
Posts: 2
Thanks: 1
Thanked 0 Times in 0 Posts
Default Display Results of SQL Query Using WHERE CONTAINS [variable].

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.

Last edited by bobwhite; January 12th, 2015 at 04:13 PM.
Reply With Quote