ASP.NET need help tweaking filtering and sorting on Datagrid.

I think the whole problem I am having is figuring out postback.  I've had some good help with codebits like dynamically setting the filter by counting the dropdownlists and such.  However I am not getting them to fire at all at this point, and it all seems to look okay to me thus far.  Then again that aint saying much since I am still relatively new to all of this.

Things that are happening:

* You have to click the page number twice in order for the grid to show that page
* The dropdownlists for filter and sort postback, but the function isn't firing (Not sure what I am doing wrong)
* I dimmed a column to create a dropdown list in edit mode, but text box still appears. Dont know how to correct this.
* Trying to figure a way to implement a search function that will filter the search criteria first, then allow filters based off of the original search.

Any suggestions, edits are appreciated.  I am only familiar with VB, so any C# examples please annotate so I can attempt to translate


Code in full:

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Page Language="VB" Debug="true"%>
<html>
 <style>
   .DataGrid {font:x-small Courier New}
   .TextBox{
font-variant:small-caps;
color:#369;
background:#fff;
border:0px;
}
.TextBoxRightAlign
{
text-align:right;
}
 </style>
<head>
      <meta name="GENERATOR" Content="ASP Express 2.1">
      <title>Untitled</title>

<script language="VB" runat="server">

    Public Function Connect() As OleDbConnection
        Dim objConn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=c:\inetpub\database\Inventory.mdb")
        objConn.Open()
        Return objConn
    End Function

    Public Function Filter() As String
        Dim strFilter As String = String.Empty
        Dim strTemp As String = String.Empty
        Dim iCount As Integer = 0
        For iCount = 0 To Me.FilterDataGrid.Controls.Count - 1
            If TypeOf Me.FilterDataGrid.Controls(iCount) Is System.Web.UI.WebControls.DropDownList Then
                strTemp = CType(Me.FilterDataGrid.Controls(iCount), DropDownList).SelectedValue
            If strTemp <> String.Empty Then
                strFilter = strTemp & " AND "
            End If
            End If
         Next
           If strFilter <> String.Empty Then
                strFilter = strFilter.TrimEnd("AND ".ToCharArray())
           End If
lblFilter.Text=strFilter
        Return strFilter
    End Function

Sub DGVoip_EditCommand(s As Object, e As DataGridCommandEventArgs )
      DGVoip.EditItemIndex = e.Item.ItemIndex
      BindData()
End Sub

Sub DGVoip_Cancel(Source As Object, E As DataGridCommandEventArgs)
      DGVoip.EditItemIndex = -1
      BindData()
End Sub

Sub DGVoip_UpdateCommand(s As Object, e As DataGridCommandEventArgs )
        Dim objConn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=c:\inetpub\database\Inventory.mdb")
      Dim MyCommand As OLEDBCommand
      Dim txtSatellite As textbox = E.Item.cells(2).Controls(0)
      Dim txtModel As textbox = E.Item.cells(3).Controls(0)
      Dim txtPhoneNo As textbox = E.Item.cells(4).Controls(0)
      Dim txtSN As textbox = E.Item.cells(5).Controls(0)
      Dim txtIssuedTo As textbox = E.Item.cells(6).Controls(0)
      Dim txtBuilding As textbox = E.Item.cells(8).Controls(0)
        Dim txtRoom As textbox = E.Item.cells(7).Controls(0)
      Dim txtIssuedStock As textbox = E.Item.cells(9).Controls(0)
      Dim ddlSite As DropdownList = E.Item.cells(10).Controls(0)
        txtSN.CssClass="TextBox"
      Dim strUpdateStmt As String
        strUpdateStmt =" UPDATE VOIP SET Satellite =@Satellite, Model =@Model, PhoneNo =@PhoneNo, SN =@SN,

IssuedTo=@IssuedTo, Room=@Room, Building=@Building, IssuedStock= @IssuedStock, Site= @Site WHERE MAC = @MAC"
      MyCommand = New OLEDBCommand(strUpdateStmt, objConn)
      MyCommand.Parameters.Add(New OleDbParameter("@Satellite", txtSatellite.text))
      MyCommand.Parameters.Add(New OleDbParameter("@Model", txtModel.text))
      MyCommand.Parameters.Add(New OleDbParameter("@PhoneNo", txtPhoneNo.text))
      MyCommand.Parameters.Add(New OleDbParameter("@SN", UCase(txtSN.text)))
      MyCommand.Parameters.Add(New OleDbParameter("@IssuedTo", txtIssuedTo.text))
      MyCommand.Parameters.Add(New OleDbParameter("@Building", txtBuilding.text))
      MyCommand.Parameters.Add(New OleDbParameter("@Room", txtRoom.text))
      MyCommand.Parameters.Add(New OleDbParameter("@Site", ddlsite.SelectedItem.Value))
      MyCommand.Parameters.Add(New OleDbParameter("@IssuedStock", txtIssuedStock.Text))
      MyCommand.Parameters.Add(New OleDbParameter("@MAC", e.Item.Cells(1).Text ))
      objConn.Open()
      MyCommand.ExecuteNonQuery()
      DGVoip.EditItemIndex = -1
        objConn.close
      BindData
End Sub

Sub Page_Load(Source as Object, E as EventArgs)
      If not Page.IsPostBack Then
            BindData
        Else
           BindData
      End If
End Sub
Sub BindData()
        Connect()
        Dim dsVoip as DataSet = New DataSet()
        Dim objConn As OleDbConnection = Connect()
        Dim strSQL As String = "Select  * From Voip"
        Dim objCmd as New OleDbDataAdapter(strSQL,objConn)
        objCmd.Fill(dsVoip,"Voip")
        dsVoip.Tables("Voip").DefaultView.Sort = ddlSort.SelectedItem.Value
        dsVoip.Tables("Voip").DefaultView.RowFilter = Filter()
        DGVoip.Datasource = dsVoip.Tables("Voip").DefaultView
        'objConn.Open()
        DGVoip.DataBind()
        'objConn.close
End Sub
    Sub Page_Change(sender As Object, e As DataGridPageChangedEventArgs)
            dgvOIP.CurrentPageIndex = e.NewPageIndex
    End Sub
 
   Sub btnSearch_OnClick(sender As Object, e As EventArgs)
     If btnSearch.Text="Search" Then
        pnlSearch.Visible="True"
        btnSearch.Text="Close Search"
        ddlSatellite.Enabled="False"
        ddlSite.Enabled="False"
     ElseIf btnSearch.Text="Close Search" Then
        pnlSearch.Visible="False"
        btnSearch.Text="Search"
        ddlSatellite.Enabled="True"
        ddlSite.Enabled="True"
        lblFilter.Text=""
     End If
    End Sub
</script>
</head>
<body><div align="center">
<form runat="server" method="post">
<b><asp:Label id="lblFilter" runat="server"></asp:Label></b>
<asp:Panel ID="pnlControls" runat="server" visible="True" align="center">
      <asp:Table border="1" id="toggle" runat="server">
            <asp:TableRow>
                        <asp:TableCell align="middle">
                        <asp:Button id="btnSearch" onclick="btnSearch_OnClick" runat="server" Text="Search"

AutoPostBack="True" causesvalidation="false"></asp:Button>
                  </asp:TableCell>
            </asp:TableRow>  
      </asp:Table>        
</asp:Panel>
<asp:Panel ID="pnlSearch" runat="server" visible="False" align="center">
<asp:Table border="1" id="Search" runat="server">
            <asp:TableRow><asp:TableCell>Search:
                    <asp:DropDownList id="ddlSearch" AutoPostback="True" runat="server">
                  <asp:listitem value="MAC" Text="MAC" />
                  <asp:listitem value="Satellite" Text="Satellite" />
                  <asp:listitem value="SN" Text="SN" />
                  <asp:listitem value="IssuedTo" Text="Issued To" />
                  <asp:listitem value="Building" Text="Building" />
                  <asp:listitem value="Room" Text="Room" />
                  <asp:listitem value="Site" Text="Site" />
                  <asp:listitem value="IssuedStock" Text="Issued/Stock" />
                </asp:dropdownlist></asp:TableCell>
                        <asp:TableCell><asp:TextBox id="txtSearch" runat="server"/></asp:TableCell>
                  <asp:TableCell align="middle"><asp:Button id="btnSearchSubmit" runat="server"                        

               Text="Search" AutoPostBack="True" causesvalidation="false"></asp:Button></asp:TableCell>
            </asp:TableRow>
</asp:Table>
</asp:Panel>
<asp:Panel ID="pnlDatagrid" runat="server" visible="True" align="center">
<asp:Table border="1" id="SortGrid" runat="server" align="center">
                <asp:TableRow>
                    <asp:TableCell>Sort by:
                   <asp:DropDownList>
                    <asp:dropdownlist id="ddlSort" AutoPostBack="True" runat="server">
                  <asp:listitem value="MAC" Text="MAC" />
                  <asp:listitem value="Satellite" Text="Satellite" />
                  <asp:listitem value="SN" Text="SN" />
                  <asp:listitem value="IssuedTo" Text="Issued To" />
                  <asp:listitem value="Building" Text="Building" />
                  <asp:listitem value="Room" Text="Room" />
                  <asp:listitem value="Site" Text="Site" />
                  <asp:listitem value="IssuedStock" Text="Issued/Stock" />
                </asp:DropDownList>
                    </asp:TableCell>
                 </asp:TableRow>
</asp:Table>
<asp:Table border="1" id="FilterDataGrid" runat="server" align="center">
                <asp:TableRow>
                    <asp:TableCell>Filter Model by:
                            <asp:Dropdownlist id="ddlSatellite" AutoPostBack="True" runat="server">
                            <asp:ListItem Value="" text="All Satellites" Selected="True" />
                            <asp:ListItem Value="Satellite='ST-5001'" text="ST-5001" />
                            <asp:ListItem Value="Satellite='ST-5002'" text="ST-5002" />
                            <asp:ListItem Value="Satellite='ST-5003'" text="ST-5003" />
                            <asp:ListItem Value="Satellite='ST-5004'" text="ST-5004" />
                            <asp:ListItem Value="Satellite='ST-5019'" text="ST-5019" />
                            <asp:ListItem Value="Satellite='ST-5041'" text="ST-5041" />
                            <asp:ListItem Value="Satellite='ST-6290'" text="ST-6290" />
                            <asp:ListItem Value="Satellite='ST-7680'" text="ST-7680" />
                            </asp:DropDownList>
                    </asp:TableCell>
                    <asp:TableCell>Filter Site by:
                            <asp:dropdownlist id="ddlSite" AutoPostBack="True" runat="server">
                            <asp:ListItem Value="" text="All Sites" Selected="True" />
                            <asp:ListItem Value="Site='H1'" text="H1" />
                            <asp:ListItem Value="Site='H2'" text="H2" />
                            <asp:ListItem Value="Site='H3'" text="H3" />
                            <asp:ListItem Value="Site='H4'" text="H4" />
                            <asp:ListItem Value="Site='H5'" text="H5" />
                            <asp:ListItem Value="Site='HG'" text="HG" />
                            <asp:ListItem Value="Site='SDC'" text="SDC" />
                            </asp:DropDownList>
                    </asp:TableCell>
                 </asp:TableRow>  
</asp:Table>  
</asp:Panel>
                <asp:Datagrid
            id="dgVoip"
            runat="server"
            Width="95%"
            PagerStyle-HorizontalAlign="Center"
            PagerStyle-VerticalAligh="Top"
            PagerStyle-PageButtonCount="5"
            PagerStyle-Mode="NumericPages"
            PagerStyle-ForeColor="Black"
            PagerStyle-BorderStyle="Inset"
            PageSize="25"
            CssClass="DataGrid"
            AllowPaging="True"
            AllowSorting = "True"
            BorderWidth="5"
            BorderStyle="Double"
            BorderColor="Black"
            Font-text="White"
            Font-Size="10"
            Font-Name="Courier New"
            BackColor="#8080FF"
            Headerstyle-Font-Bold="True"
            Headerstyle-Font-Size="10"
            Headerstyle-Font-Name="Courier New"
            Headerstyle-BackColor="#aaaadd"
            HeaderStyle-Forecolor="Black"
            Cellspacing="0"
            Cellpadding="0"
            GridLines="Both"
            AlternatingItemStyle-BackColor="#eeeeee"
            AlternatingItemStyle-Font-Name="Courier New"
            AlternatingItemStyle-Font-Size="10"
            ItemStyle-Wrap = "False"
            AlternatingItemStyle-Wrap = "False"
            EditItemStyle-Wrap = "False"
            FooterStyle-Wrap = "False"
            SelectedItemStyle-Wrap = "False"
            AutogenerateColumns="False"
              OnEditcommand="DGVoip_EditCommand"
              OnCancelcommand="DGVoip_Cancel"
              OnUpdateCommand="DGVoip_UpdateCommand"
                OnPageIndexChanged="Page_Change">
      <Columns>
            <asp:EditCommandColumn
                  ButtonType="LinkButton"
                  UpdateText="Update"
                  CancelText="Cancel"
                  EditText="Edit">
            </asp:EditCommandColumn>
            <asp:BoundColumn
                  DataField="MAC"
                  HeaderText="MAC"
                  ReadOnly="True">
            </asp:BoundColumn>
            <asp:BoundColumn
                  DataField="Satellite"
                  HeaderText="Satellite">
            </asp:BoundColumn>
            <asp:BoundColumn
                  DataField="Model"
                  HeaderText="Model">
            </asp:BoundColumn>
            <asp:BoundColumn
                  DataField="PhoneNo"
                  HeaderText="Phone Number">
            </asp:BoundColumn>
            <asp:BoundColumn
                  DataField="SN"
                  HeaderText="Serial Number">
            </asp:BoundColumn>
            <asp:BoundColumn
                  DataField="IssuedTo"
                  HeaderText="Issued To">
            </asp:BoundColumn>
            <asp:BoundColumn
                  DataField="Building"
                  HeaderText="Building">
            </asp:BoundColumn>
            <asp:BoundColumn
                  DataField="Room"
                  HeaderText="Room">
            </asp:BoundColumn>
            <asp:BoundColumn
                  DataField="Site"
                  HeaderText="Site">
            </asp:BoundColumn>
            <asp:BoundColumn
                  DataField="IssuedStock"
                  HeaderText="Issued">
            </asp:BoundColumn>
</Columns>
</asp:DataGrid>
</form>
</div>
</body>
</html>
Mose_Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mose_Author Commented:
Cool, still having some tweaky issues, I posted the code on a page I started from scratch using the tidbit you gave me.  Heres the linkage, would appreciate any help you may be able to offer

http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/ASP_DOT_NET/Q_21369845.html
0
QingtongCommented:
Some issues:

1. If not Page.IsPostBack Then
          BindData
        Else
           BindData
     End If

Logically, it is not needed:

Change it to:

 If not Page.IsPostBack Then
          BindData
 End If

2. for each event handler, add the BindData. You acturally did, except for

Sub Page_Change(sender As Object, e As DataGridPageChangedEventArgs)
            dgvOIP.CurrentPageIndex = e.NewPageIndex
End Sub

3. the sequence which code behind to run for PostBack is Page_Load and then Event Handler
if you put BindData in Page_Load and Event Handler for PostBack, the it will run twice.

Check it out.

Qingtong


0
neil_richardsCommented:
From what I can tell, the event is firing, it's just not reading the values correctly within the filter function because you're querying the controls correctly.  

Line 27: If TypeOf Me.FilterDataGrid.Controls(iCount) Is System.Web.UI.WebControls.DropDownList Then

only identifies a single control: System.Web.UI.WebControls.TableRow

The problem you are encountering is because your function does not delve deep enough into the control tree.  

<asp:Table id="FilterDataGrid" runat="server" align="center" border="1">
      <asp:TableRow>
            <asp:TableCell>Filter Model by:
                   <asp:Dropdownlist id="ddlSatellite" AutoPostBack="True" runat="server">

Your code only identifies the 2nd level.  You need to go 2 more levels down.

I would suggest using recursion to evaluate the FilterDataGrid table, and when you identify a DropDownList add it to the filter string.  I'd type it up for you, but I'm heading out the door.  

Hope this helps.  
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

neil_richardsCommented:
Thats "querying the controls incorrectly".  The databinding and changing filters definitely fires (Qingtong's suggestion is correct, but it doesn't really fix your problem).  The problem is in your reading controls.  I don't know if you have step through debugging, if not, it could be tricky to figure out.  

Good luck
0
Mose_Author Commented:
RE: Qingtong

I made the changes you suggested.  I didn't realise I didn't add the databind call to the Page_Change sub.  I did this:
If not Page.IsPostBack Then
          BindData
        Else
           BindData
     End If
Simply to try and troubeshoot.

At anyrate that fixed the need to double click a page in order to go to the next page.  I'm still can't figure out the application of the filters.
0
neil_richardsCommented:
Mose,

The filters I mentioned really are the problem.  You're not drilling down far enough to correctly

you need something like:

If TypeOf Me.FilterDataGrid.Controls[i].Controls[j].Controls[k] is System.Web.UI.WebControls.DropDownList Then

Which means you need a total of three loops, where you currently have only one.
0
Mose_Author Commented:
Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30224: 'Is' expected.

Source Error:

Line 35:         For iCount = 0 To Me.FilterDataGrid.Controls.Count - 1
Line 36:            ' If TypeOf Me.FilterDataGrid.Controls(iCount) Is System.Web.UI.WebControls.DropDownList Then
Line 37:             If TypeOf Me.FilterDataGrid.Controls[i].Controls[j].Controls[k](iCount) Is System.Web.UI.WebControls.DropDownList Then  
Line 38:                 strTemp = CType(Me.FilterDataGrid.Controls(iCount), DropDownList).SelectedValue
Line 39:             If strTemp <> String.Empty Then

What are the variables i,j,k?  Can you give a more detailed sample?
0
neil_richardsCommented:
what I meant was that you would need to implement three loops (or analyse recursively).  I've attached a recursive version below.  It burrows down multiple layers to discover the values of each drop-down list.  Hope it helps.  I've tested it on my machine and it gives you the expected output.

Public Function Filter() As String
        Dim strFilter As String = String.Empty
        Dim strTemp As String = String.Empty
       
           Dim iCount as Integer = 0
                  
                  For iCount = 0 To Me.FilterDataGrid.Controls.Count - 1

                        Dim childControl as Control = FilterDataGrid.Controls(iCount)

                        If TypeOf childControl Is System.Web.UI.WebControls.DropDownList Then
                              strTemp = CType(Me.FilterDataGrid.Controls(iCount), DropDownList).SelectedValue
                        ElseIf childControl.Controls.Count > 0
                              strTemp = FilterRecurse(childControl)
                        End If
                        
                        If (strFilter <> String.Empty) AND (strTemp <> String.Empty)Then
                              strFilter = strFilter & " AND " & strTemp
                        Elseif (strTemp <> String.Empty)
                              strFilter = strTemp
                        End If
                              
                  Next
                  
                  lblFilter.Text=strFilter
        Return strFilter
    End Function
   
    Private Function FilterRecurse(currentControl as System.Web.UI.Control) as String
                  
                  Dim iCount as Integer = 0
                  
                  Dim strFilter As String = String.Empty
                  Dim strTemp As String = String.Empty
                  
                  For iCount = 0 To currentControl.Controls.Count - 1

                        Dim childControl as Control = currentControl.Controls(iCount)

                        If TypeOf childControl Is System.Web.UI.WebControls.DropDownList Then
                              strTemp = CType(childControl, DropDownList).SelectedValue
                              
                        ElseIf childControl.Controls.Count > 0
                              strTemp = FilterRecurse(childControl)
                        End If
                        
                        If (strFilter <> String.Empty) AND (strTemp <> String.Empty)Then
                              strFilter = strFilter & " AND " & strTemp
                        Elseif (strTemp <> String.Empty)
                              strFilter = strTemp
                        End If
                              
                  Next
                  
                  Return strFilter
                  
    End Function
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mose_Author Commented:
Thanks dude, works like a charm
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

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.