?
Solved

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

Posted on 2011-10-28
9
Medium Priority
?
273 Views
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
        Try
            Me.CustomerOrdersTableAdapter.Search_Item_a_description(Me.OrdersSearchDataSet.CustomerOrders, Item_a_descriptionToolStripTextBox.Text)
        Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        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.
0
Comment
Question by:jampost
  • 4
  • 3
  • 2
9 Comments
 
LVL 9

Accepted Solution

by:
djon2003 earned 1000 total points
ID: 37048586
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.
0
 

Author Comment

by:jampost
ID: 37048598
djon2003

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

Author Comment

by:jampost
ID: 37048755
                   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?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:jampost
ID: 37048759
  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?
0
 
LVL 9

Expert Comment

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

Assisted Solution

by:Lowfatspread
Lowfatspread earned 1000 total points
ID: 37049079
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

hth
0
 
LVL 9

Assisted Solution

by:djon2003
djon2003 earned 1000 total points
ID: 37049129
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.
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 1000 total points
ID: 37049392
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 ...
0
 

Author Closing Comment

by:jampost
ID: 37049554
Thank you all for your help. I used bits and pieces of all the comments and it is working well.  Thanks again.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.
Screencast - Getting to Know the Pipeline
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

850 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