[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL LIKE STATEMENT IN VB 2008

Posted on 2009-02-19
13
Medium Priority
?
796 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

0
Comment
Question by:IScoop
  • 4
  • 4
  • 2
  • +2
13 Comments
 
LVL 14

Expert Comment

by:Emes
ID: 23684547
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
 
LVL 27

Expert Comment

by:Zberteoc
ID: 23684694
How is defined the SQL statement in VB? Post the code here.
0
 

Author Comment

by:IScoop
ID: 23704695
Even with real values it returns a blank line in the datagridview.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 23705067
And you are using SQL Server 2000 there are columns of data type Text, right?
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 23705106
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
 

Author Comment

by:IScoop
ID: 23705556
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 23706284
Are any of those columns using the deprecated (n)text data type?
0
 
LVL 2

Expert Comment

by:gandalf97
ID: 23710870
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
 
LVL 27

Accepted Solution

by:
Zberteoc earned 1500 total points
ID: 23712249
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
 
LVL 2

Expert Comment

by:gandalf97
ID: 23712600
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
 

Author Comment

by:IScoop
ID: 23713861
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
 
LVL 27

Expert Comment

by:Zberteoc
ID: 23714194
<<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
 

Author Closing Comment

by:IScoop
ID: 31548831
The ltrim(rtrim worked very well. thank you.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

867 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