My Gridview won't page or sort. By this I mean that the data in the GridView doesn't change when I try to sort by a column or when I try to page through the GridView.
I have it inside an UpdatePanel on a user control (.ascx
). The code below works fine when I try it outside of the user control.
Here's my code:
<asp:GridView runat="server" ID="grdStats" AutoGenerateColumns="false" Width="100%" AllowSorting="true"
AllowPaging="true" PageSize="20" PagerSettings-Mode="NumericFirstLast" PagerSettings-Position="TopAndBottom">
<Columns>
<asp:BoundField DataField="campaignname" HeaderText="Campaign Name" SortExpression="campaignname" />
<asp:BoundField DataField="site_name" HeaderText="Outlet" SortExpression="site_name" />
<asp:BoundField DataField="filename" HeaderText="Media" SortExpression="filename" />
<asp:BoundField DataField="playinterval" HeaderText="Play Interval" SortExpression="playinterval" />
</Columns>
</asp:GridView>
Here's the backend:
Protected Sub grdStats_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles grdStats.PageIndexChanging
grdStats.PageIndex = e.NewPageIndex
GetCampaignData()
End Sub
Protected Sub grdStats_Sorting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs) Handles grdStats.Sorting
If e.SortExpression = ViewState("sortExpr") Then
ViewState("sortExpr") = e.SortExpression & " desc"
Else
ViewState("sortExpr") = e.SortExpression
End If
GetCampaignData()
End Sub
' ######## POPULATE DATA #########
Private Sub GetCampaignData(Optional ByVal CompanyID As Integer = 0)
Dim sql As String = Nothing
' Check user access level.
If Session("userlevel") = 1 Then
' Current user is admin.
' If a company has been filtered, get it's data, else, get all campaign data.
If CompanyID <> 0 Then
sql = "SELECT" & vbCrLf & _
" a.campaignid," & vbCrLf & _
" a.companyid," & vbCrLf & _
" a.campaignname," & vbCrLf & _
" a.filename," & vbCrLf & _
" startdate," & vbCrLf & _
" enddate," & vbCrLf & _
" a.playinterval," & vbCrLf & _
" a.ActiveStatus," & vbCrLf & _
" b.*," & vbCrLf & _
" (SELECT COUNT(*)" & vbCrLf & _
" FROM campaignstats AS c" & vbCrLf & _
" WHERE DAY(date_played) = DAY(CURRENT_DATE)" & vbCrLf & _
" AND c.campaignid = a.campaignid" & vbCrLf & _
" ) AS today," & vbCrLf & _
" (SELECT COUNT(*)" & vbCrLf & _
" FROM campaignstats AS d" & vbCrLf & _
" WHERE date_played > CURRENT_DATE - INTERVAL 7 DAY" & vbCrLf & _
" AND d.campaignid = a.campaignid" & vbCrLf & _
" ) AS past_week," & vbCrLf & _
" (SELECT COUNT(*)" & vbCrLf & _
" FROM campaignstats AS e" & vbCrLf & _
" WHERE e.campaignid = a.campaignid" & vbCrLf & _
" ) AS playcount" & vbCrLf & _
"FROM campaigns AS a" & vbCrLf & _
"INNER JOIN companies AS b ON a.companyid = b.companyid" & vbCrLf & _
"WHERE a.ActiveStatus = 1" & vbCrLf & _
"AND a.companyid = " & CompanyID
Else
sql = "SELECT" & vbCrLf & _
" a.campaignid," & vbCrLf & _
" a.companyid," & vbCrLf & _
" a.campaignname," & vbCrLf & _
" a.filename," & vbCrLf & _
" startdate," & vbCrLf & _
" enddate," & vbCrLf & _
" a.playinterval," & vbCrLf & _
" a.ActiveStatus," & vbCrLf & _
" b.*," & vbCrLf & _
" (SELECT COUNT(*)" & vbCrLf & _
" FROM campaignstats AS c" & vbCrLf & _
" WHERE DAY(date_played) = DAY(CURRENT_DATE)" & vbCrLf & _
" AND c.campaignid = a.campaignid" & vbCrLf & _
" ) AS today," & vbCrLf & _
" (SELECT COUNT(*)" & vbCrLf & _
" FROM campaignstats AS d" & vbCrLf & _
" WHERE date_played > CURRENT_DATE - INTERVAL 7 DAY" & vbCrLf & _
" AND d.campaignid = a.campaignid" & vbCrLf & _
" ) AS past_week," & vbCrLf & _
" (SELECT COUNT(*)" & vbCrLf & _
" FROM campaignstats AS e" & vbCrLf & _
" WHERE e.campaignid = a.campaignid" & vbCrLf & _
" ) AS playcount" & vbCrLf & _
"FROM campaigns AS a" & vbCrLf & _
"INNER JOIN companies AS b ON a.companyid = b.companyid" & vbCrLf & _
"WHERE a.ActiveStatus = 1"
End If
Else
' User is not an admin.
sql = "SELECT" & vbCrLf & _
" a.campaignid," & vbCrLf & _
" a.companyid," & vbCrLf & _
" a.campaignname," & vbCrLf & _
" a.filename," & vbCrLf & _
" startdate," & vbCrLf & _
" enddate," & vbCrLf & _
" a.playinterval," & vbCrLf & _
" a.ActiveStatus," & vbCrLf & _
" b.*," & vbCrLf & _
" (SELECT COUNT(*)" & vbCrLf & _
" FROM campaignstats AS c" & vbCrLf & _
" WHERE DAY(date_played) = DAY(CURRENT_DATE)" & vbCrLf & _
" AND c.campaignid = a.campaignid" & vbCrLf & _
" ) AS today," & vbCrLf & _
" (SELECT COUNT(*)" & vbCrLf & _
" FROM campaignstats AS d" & vbCrLf & _
" WHERE date_played > CURRENT_DATE - INTERVAL 7 DAY" & vbCrLf & _
" AND d.campaignid = a.campaignid" & vbCrLf & _
" ) AS past_week," & vbCrLf & _
" (SELECT COUNT(*)" & vbCrLf & _
" FROM campaignstats AS e" & vbCrLf & _
" WHERE e.campaignid = a.campaignid" & vbCrLf & _
" ) AS playcount" & vbCrLf & _
"FROM campaigns AS a" & vbCrLf & _
"INNER JOIN companies AS b ON a.companyid = b.companyid" & vbCrLf & _
"WHERE a.ActiveStatus = 1" & vbCrLf & _
"AND a.companyid = " & CInt(Session("companyid"))
End If
grdCampaigns.DataSource = get_data(sql)
grdCampaigns.DataBind()
For i As Integer = 0 To grdCampaigns.Rows.Count - 1
grdCampaigns.Rows(i).Cells(2).Text = Microsoft.VisualBasic.Left(grdCampaigns.Rows(i).Cells(2).Text, 10)
grdCampaigns.Rows(i).Cells(3).Text = Microsoft.VisualBasic.Left(grdCampaigns.Rows(i).Cells(3).Text, 10)
Next
End Sub
Public Function get_data(ByVal SQLStatement As String) As DataView
'Populates the datatable
Dim dt As Data.DataTable
dt = db.GetDataTable(SQLStatement)
Dim dv As System.Data.DataView = New System.Data.DataView(dt)
If Not ViewState("sortExpr") Is Nothing Then
dv.Sort = ViewState("sortExpr")
Else
dv = dt.DefaultView
End If
Session("dv5") = dv
Return dv
End Function
Any help would be greatly appreciated.
My apologies the code I pasted in the question, although correct for it's purpose, was not the right code relevant to this question. That said, I'd like to thank you Icarus
for taking the time to look over my code.
The paging/sorting works. What I did to fix it was to replace the call to GetCampaignData()
within the PageIndexChanging
and Sorting
event handlers to the SQL code and data binding methods required to give me the results I needed.