I am trying to display records search to the gridview but nothing happens instead my browser hangs
please help
Private Sub BindDummyRow()
Dim dummy As DataTable = New DataTable()
dummy.Columns.Add("itemcode")
dummy.Columns.Add("stockitem")
dummy.Columns.Add("saleprice")
dummy.Rows.Add()
gvCustomers.DataSource = dummy
gvCustomers.DataBind()
'sample()
End Sub
<System.Web.Services.WebMethod()>
Public Shared Function GetCustomers(ByVal searchTerm As String, ByVal pageIndex As Integer) As String
Dim query As String = "[GetCustomers_Pager2022]"
Dim cmd As SqlCommand = New SqlCommand(query)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@SearchTerm", searchTerm)
cmd.Parameters.AddWithValue("@PageIndex", pageIndex)
cmd.Parameters.AddWithValue("@PageSize", 15)
cmd.Parameters.Add("@RecordCount", SqlDbType.Int, 4).Direction = ParameterDirection.Output
Return GetData(cmd, pageIndex).GetXml()
End Function
Private Shared Function GetData(ByVal cmd As SqlCommand, ByVal pageIndex As Integer) As DataSet
Dim strConnString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(strConnString)
Using sda As SqlDataAdapter = New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using ds As DataSet = New DataSet()
sda.Fill(ds, "Customers")
Dim dt As DataTable = New DataTable("Pager")
dt.Columns.Add("PageIndex")
dt.Columns.Add("PageSize")
dt.Columns.Add("RecordCount")
dt.Rows.Add()
dt.Rows(0)("PageIndex") = pageIndex
dt.Rows(0)("PageSize") = 15
dt.Rows(0)("RecordCount") = cmd.Parameters("@RecordCount").Value
ds.Tables.Add(dt)
Return ds
End Using
End Using
End Using
End Function
CREATE PROCEDURE [dbo].[GetCustomers_Pager2022]
@PageIndex INT = 1
,@PageSize INT = 50
,@RecordCount INT OUTPUT,
@SearchTerm nvarchar(200)
AS
BEGIN
SET NOCOUNT ON;
SELECT IDENTITY(INT,1,1) AS RowNumber
,itemcode
,[stockitem]
,[saleprice]
INTO #Results
FROM stockcodes
WHERE ([stockitem] LIKE @SearchTerm or [stockitem] LIKE '%'+@SearchTerm+'%' or [stockitem] LIKE '%' + @SearchTerm or [stockitem] LIKE @SearchTerm + '%') OR @SearchTerm = '' OR @SearchTerm = ''
SELECT @RecordCount = COUNT(*)
FROM #Results
SELECT * FROM #Results
WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
DROP TABLE #Results
END
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Me.BindGrid()
BindDummyRow()
gvCustomers.DataBind()
End If
End Sub
<link rel="stylesheet" href="../Scripts/Bootstrap3.3.2.css" />
<script type="text/javascript" src="../Scripts/Jquery1.9.1.min.js"></script>
<script type="text/javascript" src="../Scripts/bootstrap.min.js"></script>
<script src="../Scripts/ASPSnippets_Pager.min.js"></script>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server">
<script type="text/javascript">
$(function () {
GetCustomers(1);
$("body").on("keyup", "[id*=txtSearch]", function () {
GetCustomers(parseInt(1));
});
$("body").on("click", ".Pager .page", function () {
GetCustomers(parseInt($(this).attr('page')));
});
});
function SearchTerm() {
return jQuery.trim($("[id*=txtSearch]").val());
};
function GetCustomers(pageIndex) {
$.ajax({
type: "POST",
url: "PrintCodes.aspx/GetCustomers",
data: '{searchTerm: "' + SearchTerm() + '", pageIndex: ' + pageIndex + '}',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: OnSuccess,
failure: function (response) {
alert(response.d);
},
error: function (response) {
alert(response.d);
}
});
}
var row;
function OnSuccess(response) {
var xmlDoc = $.parseXML(response.d);
var xml = $(xmlDoc);
var customers = xml.find("Customers");
if (row == null) {
row = $("[id*=gvCustomers] tr:last-child").clone(true);
}
var footer = $("[id*=gvCustomers] tr:last-child").clone(true);
$("[id*=gvCustomers] tr").not($("[id*=gvCustomers] tr:first-child")).remove();
if (customers.length > 0) {
$.each(customers, function () {
var customer = $(this);
$("td", row).eq(0).html($(this).find("itemcode").text());
$("td", row).eq(1).html($(this).find("stockitem").text());
$("td", row).eq(2).html($(this).find("saleprice").text());
$("[id*=gvCustomers]").append(row);
row = $("[id*=gvCustomers] tr:last-child").clone(true);
});
$("[id*=gvCustomers]").append(footer);
var pager = xml.find("Pager");
$(".Pager").ASPSnippets_Pager({
ActiveCssClass: "current",
PagerCssClass: "pager",
PageIndex: parseInt(pager.find("PageIndex").text()),
PageSize: parseInt(pager.find("PageSize").text()),
RecordCount: parseInt(pager.find("RecordCount").text())
});
$(".Name").each(function () {
var searchPattern = new RegExp('(' + SearchTerm() + ')', 'ig');
$(this).html($(this).text().replace(searchPattern, "<span class = 'highlight'>" + SearchTerm() + "</span>"));
});
} else {
var empty_row = row.clone(true);
$("td:first-child", empty_row).attr("colspan", $("td", row).length);
$("td:first-child", empty_row).attr("align", "center");
$("td:first-child", empty_row).html("No records found for the search criteria.");
$("td", empty_row).not($("td:first-child", empty_row)).remove();
$("[id*=SearchGrid]").append(empty_row);
}
};
</script>
<table>
<tr>
<td>Name:<asp:TextBox ID="txtName" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td>Country:<asp:TextBox ID="txtCountry" runat="server"></asp:TextBox></td>
</tr>
<tr>
<td><asp:Button ID="btnInsert" runat="server" Text="Insert" OnClick="Insert" /></td>
</tr>
</table>
<table class="auto-style1">
<tr>
<td class="auto-style7">
Search items
<asp:TextBox ID="txtSearch" runat="server" Width="183px"></asp:TextBox>
</td>
<td> </td>
</tr>
</table>
<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:BoundField HeaderText="Itemcode" DataField="Itemcode" />
<asp:BoundField HeaderText="stockitem" DataField="stockitem" />
<asp:BoundField HeaderText="SalePrice" DataField="Saleprice" />
<asp:TemplateField>
<HeaderTemplate>
Send Iterm
</HeaderTemplate>
<ItemTemplate>
<asp:LinkButton ID="lnkSend" Text="Send" runat="server" OnClick="Send"></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<HeaderTemplate>
<asp:CheckBox ID="chkAll" runat="server" OnCheckedChanged="chkAll_CheckedChanged" AutoPostBack="true" />
</HeaderTemplate>
<ItemTemplate>
<asp:CheckBox ID="chk" runat="server" OnCheckedChanged="chk_CheckedChanged" AutoPostBack="true" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<table class="auto-style1">
<tr>
<td class="auto-style4">
<asp:Button ID="Button2" runat="server" Text="Print" />
</td>
<td> </td>
</tr>
<tr>
<td class="auto-style4"> </td>
<td> </td>
</tr>
</table>
<br />
<asp:Panel runat="server" ID="pnlDetails" Visible="false">
<table>
<tr>
<td><asp:Label ID="lblName" runat="server" Text=""></asp:Label></td>
<td><asp:Label ID="lblCountry" runat="server" Text=""></asp:Label></td>
<td><asp:Image ID="imgBarCode" runat="server" /></td>
</tr>
</table>
</asp:Panel>
</asp:Content>
