Solved

How to use LIKE with a Parameter in VB.NET SqlCommand

Posted on 2010-09-20
48
547 Views
Last Modified: 2012-06-27
I can't figure out the correct syntax for the code below.  In the SqlCommand where it says, "WHERE Description LIKE '% + @SearchNAIC + %' " the statement works if I replace it with "... LIKE '%Leather%'..., But the existing format does not work.  

I've tried, LIKE '%@SearhNAIC%' and I've tried just, LIKE @SearchNAIC

I have a TextBox, txtSearch, that I'm trying to use as a "Search" feature or filter to set the parameter for the Select statement, but I can't get any results.  

The table has, like 21,000 records, so I'm trying to filter it down with the "search" box before it goes to a DropDownList.
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        ' If Not IsPostBack Then

        Dim cmd As New SqlCommand("SELECT Description, Code FROM NAICS WHERE Description LIKE '% + @SearchNAIC + %' ORDER BY Description ASC", New SqlConnection(ConfigurationManager.ConnectionStrings("OJTConnectionString").ConnectionString))

        cmd.Parameters.Add("@SearchNAIC", SqlDbType.VarChar).Value = txtSearch.Text



        cmd.Connection.Open()



        Dim drNAIC As SqlDataReader

        drNAIC = cmd.ExecuteReader()



        ddlNAICS.DataSource = drNAIC

        ddlNAICS.DataTextField = "Description"

        ddlNAICS.DataValueField = "Code"

        ddlNAICS.DataBind()



        cmd.Connection.Close()

        cmd.Connection.Dispose()

End Sub

Open in new window

0
Comment
Question by:megnin
  • 27
  • 16
  • 5
48 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 33718121
I thought the parameters was for use with stored procedures. I could be mistaken but thats the only time I used it.

Why dont you just build the sql string and do it that way

        Dim sSql as string
        sSql = "SELECT Description, Code FROM NAICS WHERE Description LIKE '% + txtSearch.Text + %' ORDER BY Description ASC"

then use sSql in your sqlCommand instead of your hardcoded string
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 33718131
Hi
Try changing
 Dim cmd As New SqlCommand("SELECT Description, Code FROM NAICS WHERE Description LIKE '% + @SearchNAIC + %' ORDER BY Description ASC", New SqlConnection(ConfigurationManager.ConnectionStrings("OJTConnectionString").ConnectionString))
to
 Dim cmd As New SqlCommand("SELECT Description, Code FROM NAICS WHERE Description LIKE '%?%' ORDER BY Description ASC", New SqlConnection(ConfigurationManager.ConnectionStrings("OJTConnectionString").ConnectionString))
you specify ? for each parameter and you add them in order
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 33718146
Sorry you also should change the
cmd.Parameters.Add("@SearchNAIC", SqlDbType.VarChar).Value = txtSearch.Text

to
cmd.Parameters.Add("@p1", SqlDbType.VarChar).Value = txtSearch.Text

Thats what I do
0
 
LVL 1

Author Comment

by:megnin
ID: 33718156
Thanks.  I'll try both suggestions.
I did change it to
LIKE '%' + @SearchNAIC + '%'
and it worked exactly one time.  After repeated attempts I have not been able to get it to work a second time.  I tried using "If Not ISPostBack" and commenting that out and neither has worked a second time.  The DropDownList just listed the entire table now.
0
 
LVL 1

Author Comment

by:megnin
ID: 33718173
What's the difference between "@SearchNAIC" and "@p1" other than it's shorter?
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 33718219
Not entirely sure, all i know what I use and it works :)
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 33718237
Here is an example of it used in one of my apps, not complete but it shows what I mean
       Dim command As New OleDbCommand()

        command.Connection = conn

        command.CommandText = "INSERT INTO TblQuotes (QuoteNumber,gmAccountNo,Company,Movement,TransportMode,ServiceLevel,ShipTerms,CustomerRef,Accepted,Weight,ChargeWeight,Volume,LoadingMetres,NoPieces, " _

        & "Insurance,InsureValue,Quotedate,CreatedBy, InvName, InvAddr1, InvAddr2, InvTown, InvCounty, InvPostcode, CollName, CollAddr1, CollAddr2, CollTown, CollCounty, CollPostcode, DelName, DelAddr1, DelAddr2, DelTown, DelCounty, DelPostcode,Dimensions,IsAgent,Premium,CollCountry,DelCountry, invcountry, specialinstructions,ContactName)  " _

        & "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"



        'Set up the parameters

        command.Parameters.Add("@p1", OleDbType.VarChar, 10).Value = quote.QuoteNumber

        command.Parameters.Add("@p2", OleDbType.VarChar, 20).Value = quote.GMAccountNo

        command.Parameters.Add("@p3", OleDbType.VarChar, 40).Value = quote.Company

        command.Parameters.Add("@p4", OleDbType.Char, 1).Value = quote.Movement

        command.Parameters.Add("@p5", OleDbType.Char, 4).Value = quote.TransportMode

        command.Parameters.Add("@p6", OleDbType.Char, 4).Value = quote.ServiceLevel

        command.Parameters.Add("@p7", OleDbType.Char, 4).Value = quote.ShipTerms

        command.Parameters.Add("@p8", OleDbType.Char, 20).Value = quote.CustomerRef

Open in new window

0
 
LVL 1

Author Comment

by:megnin
ID: 33718264
rockiroads, I modified your suggestion just a bit to this:

Dim sSql As String = "SELECT Description, Code FROM NAICS WHERE Description LIKE '%" & txtSearch.Text & "%' ORDER BY Description ASC"
Dim cmd As New SqlCommand(sSql, New SqlConnection(ConfigurationManager.ConnectionStrings("OJTConnectionString").ConnectionString))
so it can see the TextBox.Text value.  I'm going to try it now.  The reason I'm trying to filter this is that the table the DropDownBox is using to populate is about 21,000 records and it takes 5 minute for it to read using a DataReader.  I'm trying to get the items into the ddl faster.  It's turning into a monster problem.  ;-)
0
 
LVL 1

Author Comment

by:megnin
ID: 33718274
GMGenius, oh, I see.  I'll give it a shot, too.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33718283
>> What's the difference between "@SearchNAIC" and "@p1" other than it's shorter?

didnt think there is a difference, its just a key name for which you assign a value later. Longer name might be slightly better as it makes things more readable. If using a short name then recommend you add a comment indicating the parameter.

Whatever filtering you do, to speed things up, make sure the fields are indexed.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33718293
The one difference I see between using OleDb and Sql types is jet. MSaccess works on Jet so you have to use OleDb on that. Using Sql... will not work.
0
 
LVL 1

Author Comment

by:megnin
ID: 33718294
Ahhhhhh.  I hadn't thought about indexing the fields.  The table only contains "Description" and "Code".  Would I index both columns?
0
 
LVL 1

Author Comment

by:megnin
ID: 33718301
rocki, I'm using SQL Server 2005.
0
 
LVL 1

Author Comment

by:megnin
ID: 33718336
That table is set to "Indexable = Yes" but "Is Full-text Indexed = No" Should I set "Full-text Specification" to Yes?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33718345
I saw that you was using, I mentioned the difference cos I saw posted OleDb. Hopefully it makes a difference for you but I was not 100% sure it would.

If you are going to be searching on description then yes, a duplicate index would be required to help speed up the search.
0
 
LVL 1

Author Comment

by:megnin
ID: 33718351
Oh, I'm using SQL Server Management Studio to work with the database definition.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33718368
>> That table is set to "Indexable = Yes" but "Is Full-text Indexed = No" Should I set "Full-text Specification" to Yes?

I reckon so, give it a try to see how much it speed things up
0
 
LVL 1

Author Comment

by:megnin
ID: 33718373
Hmmm, it won't let me turn on the Full-text Indexing.   It looks like it wants Is Identity turned on first.
The problem with this table is that the "Code" column does not contain unique values.  Many of the Descriptions have the same Code.  I would need to and another column for an Identity column.
0
 
LVL 1

Author Comment

by:megnin
ID: 33718384
Maybe I could use both columns as the identity?  Can I do that and would that have an adverse affect on the performance?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33718412
Whats wrong with just creating a new identity column? it helps uniquely identify a row. Easier on updates etc.
0
 
LVL 1

Author Comment

by:megnin
ID: 33718432
I've never created an Identity column "after the fact" on a fully populated table.  How do I populate the new ID column with values?
0
 
LVL 1

Author Comment

by:megnin
ID: 33718455
Oh, never mind.  I set it to Identity and it just worked.  Filled 'em up with ID's.  ;-)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33718496
yes, identity columns are handled automatically by sql server. It is always good practice to have this kind of primary key in your tables.

0
 
LVL 1

Author Comment

by:megnin
ID: 33718501
When I right-click on the table (in SSMS) I see "Full-Text index >" but it's greyed out.  It's not selectable.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 65

Expert Comment

by:rockiroads
ID: 33718624
seems like service is not running? http://support.microsoft.com/?id=270671

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33718642
0
 
LVL 1

Author Comment

by:megnin
ID: 33718648
The SQL filter is still not working either.  Should I just post the entire code?  The .vb is only 25 lines or so.
Then there's the textbox, button and ddl:

<asp:TextBox ID="txtSearch" runat="server" AutoPostBack="True"></asp:TextBox>
<asp:Button ID="btnSearch" runat="server" Text="Search" />
<asp:DropDownList ID="ddlNAICS"
runat="server"
AutoPostBack="True"
Width="98%"
AppendDataBoundItems="True">
<asp:ListItem Value="0" Text="Select One..." />
</asp:DropDownList>
I'm not sure if it should be in "IsNotPostback" or not either.

Imports System.Data

Imports System.Data.SqlClient

Imports System.Web.UI.WebControls



Partial Public Class _Default

    Inherits System.Web.UI.Page



    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        If Not IsPostBack Then

            Dim sSql As String = "SELECT Description, Code FROM NAICS WHERE Description LIKE '%" & txtSearch.Text & "%' ORDER BY Description ASC"

            Dim cmd As New SqlCommand(sSql, New SqlConnection(ConfigurationManager.ConnectionStrings("OJTConnectionString").ConnectionString))



            cmd.Connection.Open()



            Dim drNAIC As SqlDataReader

            drNAIC = cmd.ExecuteReader()



            ddlNAICS.DataSource = drNAIC

            ddlNAICS.DataTextField = "Description"

            ddlNAICS.DataValueField = "Code"

            ddlNAICS.DataBind()



            cmd.Connection.Close()

            cmd.Connection.Dispose()



        End If

    End Sub



    Protected Sub ddlNAICS_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlNAICS.SelectedIndexChanged

        lblNAICSCode.Text = ddlNAICS.SelectedValue.ToString

    End Sub



    Protected Sub SubmitButton_Click(ByVal sender As Object, ByVal e As EventArgs) Handles SubmitButton.Click



    End Sub



    Protected Sub CancelButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles CancelButton.Click



    End Sub

End Class

Open in new window

0
 
LVL 1

Author Comment

by:megnin
ID: 33718652
going to lunch be back in an hour....
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33718709
bring us a BLT please on brown bread, thanks!
0
 
LVL 1

Author Comment

by:megnin
ID: 33719331
I just thought of something over Thai, that table never changes.  Would it be a hundred times faster if I just exported the data to, say, an XML file and used that as the datasource for the DropDownList?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33719418
when u said the sql filter didnt work, what happened?
0
 
LVL 1

Author Comment

by:megnin
ID: 33719484
It just gave me the entire table.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33719708
Can u look at the generated sql and see what it looks like?

Dim sSql As String = "SELECT Description, Code FROM NAICS WHERE Description LIKE '%" & txtSearch.Text & "%' ORDER BY Description ASC"
           

Debug.print ssql  

then look in the output/immediate window

it could be txtSearch.text didnt show up with any text
0
 
LVL 1

Author Comment

by:megnin
ID: 33720563
Oh, I tried several text strings to search on.  
Since the table never changes, I'm thinking of exporting it to an XML file and using that as the datasource for the DropDownLlist.
Here is an example of the XML file I was able to generate from the SQL table.  If I put the file right on the web server, wouldn't that be much, much faster?

<root>



<dbo.NAICS><DESCRIPTION>Abattoirs</DESCRIPTION><code>311611</code></dbo.NAICS>

<dbo.NAICS><DESCRIPTION>Abortion clinics</DESCRIPTION><code>621410</code></dbo.NAICS>

<dbo.NAICS><DESCRIPTION>Abrasion testing machines manufacturing</DESCRIPTION><code>334519</code></dbo.NAICS>

<dbo.NAICS><DESCRIPTION>Abrasive points, wheels, and disks, dental, manufacturing</DESCRIPTION><code>339114</code></dbo.NAICS>

<dbo.NAICS><DESCRIPTION>Abrasive products manufacturing</DESCRIPTION><code>327910</code></dbo.NAICS>

<dbo.NAICS><DESCRIPTION>Abrasive sand quarrying and/or beneficiating</DESCRIPTION><code>212322</code></dbo.NAICS>

<dbo.NAICS><DESCRIPTION>Abrasive stones (e.g., emery, grindstones, hones, pumice) mining and/or beneficiating</DESCRIPTION><code>212399</code></dbo.NAICS>

<dbo.NAICS><DESCRIPTION>Abrasives merchant wholesalers</DESCRIPTION><code>423840</code></dbo.NAICS>

<dbo.NAICS><DESCRIPTION>Abrasives, natural, mining and/or beneficiating</DESCRIPTION><code>212399</code></dbo.NAICS>

<dbo.NAICS><DESCRIPTION>Absorbent paper stock manufacturing</DESCRIPTION><code>322121</code></dbo.NAICS>

<dbo.NAICS><DESCRIPTION>Absorbers, gas, heavy gauge metal, manufacturing</DESCRIPTION><code>332420</code></dbo.NAICS>

<dbo.NAICS><DESCRIPTION>Absorption analyzers, industrial process type (e.g., infrared), manufacturing</DESCRIPTION><code>334513</code></dbo.NAICS>

Open in new window

0
 
LVL 1

Author Comment

by:megnin
ID: 33720831

Okay, with a slight modification to the xml file and using the Page_Load below, it went from taking two minutes to about 30 seconds for the page to refresh and populate the DropDownList.  Still unacceptably slow AND it still puts the first records with the same code as the item I selected in the ddl.  When I select "Zoos", after the page refreshes the ddl contains "Animal exhibits" not "Zoos" because "Animal exhibits" has the same code as "Zoos" and it see's Animal exhibits first.  I still don't know how to prevent that.
 
Protected Sub Page_Load()
If Not IsPostBack Then
  Dim ds As New DataSet
  ds.ReadXml(MapPath("NAICS.xml"))
  ddlNAICS.DataSource = ds 'drNAIC
  ddlNAICS.DataTextField = "Description"
  ddlNAICS.DataValueField = "Code"
  ddlNAICS.DataBind()  
End If
End Sub
<?xml version="1.0" encoding="ISO-8859-1"?>

<root>

<item><DESCRIPTION>Abattoirs</DESCRIPTION><code>311611</code></item>

<item><DESCRIPTION>Abortion clinics</DESCRIPTION><code>621410</code></item>

<item><DESCRIPTION>Abrasion testing machines manufacturing</DESCRIPTION><code>334519</code></item>

<item><DESCRIPTION>Abrasive points, wheels, and disks, dental, manufacturing</DESCRIPTION><code>339114</code></item>

<item><DESCRIPTION>Abrasive products manufacturing</DESCRIPTION><code>327910</code></item>

<item><DESCRIPTION>Abrasive sand quarrying and/or beneficiating</DESCRIPTION><code>212322</code></item>

<item><DESCRIPTION>Abrasive stones (e.g., emery, grindstones, hones, pumice) mining and/or beneficiating</DESCRIPTION><code>212399</code></item>

<item><DESCRIPTION>Abrasives merchant wholesalers</DESCRIPTION><code>423840</code></item>

<item><DESCRIPTION>Abrasives, natural, mining and/or beneficiating</DESCRIPTION><code>212399</code></item>

<item><DESCRIPTION>Absorbent paper stock manufacturing</DESCRIPTION><code>322121</code></item>

<item><DESCRIPTION>Absorbers, gas, heavy gauge metal, manufacturing</DESCRIPTION><code>332420</code></item>

<item><DESCRIPTION>Absorption analyzers, industrial process type (e.g., infrared), manufacturing</DESCRIPTION><code>334513</code></item>

Open in new window

0
 
LVL 12

Expert Comment

by:GMGenius
ID: 33720832
If txtSearch.text was empty you would get the entire table
0
 
LVL 1

Author Comment

by:megnin
ID: 33720860
I know.  That's why I put a string to search for and thus filter the list down into txtSearch.text.  I would use something like "leather" or "zoo".  Both of which I know have records that it will match.  
I've got the ddl using the DataSet from the XML file now, so I'll need to come up with a different way to filter it now anyway.  Any idea on how to do the same thing with the XML file I was trying to do with the SQL table?
0
 
LVL 1

Author Comment

by:megnin
ID: 33720992
My head hurts and my ddl still doesn't work after working on it for 9 hours.  I'm going to go home and drink a lot.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33721016
sounds like a plan - heinekin m8
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 33722158
Any reason why your code is on the page load event? better to be on the button search I would of though
or if u want to call it in more than one place, create the code in its own method then call it from the places you want


    Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSearch.Click

            Dim sSql As String = "SELECT Description, Code FROM NAICS WHERE Description LIKE '%" & txtSearch.Text & "%' ORDER BY Description ASC"
            Dim cmd As New SqlCommand(sSql, New SqlConnection(ConfigurationManager.ConnectionStrings("OJTConnectionString").ConnectionString))

            cmd.Connection.Open()

            Dim drNAIC As SqlDataReader
            drNAIC = cmd.ExecuteReader()

            ddlNAICS.DataSource = drNAIC
            ddlNAICS.DataTextField = "Description"
            ddlNAICS.DataValueField = "Code"
            ddlNAICS.DataBind()

            cmd.Connection.Close()
            cmd.Connection.Dispose()


    End Sub
0
 
LVL 1

Author Comment

by:megnin
ID: 33724333
heinekin? After yesterday it was more like Aberlour.  ;-)
0
 
LVL 1

Author Comment

by:megnin
ID: 33724375
Yes, it does make more sense to have the code in a button event with the search.  I didn't originally have a search of filter feature in it, so I just wanted the ddl to be populated when the page loaded.  I will move it to a button due to the long load time and to better handle any search or filtering.
If I use the XML file rather than pinging the database I think searching or filtering will be a bit more work.  I Googled some methods for searching within XML files and none that I found looked easy.
I'll go back to the SqlCommand in a button and see if that works fast enough to get by.  Otherwise I may have to continue exploring an XML solution.
0
 
LVL 1

Author Comment

by:megnin
ID: 33724517
Okay, with the SqlDataReader in a button, that actually works great and pretty fast.  The only issue is that every search is added to the existing contents of the ddl.
How can I "reset" or empty out the DropDownList?  I assume I would do that in the Page_Load and an If Not IsPostBack.
Can I just ... ddlNAICS.DataSource = Nothing  or something like that?
0
 
LVL 1

Author Comment

by:megnin
ID: 33725016
Okay, this is working pretty well:




    Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click

          'Clear or Reset the DropDownList prior to populating it with the new search:

        ddlNAICS.Items.Clear()



        Dim sSql As String = "SELECT Description, Code FROM NAICS WHERE Description LIKE '%" & txtSearch.Text & "%' ORDER BY Description ASC"

        Dim cmd As New SqlCommand(sSql, New SqlConnection(ConfigurationManager.ConnectionStrings("OJTConnectionString").ConnectionString))



        cmd.Connection.Open()



        Dim drNAIC As SqlDataReader

        drNAIC = cmd.ExecuteReader()

        drNAIC.Read()



        ddlNAICS.DataSource = drNAIC           'ds

        ddlNAICS.DataTextField = "Description"

        ddlNAICS.DataValueField = "Code"

        ddlNAICS.DataBind()



        cmd.Connection.Close()

        cmd.Connection.Dispose()



    End Sub

Open in new window

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33726064
ok, glad to hear its working better. so where we at with this right now then
0
 
LVL 1

Author Comment

by:megnin
ID: 33726096
I'm going to close this one as solved.  
I've got other issue, so I'll open a new question for that.  Doing math on textbox contents is kicking my tail now.  
0
 
LVL 1

Author Closing Comment

by:megnin
ID: 33726122
Thanks.  This solution works great.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 33726191
ok.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
A short film showing how OnPage and Connectwise integration works.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

929 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now