Search ability is too limited in my VB 2008/ SQL Server program

Posted on 2011-10-28
Last Modified: 2012-05-12
Is there any thing I can do to create a search of a database table that wil look into the entire cell instaed of just the first word in the cell? Right now it will search a single column and only the first word in each cell for a perfect match.

   If this question is like asking someone to write a program for me, then I would be happy if you could just point me in the right direction; like a book or video. I got the info for my current seach ability from Beth Massi (blogs.msdn), and I must admit, it works great.  

Here is some VB code in the Windows form.
Private Sub Search_Item_a_descriptionToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Search_Item_a_descriptionToolStripButton.Click
            Me.CustomerOrdersTableAdapter.Search_Item_a_description(Me.OrdersSearchDataSet.CustomerOrders, Item_a_descriptionToolStripTextBox.Text)
        Catch ex As System.Exception
        End Try

    End Sub

This code in from the table adapter:      

SQL Select statement    Bla Bla Bla     And at the end, I put the following:

WHERE        (Item_a_description LIKE @Item_a_description + '%')

         I did this for a number of columns and they all work, but this is limited and combersome.    Thank you for any help.
Question by:jampost
    LVL 9

    Accepted Solution

    Well, I don't know what your adapter function does exactly, but from the SQL code I see there, you have the ability to set it directly.

    So to make the search you need, you have to set the WHERE part as this :
    WHERE Field1 = Value OR Field1 LIKE Value + ' %' OR Field1 LIKE + '% ' + Value OR Field1 LIKE '% ' + Value + ' %' [OR Field2 = Value OR Field2 LIKE Value + ' %' OR Field2 LIKE + '% ' + Value OR Field2 LIKE '% ' + Value + ' %']

    The parenthesis in the WHERE part means optional part which can be duplicated as needed.
    So here, for one field we test if the value is the field's value, is the first word, is the last word or a word in the middle.

    Author Comment


       Thank you, I will test this and get back to you shortly.   --     Jampost

    Author Comment

                       This code did not work, but...  I think your code might work if I have the correct code on the Windows for to correspond with the table adapter code you gave me. Also; where you have Value or Field1  --  should I replace either of those with "Item_a_description" or another column name?

    Author Comment

      Just correcting a typo-------   Windows form

     This code did not work, but...  I think your code might work if I have the correct code on the Windows form to correspond with the table adapter code you gave me. Also; where you have Value or Field1  --  should I replace either of those with "Item_a_description" or another column name?
    LVL 9

    Expert Comment

    Yes, my mistake. I forgot to tell you to replace FieldX and Value by your own variable and fields.
    LVL 50

    Assisted Solution

    just start the like test with a '%' as well to do a wildcard search for the term within your column rather than it starting the column...

    WHERE        (Item_a_description LIKE '%'+@Item_a_description + '%')

    there is no point in declaring all the other possible combinations in the where clause if you are going to do this type of search...

    BE PREPARED for a PERFORMANCE impact however as a '%'+...+'%'  search is not able to use any indexes and so will
    normally result in a full table scan...

    you may wish to consider implementing a FULLTEXT  search capability to your application if you need more sophisticated
    searches , or a more natural language based search (e.g. plurals/singles can be accomodated...)

    look at MSDN for FULLTEXT search and CONTAINS

    LVL 9

    Assisted Solution

    All the other search clauses where useful because the '%' part always contains a space either before or after the pourcentage sign.

    Though, the advice about speed is clearly an important factor to consider.
    LVL 50

    Assisted Solution

    i hadn't noticed the space but thats  not really an issue... if you want to

    search for words/phrases ignoring embedded conditions then there is a whole lot of punctuation to consider.... in addition to a simple space... which again a fulltext search is more suited to assist ...

    Author Closing Comment

    Thank you all for your help. I used bits and pieces of all the comments and it is working well.  Thanks again.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now