firlter gridview

Question

tariqi 0 Newbie Poster

14 Years Ago

i have 5 dropdown list and i want the gridview to get refined with each
selection.this is what i used but its not working:

a button click event that that gets a dataview to filter records then
bind
these records to the gridview.this routine then checks each
dropdown list to see which is selected,then creates a select statement
based on the selected dropdown lists,,

Protected Sub Buttonsearch_Click(ByVal sender As Object, ByVal e As 
System.EventArgs) Handles Buttonsearch.Click 
Dim dv As DataView = GetAll() 
Dim condition As String = " " 


If (DropDownListemployeeID.SelectedValue IsNot "--Select-- 
--Select--") Then 

condition = "EmployeeID='" + DropDownListemployeeID.SelectedValue 
+ 
"'" 


If (DropDownListCompanyID.SelectedValue IsNot "--Select--") Then 
If (condition IsNot " ") Then 
condition += " and CompanyID ='" + 
DropDownListCompanyID.SelectedValue + 
"'" 
Else 
condition = "CompanyID ='" + DropDownListCompanyID.SelectedValue + 
"'" 


If (DropDownListCustomerID.SelectedValue IsNot "--Select--") 
Then 
If (condition IsNot " ") Then 
condition += " and CustomerID ='" + 
DropDownListCustomerID.SelectedValue + 
"'" 
Else 
condition = "CustomerID ='" + DropDownListCustomerID.SelectedValue 
+ 
"'" 


If (DropDownListProjectID.SelectedValue IsNot 
"--Select--") 
Then 
If (condition IsNot " ") Then 
condition += " and ProjectID ='" + 
DropDownListProjectID.SelectedValue + 
"'" 
Else 
condition = "ProjectID ='" + DropDownListProjectID.SelectedValue 
+ 
"'" 


If (DropDownListATMID.SelectedValue IsNot 
"--Select--") 
Then 
If (condition IsNot " ") Then 
condition += " and ATMID ='" + DropDownListATMID.SelectedValue 
+ 
"'" 
Else 
condition = "ATMID ='" + DropDownListATMID.SelectedValue 
+ 
"'" 
End If 
End If 
End If 
End If 
End If 
End If 
End If 
End If 
End If 
If (condition IsNot " ") Then 
dv.RowFilter = condition 

GridViewHourView1.DataSource = dv 
GridViewHourView1.DataBind() 

End If 
End Sub

your assistance will be highly appreciated.

Regards,
Tariq

asp.net

Edited 11 Years Ago by mike_2000_17 because: Fixed formatting

2 Contributors
6 Replies
100 Views
1 Month Discussion Span
Latest Post 14 Years Ago Latest Post by tariqi

rohand 1 Posting Whiz in Training

14 Years Ago

Why you have written If..Else condition? Instead i say just write a separate If..End If block for each Dropdown. Also are you sure your EmployeeID, CustomerID, ATMID etc are String/text/varchar type in database ? because SelectedValue property return string.

Another thing is If user haven't selected any value from Dropdown then in this case don't you need to show all the records without filter ? If this is the case then you need to change your below condition :

If (condition IsNot " ") Then 
   dv.RowFilter = condition 
End If 
GridViewHourView1.DataSource = dv 
GridViewHourView1.DataBind()

rohand 1 Posting Whiz in Training

14 Years Ago

Oh..Do one thing, first make sure your function GetAll() returns rows in dataview. I mean dataview has rows. Then put the DataSource and DataBinding of GridView outside the If..End If condition that i have mentioned in earlier post. And see whether Gridview getting bind or not. If it's showing/binding the data then it's fine.

Now change the code of your button click event with below modified code and see it's working or not :

If (Not DropDownListemployeeID.SelectedValue.Equals("value you don;t want to consider in criteria")) Then
    condition += "EmployeeID='" + DropDownListemployeeID.SelectedValue + "'"
End If

If (Not DropDownListCompanyID.SelectedValue.Equals("value you don;t want to consider in criteria")) Then
   If (Not condition.Equals("")) Then
       condition += " and CompanyID ='" + DropDownListCompanyID.SelectedValue + "'"
   Else
       condition += "CompanyID ='" + DropDownListCompanyID.SelectedValue + "'"
   End If
End If

If (Not DropDownListCustomerID.SelectedValue.Equals("value you don;t want to consider in criteria")) Then
   If (Not condition.Equals(""))Then
        condition += " and CustomerID ='" + DropDownListCustomerID.SelectedValue + "'"
   Else
        condition += "CustomerID ='" + DropDownListCustomerID.SelectedValue + "'"
   End If
End If

If (Not DropDownListProjectID.SelectedValue.Equals("value you don;t want to consider in criteria")) Then
   If (Not condition.Equals(""))Then
         condition += " and ProjectID ='" + DropDownListProjectID.SelectedValue + "'"
   Else
         condition += "ProjectID ='" + DropDownListProjectID.SelectedValue + "'"
   End If
End If

If (Not DropDownListATMID.SelectedValue.Equals("value you don;t want to consider in criteria")) Then
   If (Not condition.Equals("")) Then
         condition += " and ATMID ='" + DropDownListATMID.SelectedValue + "'"
   Else
         condition += "ATMID ='" + DropDownListATMID.SelectedValue + "'"
   End If
End If

then rest of the code is as it is. Try by this way and let us know..

Hope it will fix your problem.

Thanks for the assistance,I have done and checked what you suggested,the code is not bringing any error,but still the filtered gridview does not get displayed.

below is the getall() function which fills a dataview with records and returns it as dataview,the click event then uses this dataview to filter off the required records:

Public Function GetAll() As DataView
        Dim dv As DataView
        Dim ds As New DataSet
        Dim myConn3 As New SqlConnection("Data Source=TARIQ-PC\SQLEXPRESS;Initial Catalog=TimeSheet;Integrated Security=True")
        Dim myCmd3 As SqlCommand = New SqlCommand
        Dim myadp3 As New SqlDataAdapter
        myConn3.Open()
        myCmd3 = New SqlCommand("Select * from hours", myConn3)
        myadp3.SelectCommand = myCmd3
        myadp3.Fill(ds, "create dataview")
        myadp3.Dispose()
        myCmd3.Dispose()
        myConn3.Close()
        dv = ds.Tables(0).DefaultView
        Return dv


    End Function
 

Protected Sub Buttonsearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Buttonsearch.Click
        Dim dv As DataView = GetAll()
        Dim condition As String = ""
        

        If (DropDownListemployeeID.SelectedIndex <> 0) Then

            condition += "EmployeeID='" + DropDownListemployeeID.SelectedValue + "'"
        End If
        If (DropDownListCompanyID.SelectedIndex <> 0) Then
            If (condition IsNot " ") Then
                condition += " and CompanyID ='" + DropDownListCompanyID.SelectedValue + "'"
            Else
                condition += "CompanyID ='" + DropDownListCompanyID.SelectedValue + "'"
            End If
        End If

        If (DropDownListCustomerID.SelectedIndex <> 0) Then
            If (condition IsNot " ") Then
                condition += " and CustomerID ='" + DropDownListCustomerID.SelectedValue + "'"
            Else
                condition += "CustomerID ='" + DropDownListCustomerID.SelectedValue + "'"
            End If
        End If

        If (DropDownListProjectID.SelectedIndex <> 0) Then
            If (condition IsNot " ") Then
                condition += " and ProjectID ='" + DropDownListProjectID.SelectedValue + "'"
            Else
                condition += "ProjectID ='" + DropDownListProjectID.SelectedValue + "'"
            End If
        End If

        If (DropDownListATMID.SelectedIndex <> 0) Then
            If (condition IsNot " ") Then
                condition += " and ATMID ='" + DropDownListATMID.SelectedValue + "'"
            Else
                condition += "ATMID ='" + DropDownListATMID.SelectedValue + "'"
            End If
        End If


        If (condition IsNot " ") Then
            dv.RowFilter = condition

            GridViewHourView1.DataSource = dv
            GridViewHourView1.DataBind()

        End If
    End Sub

please assist
thanks

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

tariqi 0 Newbie Poster · Answer 1 · 2010-07-30T20:48:09+00:00

Thanks for the assistance,I have done and checked what you suggested,the code is not bringing any error,but still the filtered gridview does not get displayed.

below is the getall() function which fills a dataview with records and returns it as dataview,the click event then uses this dataview to filter off the required records:

Public Function GetAll() As DataView
        Dim dv As DataView
        Dim ds As New DataSet
        Dim myConn3 As New SqlConnection("Data Source=TARIQ-PC\SQLEXPRESS;Initial Catalog=TimeSheet;Integrated Security=True")
        Dim myCmd3 As SqlCommand = New SqlCommand
        Dim myadp3 As New SqlDataAdapter
        myConn3.Open()
        myCmd3 = New SqlCommand("Select * from hours", myConn3)
        myadp3.SelectCommand = myCmd3
        myadp3.Fill(ds, "create dataview")
        myadp3.Dispose()
        myCmd3.Dispose()
        myConn3.Close()
        dv = ds.Tables(0).DefaultView
        Return dv


    End Function
 

Protected Sub Buttonsearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Buttonsearch.Click
        Dim dv As DataView = GetAll()
        Dim condition As String = ""
        

        If (DropDownListemployeeID.SelectedIndex <> 0) Then

            condition += "EmployeeID='" + DropDownListemployeeID.SelectedValue + "'"
        End If
        If (DropDownListCompanyID.SelectedIndex <> 0) Then
            If (condition IsNot " ") Then
                condition += " and CompanyID ='" + DropDownListCompanyID.SelectedValue + "'"
            Else
                condition += "CompanyID ='" + DropDownListCompanyID.SelectedValue + "'"
            End If
        End If

        If (DropDownListCustomerID.SelectedIndex <> 0) Then
            If (condition IsNot " ") Then
                condition += " and CustomerID ='" + DropDownListCustomerID.SelectedValue + "'"
            Else
                condition += "CustomerID ='" + DropDownListCustomerID.SelectedValue + "'"
            End If
        End If

        If (DropDownListProjectID.SelectedIndex <> 0) Then
            If (condition IsNot " ") Then
                condition += " and ProjectID ='" + DropDownListProjectID.SelectedValue + "'"
            Else
                condition += "ProjectID ='" + DropDownListProjectID.SelectedValue + "'"
            End If
        End If

        If (DropDownListATMID.SelectedIndex <> 0) Then
            If (condition IsNot " ") Then
                condition += " and ATMID ='" + DropDownListATMID.SelectedValue + "'"
            Else
                condition += "ATMID ='" + DropDownListATMID.SelectedValue + "'"
            End If
        End If


        If (condition IsNot " ") Then
            dv.RowFilter = condition

            GridViewHourView1.DataSource = dv
            GridViewHourView1.DataBind()

        End If
    End Sub

please assist
thanks

tariqi 0 Newbie Poster · Answer 2 · 2010-08-05T12:17:18+00:00

sory,was away from the office for a while.i tried what you adviced,thanks,but still the gridview is not getting displayed,when i try to run the dataview independently without filtering,the gridview gets binded and displays all records ok,so maybe there is something wrong with the filtering part of my code maybe? to be honest everything seems right for me.so rather than creating a different function for the dataview,i put it in the click event so that the dataview gets generated when button is clicked,but still not working,below is the code:

Protected Sub Buttonsearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Buttonsearch.Click
        'Dim dv As DataView = GetAll()
        Dim condition As String = ""
        Dim dv As DataView
        Dim ds As New DataSet
        Dim myConn3 As New SqlConnection("Data Source=TARIQ-PC\SQLEXPRESS;Initial Catalog=TimeSheet;Integrated Security=True")
        Dim myCmd3 As SqlCommand = New SqlCommand
        Dim myadp3 As New SqlDataAdapter
        myConn3.Open()
        myCmd3 = New SqlCommand("Select CONVERT (varchar(10), CallDate, 103) AS CallDate, CONVERT (varchar(10), StartTime, 108) AS StartTime, CONVERT (varchar(10), Finishtime, 108) AS FinishTime,EmployeeID,CompanyID,ProjectID,CustomerID,ATMID, Description  from hours", myConn3)
        myadp3.SelectCommand = myCmd3
        myadp3.Fill(ds, "create dataview")
        myadp3.Dispose()
        myCmd3.Dispose()
        myConn3.Close()
        dv = New DataView(ds.Tables(0))

        If (Not DropDownListemployeeID.SelectedValue.Equals("--All-- --All--")) Then

            condition += "EmployeeID='" + DropDownListemployeeID.SelectedValue + "'"
        End If
        If (DropDownListCompanyID.SelectedIndex <> 0) Then
            If (Not condition.Equals("")) Then
                condition += " and CompanyID ='" + DropDownListCompanyID.SelectedValue + "'"
            Else
                condition += "CompanyID ='" + DropDownListCompanyID.SelectedValue + "'"
            End If
        End If

        If (Not DropDownListCustomerID.SelectedValue.Equals("--All--")) Then
            If (Not condition.Equals("")) Then
                condition += " and CustomerID ='" + DropDownListCustomerID.SelectedValue + "'"
            Else
                condition += "CustomerID ='" + DropDownListCustomerID.SelectedValue + "'"
            End If
        End If

        If (Not DropDownListProjectID.SelectedValue.Equals("--All--")) Then
            If (Not condition.Equals("")) Then
                condition += " and ProjectID ='" + DropDownListProjectID.SelectedValue + "'"
            Else
                condition += "ProjectID ='" + DropDownListProjectID.SelectedValue + "'"
            End If
        End If

        If (Not DropDownListATMID.SelectedValue.Equals("--All--")) Then
            If (Not condition.Equals("")) Then
                condition += " and ATMID ='" + DropDownListATMID.SelectedValue + "'"
            Else
                condition += "ATMID ='" + DropDownListATMID.SelectedValue + "'"
            End If
        End If


        If (Not condition.Equals("")) Then
            dv.RowFilter = condition

        End If
        GridViewHourView1.DataSource = dv
        GridViewHourView1.DataBind()
    End Sub

your assistance is highly appreciated.

rohand 1 Posting Whiz in Training · Answer 3 · 2010-08-05T12:50:04+00:00

Oh i see. I guess the problem is because of single quote (') in your filtering condition. I believe your EmployeeId, ProjectId looks like (1001,1 etc). I mean it's not alphabetics. If so then you need to remove single quote (') from your code. So your condition may looks like :

If (Not DropDownListemployeeID.SelectedValue.Equals("--All-- --All--")) Then
   condition += "EmployeeID=" + DropDownListemployeeID.SelectedValue
End If
If (DropDownListCompanyID.SelectedIndex <> 0) Then
   If (Not condition.Equals("")) Then
       condition += " and CompanyID =" + DropDownListCompanyID.SelectedValue
   Else
       condition += "CompanyID =" + DropDownListCompanyID.SelectedValue 
   End If
End If

Try by this and let us know..

sory,was away from the office for a while.i tried what you adviced,thanks,but still the gridview is not getting displayed,when i try to run the dataview independently without filtering,the gridview gets binded and displays all records ok,so maybe there is something wrong with the filtering part of my code maybe? to be honest everything seems right for me.so rather than creating a different function for the dataview,i put it in the click event so that the dataview gets generated when button is clicked,but still not working,below is the code:

Protected Sub Buttonsearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Buttonsearch.Click
        'Dim dv As DataView = GetAll()
        Dim condition As String = ""
        Dim dv As DataView
        Dim ds As New DataSet
        Dim myConn3 As New SqlConnection("Data Source=TARIQ-PC\SQLEXPRESS;Initial Catalog=TimeSheet;Integrated Security=True")
        Dim myCmd3 As SqlCommand = New SqlCommand
        Dim myadp3 As New SqlDataAdapter
        myConn3.Open()
        myCmd3 = New SqlCommand("Select CONVERT (varchar(10), CallDate, 103) AS CallDate, CONVERT (varchar(10), StartTime, 108) AS StartTime, CONVERT (varchar(10), Finishtime, 108) AS FinishTime,EmployeeID,CompanyID,ProjectID,CustomerID,ATMID, Description  from hours", myConn3)
        myadp3.SelectCommand = myCmd3
        myadp3.Fill(ds, "create dataview")
        myadp3.Dispose()
        myCmd3.Dispose()
        myConn3.Close()
        dv = New DataView(ds.Tables(0))

        If (Not DropDownListemployeeID.SelectedValue.Equals("--All-- --All--")) Then

            condition += "EmployeeID='" + DropDownListemployeeID.SelectedValue + "'"
        End If
        If (DropDownListCompanyID.SelectedIndex <> 0) Then
            If (Not condition.Equals("")) Then
                condition += " and CompanyID ='" + DropDownListCompanyID.SelectedValue + "'"
            Else
                condition += "CompanyID ='" + DropDownListCompanyID.SelectedValue + "'"
            End If
        End If

        If (Not DropDownListCustomerID.SelectedValue.Equals("--All--")) Then
            If (Not condition.Equals("")) Then
                condition += " and CustomerID ='" + DropDownListCustomerID.SelectedValue + "'"
            Else
                condition += "CustomerID ='" + DropDownListCustomerID.SelectedValue + "'"
            End If
        End If

        If (Not DropDownListProjectID.SelectedValue.Equals("--All--")) Then
            If (Not condition.Equals("")) Then
                condition += " and ProjectID ='" + DropDownListProjectID.SelectedValue + "'"
            Else
                condition += "ProjectID ='" + DropDownListProjectID.SelectedValue + "'"
            End If
        End If

        If (Not DropDownListATMID.SelectedValue.Equals("--All--")) Then
            If (Not condition.Equals("")) Then
                condition += " and ATMID ='" + DropDownListATMID.SelectedValue + "'"
            Else
                condition += "ATMID ='" + DropDownListATMID.SelectedValue + "'"
            End If
        End If


        If (Not condition.Equals("")) Then
            dv.RowFilter = condition

        End If
        GridViewHourView1.DataSource = dv
        GridViewHourView1.DataBind()
    End Sub

your assistance is highly appreciated.

tariqi 0 Newbie Poster · Answer 4 · 2010-09-07T18:49:58+00:00

thanks alot,used your method and everything is working ok.