?
Solved

search button asp.net

Posted on 2010-08-31
9
Medium Priority
?
368 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 400 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 1600 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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
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…
Suggested Courses

809 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