We help IT Professionals succeed at work.

problem getting "LIKE @parameter%" to work

263 Views
Last Modified: 2012-06-21
code:

   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 + "%')"
Comment
Watch Question

Ron MalmsteadInformation Services Manager
CERTIFIED EXPERT

Commented:
First, change your "+" symbols to "&" ampersand.

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

Example:
Change...
'%" + specialityDropDown.SelectedValue.ToString + " %'
To ....
'*" & specialityDropDown.SelectedValue.ToString & "*'
Top Expert 2010

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

Author

Commented:
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
            ListView1.DataBind()

        End If


<asp:AccessDataSource ID="AccessDataSource1" runat="server" 
        DataFile="~/App_Data/Providers.mdb"  
        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] = ? " >
         
        <SelectParameters>
            <asp:ControlParameter ControlID="providerTextBox" Name="newparameter" 
                PropertyName="Text" />
            <asp:ControlParameter ControlID="specialityDropDown" Name="newparameter" 
                PropertyName="SelectedValue" />
        </SelectParameters>

Open in new window

CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
got it
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.