Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2005-03-30
9
Medium Priority
?
282 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_
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Industry Leaders: 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!

Question has a verified solution.

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

I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
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…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses
Course of the Month13 days, 19 hours left to enroll

581 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