Solved

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

Posted on 2010-09-20
48
545 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
What's the difference between "@SearchNAIC" and "@p1" other than it's shorter?
0
 
LVL 12

Expert Comment

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

Expert Comment

by:GMGenius
Comment Utility
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
Comment Utility
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
Comment Utility
GMGenius, oh, I see.  I'll give it a shot, too.
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
>> 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
Comment Utility
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
Comment Utility
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
Comment Utility
rocki, I'm using SQL Server 2005.
0
 
LVL 1

Author Comment

by:megnin
Comment Utility
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
Comment Utility
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
Comment Utility
Oh, I'm using SQL Server Management Studio to work with the database definition.
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
>> 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
seems like service is not running? http://support.microsoft.com/?id=270671

0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
0
 
LVL 1

Author Comment

by:megnin
Comment Utility
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
Comment Utility
going to lunch be back in an hour....
0
 
LVL 65

Expert Comment

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

Author Comment

by:megnin
Comment Utility
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
Comment Utility
when u said the sql filter didnt work, what happened?
0
 
LVL 1

Author Comment

by:megnin
Comment Utility
It just gave me the entire table.
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
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
Comment Utility
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
Comment Utility

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
Comment Utility
If txtSearch.text was empty you would get the entire table
0
 
LVL 1

Author Comment

by:megnin
Comment Utility
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
Comment Utility
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
Comment Utility
sounds like a plan - heinekin m8
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
Comment Utility
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
Comment Utility
heinekin? After yesterday it was more like Aberlour.  ;-)
0
 
LVL 1

Author Comment

by:megnin
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
ok, glad to hear its working better. so where we at with this right now then
0
 
LVL 1

Author Comment

by:megnin
Comment Utility
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
Comment Utility
Thanks.  This solution works great.
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
ok.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
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…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

772 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