search button asp.net

Why I cant get the results when I try to show restricted values (zip) (name)
Protected Sub searchButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles searchButton.Click
        ListView1.DataBind()
        ListView1.Visible = "True"
        ListView2.Visible = "False"

        If specialityDropDown.SelectedValue = "All" And providerTextBox.Text = "" And txtZipCode.Text = "" Then

            ' Show All Results 
            ListView2.DataBind()
            ListView1.Visible = "False"
            ListView2.Visible = "True"
        End If

        If specialityDropDown.SelectedValue = "All" And (providerTextBox.Text <> "" Or txtZipCode.Text <> "") Then

            ' Show All Results 
            Dim sql As String
            'sql = "SELECT [First_Name],[Last_Name],[Prof_Designation], [Specialty], [Specialty2],[Address], [Address2], [City], [State], [Zip], [Phone], [Fax] FROM [tbl_Providers] WHERE ((@First_Name IS NULL OR First_Name LIKE '%' +  @First_Name + '%') OR (@Last_Name IS NULL OR Last_Name LIKE '%' +  @Last_Name + '%')) OR (@Zip IS NULL OR Zip LIKE '%' + @Zip + '%') "
            sql = "SELECT [First_Name],[Last_Name],[Prof_Designation], [Specialty], [Specialty2],[Address], [Address2], [City], [State], [Zip], [Phone], [Fax] FROM [tbl_Providers] WHERE Zip LIKE '%" + txtZipCode.Text + "%' "

            AccessDataSource2.SelectCommand = sql
            ListView1.Visible = "False"
            ListView2.Visible = "True"
            ListView1.DataBind()
        End If

    End Sub

    Protected Sub showallButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles showallButton.Click
        ' Show All Results 
        ListView2.DataBind()
        ListView1.Visible = "False"
        ListView2.Visible = "True"
    End Sub

Open in new window

LVL 9
TonyRebaAsked:
Who is Participating?
 
puru1981Connect With a Mentor Commented:
your sql must be this

sql = "SELECT [First_Name],[Last_Name],[Prof_Designation], [Specialty], [Specialty2],[Address], [Address2], [City], [State], [Zip], [Phone], [Fax] FROM [tbl_Providers] WHERE (First_Name LIKE '%" + providerTextBox.Text.ToString + " %' OR Last_Name LIKE '%" + providerTextBox.Text.ToString + "%') AND Zip LIKE '%" + txtZipCode.Text.ToString + "%'  "
0
 
AkshConnect With a Mentor Commented:
Hi,

 Are you getting the results from query when you directly run to database?

There can be issue with the case sensitivity. You may try out "like" predicate with all the cases.

....WHERE Zip LIKE '%" + txtZipCode.Text.ToUpper() + "%' " + OR Zip LIKE '%" + txtZipCode.Text.ToLower() + "%' " + OR Zip LIKE '%" + txtZipCode.Text + "%' " 

========
You should also change the practice of setting the visibility from quoted literal to constant.
You have used: ListView1.Visible = "False"
Instead you should: ListView1.Visible = False
0
 
TonyRebaAuthor Commented:
i believe thats not the issue but the logic  itself, I test Zip by itself but not with the other conditions
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
puru1981Commented:
what do you mean by restricted value???
0
 
TonyRebaAuthor Commented:
I meant Where parameters sorry
0
 
TonyRebaAuthor Commented:
this is my current code it filters by wharever I enter in the provider textbox, but not wherver I enter iun provider texbox and whatever I enter in the zip code texbox


If specialityDropDown.SelectedValue = "All" And (providerTextBox.Text <> "" Or txtZipCode.Text <> "") Then

            ' Show All Results 
            Dim sql As String
            sql = "SELECT [First_Name],[Last_Name],[Prof_Designation], [Specialty], [Specialty2],[Address], [Address2], [City], [State], [Zip], [Phone], [Fax] FROM [tbl_Providers] WHERE (   ((First_Name LIKE '%' +  providerTextBox.Text.ToString + '%') OR (Last_Name LIKE '%' +  providerTextBox.Text.ToString + '%')) OR ( ((First_Name LIKE '%' +  providerTextBox.Text.ToString + '%') OR (Last_Name LIKE '%' +  providerTextBox.Text.ToString + '%')) AND (Zip LIKE '%" + txtZipCode.Text.ToString + "%'   ))) "
            'sql = "SELECT [First_Name],[Last_Name],[Prof_Designation], [Specialty], [Specialty2],[Address], [Address2], [City], [State], [Zip], [Phone], [Fax] FROM [tbl_Providers] WHERE Zip LIKE '%" + txtZipCode.Text.ToString + "%'  "

            'sql = "SELECT [First_Name],[Last_Name],[Prof_Designation], [Specialty], [Specialty2],[Address], [Address2], [City], [State], [Zip], [Phone], [Fax] FROM [tbl_Providers] WHERE ( (Zip LIKE '%" + txtZipCode.Text.ToString + "%') OR  (  (First_Name LIKE '%" + providerTextBox.Text.ToString + "%') OR (Last_Name LIKE '%" + providerTextBox.Text.ToString + "%') )"
            AccessDataSource3.SelectCommand = sql
            ListView1.Visible = False
            ListView2.Visible = True
            ListView2.DataBind()
        End If

Open in new window

0
 
TonyRebaAuthor Commented:
That work for zip code it self , but It can be the case of having both the name and zipcode pertaining to that name
0
 
TonyRebaAuthor Commented:
Nevermind I forgot to re-run, it worked
0
 
TonyRebaAuthor Commented:
thanks guys
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.