SQL LIKE STATEMENT IN VB 2008

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

IScoopAsked:
Who is Participating?
 
ZberteocConnect With a Mentor 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

0
 
EmesCommented:
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  ' + '%')
0
 
ZberteocCommented:
How is defined the SQL statement in VB? Post the code here.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
IScoopAuthor Commented:
Even with real values it returns a blank line in the datagridview.
0
 
Anthony PerkinsCommented:
And you are using SQL Server 2000 there are columns of data type Text, right?
0
 
ZberteocCommented:
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.
0
 
IScoopAuthor 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.
0
 
Anthony PerkinsCommented:
Are any of those columns using the deprecated (n)text data type?
0
 
gandalf97Commented:
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


0
 
gandalf97Commented:
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
0
 
IScoopAuthor 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.
0
 
ZberteocCommented:
<<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.
0
 
IScoopAuthor Commented:
The ltrim(rtrim worked very well. thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.