• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 579
  • Last Modified:

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

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
megnin
Asked:
megnin
  • 27
  • 16
  • 5
1 Solution
 
rockiroadsCommented:
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
 
GMGeniusCommented:
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
 
GMGeniusCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
megninAuthor Commented:
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
 
megninAuthor Commented:
What's the difference between "@SearchNAIC" and "@p1" other than it's shorter?
0
 
GMGeniusCommented:
Not entirely sure, all i know what I use and it works :)
0
 
GMGeniusCommented:
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
 
megninAuthor Commented:
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
 
megninAuthor Commented:
GMGenius, oh, I see.  I'll give it a shot, too.
0
 
rockiroadsCommented:
>> 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
 
rockiroadsCommented:
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
 
megninAuthor Commented:
Ahhhhhh.  I hadn't thought about indexing the fields.  The table only contains "Description" and "Code".  Would I index both columns?
0
 
megninAuthor Commented:
rocki, I'm using SQL Server 2005.
0
 
megninAuthor Commented:
That table is set to "Indexable = Yes" but "Is Full-text Indexed = No" Should I set "Full-text Specification" to Yes?
0
 
rockiroadsCommented:
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
 
megninAuthor Commented:
Oh, I'm using SQL Server Management Studio to work with the database definition.
0
 
rockiroadsCommented:
>> 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
 
megninAuthor Commented:
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
 
megninAuthor Commented:
Maybe I could use both columns as the identity?  Can I do that and would that have an adverse affect on the performance?
0
 
rockiroadsCommented:
Whats wrong with just creating a new identity column? it helps uniquely identify a row. Easier on updates etc.
0
 
megninAuthor Commented:
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
 
megninAuthor Commented:
Oh, never mind.  I set it to Identity and it just worked.  Filled 'em up with ID's.  ;-)
0
 
rockiroadsCommented:
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
 
megninAuthor Commented:
When I right-click on the table (in SSMS) I see "Full-Text index >" but it's greyed out.  It's not selectable.
0
 
rockiroadsCommented:
seems like service is not running? http://support.microsoft.com/?id=270671

0
 
megninAuthor Commented:
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
 
megninAuthor Commented:
going to lunch be back in an hour....
0
 
rockiroadsCommented:
bring us a BLT please on brown bread, thanks!
0
 
megninAuthor Commented:
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
 
rockiroadsCommented:
when u said the sql filter didnt work, what happened?
0
 
megninAuthor Commented:
It just gave me the entire table.
0
 
rockiroadsCommented:
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
 
megninAuthor Commented:
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
 
megninAuthor Commented:

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
 
GMGeniusCommented:
If txtSearch.text was empty you would get the entire table
0
 
megninAuthor Commented:
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
 
megninAuthor Commented:
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
 
rockiroadsCommented:
sounds like a plan - heinekin m8
0
 
rockiroadsCommented:
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
 
megninAuthor Commented:
heinekin? After yesterday it was more like Aberlour.  ;-)
0
 
megninAuthor Commented:
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
 
megninAuthor Commented:
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
 
megninAuthor Commented:
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
 
rockiroadsCommented:
ok, glad to hear its working better. so where we at with this right now then
0
 
megninAuthor Commented:
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
 
megninAuthor Commented:
Thanks.  This solution works great.
0
 
rockiroadsCommented:
ok.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 27
  • 16
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now