Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Visual Basic > VB 2010 > BOOK: Visual Basic 2010 Programmer's Reference
BOOK: Visual Basic 2010 Programmer's Reference
This is the forum to discuss the Wrox book Visual Basic 2010 Programmer's Reference by Rod Stephens; ISBN: 9780470499832
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Visual Basic 2010 Programmer's Reference section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old January 8th, 2015, 03:27 PM
Registered User
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.

        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
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 05:13 PM..
Old January 19th, 2015, 07:11 PM
Rod Stephens's Avatar
Wrox Author
Join Date: Jan 2006
Posts: 647
Thanks: 2
Thanked 96 Times in 95 Posts

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:

While dr.Read()
    String result = dr("CD").ToString() & _
        dr("ARTISTS").ToString() & _
        dr("RECORDED").ToString() & _
        dr("TUNE").ToString() & _
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.

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)
The Following User Says Thank You to Rod Stephens For This Useful Post:
bobwhite (January 20th, 2015)
Old January 20th, 2015, 01:35 PM
Registered User
Join Date: Jan 2015
Posts: 2
Thanks: 1
Thanked 0 Times in 0 Posts

Simple but elegant. Thank you so much.

Similar Threads
Thread Thread Starter Forum Replies Last Post
XLST transform xml multiple query and display results in html table tatetiffany XSLT 0 August 13th, 2014 05:17 PM
Display query results in Combobox2 based on the selected value of Combobox1 harinarayananvp Classic ASP Databases 0 October 22nd, 2011 07:50 AM
Display Crosstab Query Results - HELP? timmaher Classic ASP Databases 0 August 25th, 2005 09:47 AM
Trying to send SQL results to a single variable NathanielMc VB Databases Basics 9 June 3rd, 2004 02:36 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.