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.