SQL LIKE STATEMENT IN VB 2008

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  ' + '%')

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

Even with real values it returns a blank line in the datagridview.

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

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.

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.

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

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

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.

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

The ltrim(rtrim worked very well. thank you.