problem getting "LIKE @parameter%" to work

Posted on 2011-04-26
Last Modified: 2012-06-21

   If specialityDropDown.SelectedValue <> "" Then
            ' Show me all doctors with that specialty
            Dim sql2 As String
            sql2 = "SELECT [First_Name],[Last_Name],[Prof_Designation], [Specialty], [Specialty2],[Address], [Address2], [City], [State], [Zip], [Phone], [Fax], [Provider_Type] FROM [tbl_Providers] WHERE (Specialty LIKE '%" + specialityDropDown.SelectedValue.ToString + " %' OR Specialty2 LIKE '%" + specialityDropDown.SelectedValue.ToString + "%')"
Question by:TonyReba
    LVL 25

    Expert Comment

    by:Ron M
    First, change your "+" symbols to "&" ampersand.

    I also noticed a "space" ....and since this is MS Access syntax, not SQL...Wildcard should be * not %.

    '%" + specialityDropDown.SelectedValue.ToString + " %'
    To ....
    '*" & specialityDropDown.SelectedValue.ToString & "*'
    LVL 22

    Expert Comment

    by:Om Prakash
    the query seems to be ok,
    try printing the query and run it in the SQL management studio

    also check the value in specialityDropDown.SelectedValue.ToString
    LVL 9

    Author Comment

    I am not being able to get the parameters to pass into the ListView Control
        Protected Sub searchButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles searchButton.Click

            If specialityDropDown.SelectedValue = "All" And providerTextBox.Text <> "" Then
                ' Show me all doctors with that name
                Dim sql1 As String

                sql1 = "SELECT [First_Name],[Last_Name],[Prof_Designation], [Specialty], [Specialty2],[Address], [Address2], [City], [State], [Zip], [Phone], [Fax], [Provider_Type] FROM [tbl_Providers] WHERE (First_Name LIKE '%" + providerTextBox.Text.ToString + " %' OR Last_Name LIKE '%" + providerTextBox.Text.ToString + "%' ) "
                AccessDataSource1.SelectCommand = sql1
                ListView1.Visible = True

            End If

    <asp:AccessDataSource ID="AccessDataSource1" runat="server" 
            SelectCommand = " Select * From  [tbl_Providers] "     
            UpdateCommand="UPDATE [tbl_Providers] SET [Provider_Type] = ?, [Last_Name] = ?, [First_Name] = ?, [Prof_Designation] = ?, [Group_Business_Name] = ?, [Specialty] = ?, [Specialty2] = ?, [Address] = ?, [Address2] = ?, [City] = ?, [State] = ?, [Zip] = ?, [Phone] = ?, [Fax] = ? WHERE [ID] = ? " >
                <asp:ControlParameter ControlID="providerTextBox" Name="newparameter" 
                    PropertyName="Text" />
                <asp:ControlParameter ControlID="specialityDropDown" Name="newparameter" 
                    PropertyName="SelectedValue" />

    Open in new window

    LVL 30

    Accepted Solution

    Try this in your declarations. I think you have syntax problems.
    SelectCommand = "SELECT [First_Name],[Last_Name],[Prof_Designation], [Specialty], [Specialty2],[Address], [Address2], [City], [State], [Zip], [Phone], [Fax], [Provider_Type] FROM [tbl_Providers] WHERE (First_Name LIKE '%' + @Name + '%' OR Last_Name LIKE '%' + @Name + '%')"
        <asp:ControlParameter ControlID="providerTextBox" Name="Name" PropertyName="Text" />

    Open in new window

    LVL 9

    Author Closing Comment

    got it

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    728 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

    20 Experts available now in Live!

    Get 1:1 Help Now