Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 2010 > BOOK: Visual Basic 2010 Programmer's Reference
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old January 8th, 2015, 02:27 PM
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
  #2 (permalink)  
Old January 19th, 2015, 06:11 PM
Rod Stephens's Avatar
Wrox Author
Points: 3,141, Level: 23
Points: 3,141, Level: 23 Points: 3,141, Level: 23 Points: 3,141, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: , , .
Posts: 640
Thanks: 2
Thanked 96 Times in 95 Posts
Default

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.
__________________
Rod

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)
Reply With Quote
The Following User Says Thank You to Rod Stephens For This Useful Post:
bobwhite (January 20th, 2015)
  #3 (permalink)  
Old January 20th, 2015, 12:35 PM
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

Simple but elegant. Thank you so much.
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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



All times are GMT -4. The time now is 06:30 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.