We help IT Professionals succeed at work.

RowFilter property not working with DataGrid

jtrapat1
jtrapat1 asked
on
309 Views
Last Modified: 2013-11-26
Im trying to pass a letter from an alphabet menu to my SELECT statement as a WHERE clause in order to narrow down the results of my AddressList.
I have a DataGrid, Repeater, and LinkButton:
Heres my code:
-----------------------------------------------
 Sub BindData(ByVal GetFresh As Boolean)
            Dim DTable As DataTable = Nothing
            If ViewState("vsSortData") Is Nothing Or GetFresh Then
                cmd = New SqlCommand("SELECT DISTINCT [general/name] FROM AddressBook ", myConn)
                myDataAdapter = New SqlDataAdapter(cmd)
                Dim DSet As New DataSet()
                Try
                    myDataAdapter.Fill(DSet)
                    DTable = DSet.Tables(0)
                Catch EXC As SqlException
                    Return
                Finally
                    myConn.Close()
                End Try
                ViewState("vsSortData") = DTable
            Else
                DTable = CType(ViewState("vsSortData"), DataTable)
            End If
            If strFilter = "All" Then
                DTable.DefaultView.RowFilter = String.Empty
            Else
                DTable.DefaultView.RowFilter = "[general/name] LIKE '" & strFilter & "%' AND location_id = 1"
            End If
            DTable.DefaultView.Sort = "[general/name] ASC"
            Me.dgAddress.DataSource = DTable.DefaultView
            Me.dgAddress.DataBind()
            BuildAlphaPager()
        End Sub
-------------------------------------------
And strFilter gets filled here:
------------------------------------------------------
 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            Dim ThisFilter As Object = ViewState("vsFilter")
            If Not (ThisFilter Is Nothing) Then
                strFilter = CStr(ThisFilter)
            Else
                strFilter = "All"
            End If
            If Not Page.IsPostBack Then
                BindData(True)
            End If
        End Sub
---------------------------------------------------
I'm trying to customize some previously written code and I know the javascript involved is this:
---------------------------
<a id="rptAlpha_ctl04_lnkAlpha" class="letters" href="javascript:__doPostBack('rptAlpha$ctl04$lnkAlpha','')">D</a>
--------------------------

So, when you click on a letter - it should filter the dataset below.
When I click on the letter, it does not filter the data but when I hard-code the sql statement, it does.
Is there any function I can override or add?

Thanks in Advance.

Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2008

Commented:
With the RowFilter, it isn't SQL, it is a limited filter expression.  The Like operator uses '*'.

Try this:

    DTable.DefaultView.RowFilter = String.Format("[general/name] LIKE '{0}*' AND location_id = 1", strFilter)

Bob

Author

Commented:
I tried what you added but it's not working.
Heres where I build the Alphabet string: and the ItemCommand function:
Can you see anything wrong here?
Do I have to enable ViewState or something?
My javascript Replace function doesnt look right.......

--------------------------------------------
      Sub BuildAlphaPager()
            Dim DTable As DataTable
            If ViewState(("strLetter")) Is Nothing Then
                Dim arrLetters As String() = {"All", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"}
                DTable = New DataTable()
                DTable.Columns.Add(New DataColumn("Letter", GetType(String)))
                Dim i As Integer
                For i = 0 To arrLetters.Length - 1
                    Dim DRow As DataRow = DTable.NewRow()
                    DRow(0) = arrLetters(i)
                    DTable.Rows.Add(DRow)
                Next i
                ViewState("strLetter") = DTable
            Else
                DTable = CType(ViewState("strLetter"), DataTable)
            End If
            Me.rptAlpha.DataSource = DTable.DefaultView
            Me.rptAlpha.DataBind()
        End Sub

        Protected Sub rptAlpha_ItemDataBound(ByVal sender As Object, ByVal e As RepeaterItemEventArgs)
            ' row.Attributes["onClick"] = "javascript:__doPostBack('" + link.ClientID.Replace("_", "$").Replace("$$", "$_") + "', '');"

            If (e.Item.ItemType = ListItemType.Header) Then
            ElseIf (e.Item.ItemType = ListItemType.Item) Or (e.Item.ItemType = ListItemType.AlternatingItem) Then
                Dim lnkAlpha As LinkButton = CType(e.Item.FindControl("lnkAlpha"), LinkButton)
                lnkAlpha.Text = DataBinder.Eval(e.Item.DataItem, "Letter")
                lnkAlpha.CommandName = "Filter"
                lnkAlpha.CommandArgument = DataBinder.Eval(e.Item.DataItem, "Letter")
                Dim DRView As DataRowView = CType(e.Item.DataItem, DataRowView)
                If CStr(DRView(0)) = strFilter Then
                End If
            ElseIf (e.Item.ItemType = ListItemType.Footer) Then
            End If
        End Sub

        Protected Sub rptAlpha_ItemCommand(ByVal source As Object, ByVal e As RepeaterCommandEventArgs)
            If e.CommandName = "Filter" Then
                strFilter = CStr(e.CommandArgument)
                ViewState("vsFilter") = strFilter
                BindData(False)
            End If
        End Sub
---------------------------------------------

Author

Commented:
My mistake - the javascript Replace function is commented out-
I thought I would have to override the javascript function to make this code work.

Author

Commented:
Here is my main source code where I declare the datagrid, repeater and linkbutton:
Is there anything incorrect here:
----------------------------------------------
<asp:Repeater ID="rptAlpha" runat="server">
                <ItemTemplate>
                        <td  background="~/images/addr_body.gif" align="center" nowrap style="PADDING-RIGHT: 3px; PADDING-LEFT: 3px">
             <asp:LinkButton id="lnkAlpha" runat="server"   CssClass="letters"
               CommandName="Filter"
                CommandArgument='<%# DataBinder.Eval(Container, "DataItem.Letter")%>'>
               <%# DataBinder.Eval(Container, "DataItem.Letter")%>
            </asp:LinkButton>
                    </td>
                </ItemTemplate>
            </asp:Repeater>
                                   </tr>
                                    </table>
                              </asp:TableCell>
                        </asp:TableRow>
                  </asp:Table>
         
                 <asp:Table id="tblContent" runat="server" Width="98%" Height="390" HorizontalAlign="Center"
                        CellPadding="0" CellSpacing="0" BackImageUrl="~/images/cell2_bkgrd.gif">
                        <asp:TableRow VerticalAlign="Top" runat="server">
                              <asp:TableCell Width="19px" runat="server">&nbsp;
                      <asp:DataGrid ID="dgAddress" runat="server" Width="100%" CellPadding="3" BackColor="#DEBA84" BorderWidth="1px" CellSpacing="2" BorderStyle="None" BorderColor="#DEBA84">
                <ItemStyle ForeColor="#8C4510" BackColor="#F7F7F7" />
                <HeaderStyle Font-Bold="True" ForeColor="White" BackColor="#E9EAEC" />
                </asp:DataGrid>            
----------------------------------
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2008

Commented:
1) "It doesn't work" is pretty vague and doesn't help.

2) Is this working now?

3) I don't see anything wrong, so are you still having a problem with the DataView RowFilter?

Bob

Author

Commented:
Sorry for the vagueness;
Its difficult to debug and all I know is that when I click on the letter on my alphabet to refresh the page and show the flitered list below; I still get the same list of all names in the table-

when i do a mouse-over on the letter- in the statusbar it shows the javascript function that I mentioned earlier:  
For example, when I click on the letter G:
in the status bar- it says:
javascript:__doPostBack('rptAlpha$ctl07$lnkAlpha','')
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2008

Commented:
1) That is much better

2) You are storing the filter in the ViewState.

3) When the page posts back, does the value in the ViewState equal what you expect?

Bob

Author

Commented:
The ViewState is my problem:
When I debug that value, I get:
------------------------

1371227564
vsSortData2l
--------------------------
So this is definitely my problem.
How can I go about cleaning this value up?
And passing it correctly from page to page?

Thanks in Advance.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2008

Commented:
1) You can't pass ViewState from page to page, it is for the current page only.

2) If you need access from a different page, then use a Session variable.

3) Where are you setting the value?

Bob

Author

Commented:
Bob-
Thanks.
The ViewState is the problem. It was eating up all of my resources on the page.
Can you suggest a better way to pass this item?
I have sort of a rolodex alphabet across the top of the form and when the user clicks on the letter, the
 rptAlpha_ItemDataBound event fires and that is when I would like to set that letter in my where clause in RowFilter.
Thanks Again for helping me find the problem.
John
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2008
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Bob-
Thanks.
I did what you suggested and replaced all the instances of ViewState with Session and its not working (i know you dont want to hear that, but....)

I'll try to figure out how to debug this Session object - unless you know of a way.
I'm still working on this problem.

John

Author

Commented:
Bob-
can you tell me-
Am I supposed to turn something on in the main .aspx page to enable the Session or ViewState objects?
Because I cant believe I cant find the value of one variable and pass this to my rowfilter...

When I do a response.Write and print out the value of:
      <%Response.Write(Session("vsSortData"))%>
It simply says Table.
How can I use that?
Am I missing something here?

Thanks in Advance.

Author

Commented:
Bob-
I have data!
If you look in my .aspx page where I set up the repeater, linkbutton, and datagrid-
I didnt set and events for the repeater only the linkbutton-
So, I changed the repeater code to: :
-----------------------------------------------
<asp:Repeater ID="rptAlpha" runat="server" OnItemDataBound="rptAlpha_ItemDataBound" OnItemCommand="rptAlpha_ItemCommand">
                <ItemTemplate>
                        <td  background="~/images/addr_body.gif" align="center" nowrap style="PADDING-RIGHT: 3px; PADDING-LEFT: 3px">
             <asp:LinkButton id="lnkAlpha" runat="server"   CssClass="letters"
               CommandName="Filter"
                CommandArgument='<%# DataBinder.Eval(Container, "DataItem.Letter")%>'>
               <%# DataBinder.Eval(Container, "DataItem.Letter")%>
            </asp:LinkButton>
                    </td>
                </ItemTemplate>
            </asp:Repeater>
-----------------------------------------
And now it works.
I thought that since the linkbutton was nested inside the repeater that the Command Argument of teh LinkButton would be called when the user clicked on the LinkButton.

Thanks For All Your Help.
John
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*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.