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

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

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

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

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.

What's the difference between "@SearchNAIC" and "@p1" other than it's shorter?

Not entirely sure, all i know what I use and it works :)

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

Select allOpen in new window

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

GMGenius, oh, I see.  I'll give it a shot, too.

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

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.

Ahhhhhh.  I hadn't thought about indexing the fields.  The table only contains "Description" and "Code".  Would I index both columns?

rocki, I'm using SQL Server 2005.

That table is set to "Indexable = Yes" but "Is Full-text Indexed = No" Should I set "Full-text Specification" to Yes?

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.

Oh, I'm using SQL Server Management Studio to work with the database definition.

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

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.

Maybe I could use both columns as the identity?  Can I do that and would that have an adverse affect on the performance?

Whats wrong with just creating a new identity column? it helps uniquely identify a row. Easier on updates etc.

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?

Oh, never mind.  I set it to Identity and it just worked.  Filled 'em up with ID's.  ;-)

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

When I right-click on the table (in SSMS) I see "Full-Text index >" but it's greyed out.  It's not selectable.

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

dunno if this helps http://blog.sqlauthority.com/2008/09/05/sql-server-creating-full-text-catalog-and-index/

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

Select allOpen in new window

going to lunch be back in an hour....

bring us a BLT please on brown bread, thanks!

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?

when u said the sql filter didnt work, what happened?

It just gave me the entire table.

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

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>

Select allOpen in new window

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>

Select allOpen in new window

If txtSearch.text was empty you would get the entire table

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?

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.

sounds like a plan - heinekin m8

heinekin? After yesterday it was more like Aberlour.  ;-)

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.

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?

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

Select allOpen in new window

ok, glad to hear its working better. so where we at with this right now then

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.  

Thanks.  This solution works great.

ok.