Hi super experts,
I go this code from ASPSnippets.
That code works except it only exports the current record.
We would like it to export all the records.
As a result, I made some changes to the code.
Now, it exports all records.
However, the Sorting and Search do not work.
Could you please help?
As usual, much appreciated.
<%@ Page Language="VB" AutoEventWireup="false" EnableEventValidation="false" CodeFile="default.aspx.vb" Inherits="Main" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>
<script type="text/javascript" src="Scripts/jquery.tablesorter-2.0.3.js"></script>
<link type="text/css" rel="stylesheet" href="Scripts/style1.css" />
<script type="text/javascript">
jQuery(document).ready(function () {
$("#Gridview2").tablesorter({
debug: false,
widgets: ['zebra'],
sortList: [[0, 0]]
});
});
</script>
</head>
<body>
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<div id="Frame" style="width: 100%; max-height: 500px; margin: 0px; padding: 0px;">
<div id="Separation" style="width: 100%; height: 5px; background-color: #000000;"></div>
<div id="Content" style="min-height: 200px; overflow: auto">
<table width="100%" height="300px" border="0px" cellpadding="0" cellspacing="0"
frame="void">
<tr>
<td></td>
<td>Search:
<asp:TextBox ID="txtSearch"
runat="server" OnTextChanged="txtSearch_TextChanged" Height="20px"
Width="208px" /> | <asp:Button ID="btnExport" runat="server" Text="Export To Excel" OnClick = "ExportToExcel" /> </td>
<td></td>
</tr>
<tr>
<td> </td>
<td valign="top">
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate> <asp:GridView ID="Gridview2" CssClass="mydatagrid yui" PagerStyle-CssClass="pager" HeaderStyle-CssClass="header" RowStyle-CssClass="rows" runat="server" AutoGenerateColumns="False" AllowPaging="True"
AllowSorting="True" OnPageIndexChanging="OnPageIndexChanging" Width="540px" PageSize="5" OnRowDataBound="OnRowDataBound">
<Columns>
<asp:BoundField DataField="SpeakerId" HeaderText="Id" SortExpression="SpeakerId"
ItemStyle-Width="40px" ItemStyle-HorizontalAlign="Center" InsertVisible="False" ReadOnly="True">
<ItemStyle HorizontalAlign="Center" Width="40px" />
</asp:BoundField>
<asp:BoundField DataField="SpeakerName" HeaderText="Speaker Name" SortExpression="SpeakerName" />
<asp:BoundField DataField="ClientName" HeaderText="Client Name" SortExpression="ClientName" />
<asp:BoundField DataField="MinistryName" HeaderText="Ministry" ItemStyle-Width="130px" SortExpression="MinistryName">
<ItemStyle Width="130px" />
</asp:BoundField>
<asp:BoundField DataField="dateAdded" HeaderText="Date Added" ItemStyle-Width="130px" SortExpression="dateAdded">
<ItemStyle Width="130px" />
</asp:BoundField>
</Columns>
</asp:GridView>
</ContentTemplate>
<Triggers>
<asp:AsyncPostBackTrigger ControlID="txtSearch" EventName="TextChanged" />
</Triggers>
</asp:UpdatePanel>
</td>
<td></td>
</tr>
</table>
</div>
</div>
</form>
</body>
</html>
VB:
Partial Class Main
Inherits System.Web.UI.Page
Private SearchString As String = ""
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
txtSearch.Attributes.Add("onkeyup", " setTimeout(function () { __doPostBack('<%=txtSearch.ClientID %>','') }, 100);")
If Not IsPostBack Then
BindData()
End If
End Sub
Protected Sub txtSearch_TextChanged(ByVal sender As Object, ByVal e As EventArgs)
SearchString = txtSearch.Text
txtSearch.Text = SearchString
txtSearch.Focus()
End Sub
Protected Sub OnRowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs)
If e.Row.RowType = DataControlRowType.DataRow Then
If e.Row.RowType = DataControlRowType.DataRow Then
e.Row.Cells(1).Text = Regex.Replace(e.Row.Cells(1).Text, txtSearch.Text.Trim(),
Function(match As Match) String.Format("<span class='highlight'>{0}</span>", match.Value),
RegexOptions.IgnoreCase)
End If
If e.Row.RowType = DataControlRowType.DataRow Then
e.Row.Cells(2).Text = Regex.Replace(e.Row.Cells(2).Text, txtSearch.Text.Trim(),
Function(match As Match) String.Format("<span class='highlight'>{0}</span>", match.Value),
RegexOptions.IgnoreCase)
End If
End If
End Sub
Private Sub BindData()
'Dim strSearch As String
'Protect against SQL Injection
' strSearch = Replace(searchBox.Text, "'", "''", 1, -1, 1)
Dim strQuery As String
If txtSearch.Text = "" Then
strQuery = "SELECT SpeakerId, SpeakerName, MinistryName, ClientName, dateAdded FROM Speakers"
Else
strQuery = "SELECT SpeakerId, SpeakerName, MinistryName, ClientName, dateAdded FROM Speakers" &
"WHERE SpeakerName LIKE '%' + @strSearch + '%' " &
"OR ClientName = LIKE '%' + @strSearch + '%' "
End If
Dim cmd As New SqlCommand(strQuery)
cmd.Parameters.AddWithValue("@strSearch", txtSearch.Text)
Gridview2.DataSource = GetData(cmd)
Gridview2.DataBind()
'Showing Numbers in Label
Dim iTotalRecords As Integer = DirectCast(Gridview2.DataSource, DataTable).Rows.Count
Dim iEndRecord As Integer = Gridview2.PageSize * (Gridview2.PageIndex + 1)
Dim iStartsRecods As Integer = iEndRecord - Gridview2.PageSize
If iEndRecord > iTotalRecords Then
iEndRecord = iTotalRecords
End If
If iStartsRecods = 0 Then
iStartsRecods = 1
End If
If iEndRecord = 0 Then
iEndRecord = iTotalRecords
End If
returnLabel.Text = "Total Records Found: <strong>" & iTotalRecords.ToString() & "</strong>"
End Sub
Private Function GetData(ByVal cmd As SqlCommand) As DataTable
Dim strConnString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(strConnString)
Using sda As New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using dt As New DataTable()
sda.Fill(dt)
Return dt
End Using
End Using
End Using
End Function
Protected Sub OnPageIndexChanging(sender As Object, e As GridViewPageEventArgs)
Gridview2.PageIndex = e.NewPageIndex
Me.BindData()
End Sub
Protected Sub ExportToExcel(sender As Object, e As EventArgs)
Response.Clear()
Response.Buffer = True
Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls")
Response.Charset = ""
Response.ContentType = "application/vnd.ms-excel"
Using sw As New StringWriter()
Dim hw As New HtmlTextWriter(sw)
'To Export all pages
Gridview2.AllowPaging = False
BindData()
'Dim dv As DataView = CType(dsGridview.Select(DataSourceSelectArguments.Empty), DataView)
'Dim dt As DataTable = dv.ToTable()
Gridview2.HeaderRow.BackColor = Color.White
For Each cell As TableCell In Gridview2.HeaderRow.Cells
cell.BackColor = Gridview2.HeaderStyle.BackColor
Next
For Each row As GridViewRow In Gridview2.Rows
row.BackColor = Color.White
For Each cell As TableCell In row.Cells
If row.RowIndex Mod 2 = 0 Then
cell.BackColor = Gridview2.AlternatingRowStyle.BackColor
Else
cell.BackColor = Gridview2.RowStyle.BackColor
End If
cell.CssClass = "textmode"
Next
Next
Gridview2.RenderControl(hw)
'style to format numbers to string
Dim style As String = "<style> .textmode { mso-number-format:\@; } </style>"
Response.Write(style)
Response.Output.Write(sw.ToString())
Response.Flush()
Response.[End]()
End Using
End Sub
Public Overrides Sub VerifyRenderingInServerForm(control As Control)
' Verifies that the control is rendered
End Sub
End Class
I am using jQuery tablesorter plugin.
