?
Solved

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

Posted on 2005-03-30
9
Medium Priority
?
279 Views
Last Modified: 2008-02-20
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>
0
Comment
Question by:Mose_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 

Author Comment

by:Mose_
ID: 13662698
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
 
LVL 5

Expert Comment

by:Qingtong
ID: 13663139
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
 
LVL 2

Expert Comment

by:neil_richards
ID: 13663161
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Expert Comment

by:neil_richards
ID: 13663192
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
 

Author Comment

by:Mose_
ID: 13669483
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
 
LVL 2

Expert Comment

by:neil_richards
ID: 13669619
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
 

Author Comment

by:Mose_
ID: 13670615
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
 
LVL 2

Accepted Solution

by:
neil_richards earned 2000 total points
ID: 13670909
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
 

Author Comment

by:Mose_
ID: 13678195
Thanks dude, works like a charm
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month12 days, 4 hours left to enroll

752 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