We help IT Professionals succeed at work.

SQL LIKE STATEMENT IN VB 2008

Medium Priority
807 Views
Last Modified: 2013-12-25
I have a SQL LIKE statement that works just fine in the query builder of VB2008. However, when I call on it in the VB it doesn't fill the datagridview.

SELECT     Description, ExpDate, ID, Quantity, SubDept, UPC, Unit, [Unit Quantity], [Unit of Measure]
FROM         Inventory
WHERE     (Description LIKE '%' + @Description + '%')
Private Sub TSbtnByDescr_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TSbtnByDescr.Click
        'Executes Search base on sql query - FillByDescription
        Try
            Me.InventoryTableAdapter.FillByDescription(Me.WarehouseDataSet.Inventory, txtDescr.Text)
        Catch ex As Exception
            MsgBox("Search Failed. Let I.S. know!")
        End Try
    End Sub

Open in new window

Comment
Watch Question

Commented:
It most likely is how the Description  value is being used.

I try to debug by placing real values and work from there.

SELECT     Description, ExpDate, ID, Quantity, SubDept, UPC, Unit, [Unit Quantity], [Unit of Measure]
FROM         Inventory
WHERE     (Description LIKE '%' +'  Test  ' + '%')
CERTIFIED EXPERT

Commented:
How is defined the SQL statement in VB? Post the code here.

Author

Commented:
Even with real values it returns a blank line in the datagridview.
CERTIFIED EXPERT
Top Expert 2012

Commented:
And you are using SQL Server 2000 there are columns of data type Text, right?
CERTIFIED EXPERT

Commented:
IScoop, I repeat, poste the code you are using in VB to excute the SQL statement.

We can't hemp if we don't know how the statement is generated and sent to the SQL server.

Author

Commented:
I created the SQL statement:

SELECT     Description, ExpDate, ID, Quantity, SubDept, UPC, Unit, [Unit Quantity], [Unit of Measure]
FROM         Inventory
WHERE     (Description LIKE '%' + @Description + '%')

In the table adapter in VB 2008. I am using SQL Server 2005 Express.

Private Sub TSbtnByDescr_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TSbtnByDescr.Click
        'Executes Search base on sql query - FillByDescription
        Try
            Me.InventoryTableAdapter.FillByDescription(Me.WarehouseDataSet.Inventory, txtDescr.Text)
        Catch ex As Exception
            MsgBox("Search Failed. Let I.S. know!")
        End Try
    End Sub

This is all of the code that I'm aware of that I have modified or written.
CERTIFIED EXPERT
Top Expert 2012

Commented:
Are any of those columns using the deprecated (n)text data type?
IScoop...

I'd try going into Visual Studio and setting a breakpoint on the line that corresponds to line 4 in your original code snippet.  Then check the value of txtDescr.Text.  It may have extra characters in it that are causing your search to fail.  If somehow extra white space is getting passed and becomes part of what you're searching for, it can cause the search to fail.  For example, if the contents of the DESCRIPTION field is "GREEN NOTEBOOKS WITH DOTS" and your WHERE clause is: WHERE (Description LIKE '%' + 'NOTEBOOKS     ' + '%') (notebooks with 5 spaces after it) then the search should fail.  Try trimming white space from your parameter and see if it helps.  Let us know what you find.

Gandalf


CERTIFIED EXPERT
Commented:
Try to print whatever you have in  txtDescr.Text and then copy that exact value and run the select statement with it in the database, see if you get the right values. This is related to what gandalf97 said.

You could trim the variable used in the select statement if the trailing spaces or white chars are the problem like this:


SELECT     Description, ExpDate, ID, Quantity, SubDept, UPC, Unit, [Unit Quantity], [Unit of Measure]
FROM         Inventory
WHERE     (Description LIKE '%' + ltrim(rtrim(@Description)) + '%')

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
To elaborate a little...  I suggested debug/breakpoints because whitespace is hard to find by eye.  If you use a simple single word example like "NOTEBOOK" and get a string length of anything other than 8, then you have a problem.

If you need help using debug/setting breakpoints, just ask and I'll try to help.

Gandalf

Author

Commented:
I took out all spaces in the SQL statement:

SELECT     Description, ExpDate, ID, Quantity, SubDept, UPC, Unit, [Unit Quantity], [Unit of Measure]
FROM         Inventory
WHERE     (Description LIKE '%'+@Description+'%')

none of the columns are (n)text.

I'm sorry I don't understand what you mean by "set a break point"

It works just fine in the query builder when I test it there. It just doesn't work in practice.

When I put something in the SQL statement WHERE (Description LIKE '%star%' it worked.
CERTIFIED EXPERT

Commented:
<<I took out all spaces in the SQL statement:>>

That doesn't matter. The spaces between the elements of a SQL statement are irrelevant unless they are part of a string value. Use the SELECT statement I posted above with ltrim(rtrim...))



<<When I put something in the SQL statement WHERE (Description LIKE '%star%' it worked.>>

That's not the point. If you typed "star" in the select statement worked but what I say is to grab the value that is passed by the VB code in the txtDescr.Text control when executing the select. It ios possible that for some reason in that text box you don't have just star but something like star* where * is a non printable character, garbage.

Author

Commented:
The ltrim(rtrim worked very well. thank you.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.