Solved

search button asp.net

Posted on 2010-08-31
9
338 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:TonyReba
  • 6
  • 2
9 Comments
 
LVL 6

Assisted Solution

by:Aksh
Aksh earned 100 total points
ID: 33568086
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
 
LVL 9

Author Comment

by:TonyReba
ID: 33568190
i believe thats not the issue but the logic  itself, I test Zip by itself but not with the other conditions
0
 
LVL 9

Expert Comment

by:puru1981
ID: 33568425
what do you mean by restricted value???
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 9

Author Comment

by:TonyReba
ID: 33568483
I meant Where parameters sorry
0
 
LVL 9

Author Comment

by:TonyReba
ID: 33568902
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
 
LVL 9

Accepted Solution

by:
puru1981 earned 400 total points
ID: 33569497
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
 
LVL 9

Author Comment

by:TonyReba
ID: 33570012
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
 
LVL 9

Author Comment

by:TonyReba
ID: 33570036
Nevermind I forgot to re-run, it worked
0
 
LVL 9

Author Closing Comment

by:TonyReba
ID: 33570047
thanks guys
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

770 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