Hi
I have a gridview that fetches data from database its working fine. But i am not able to do paging for the gridview as it has to appear within an iframe. Here is my code of the cs as well as the aspx page. Please help me to solve this issue. Thanks in advance.
search.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.Services;
using System.IO;
public partial class search : System.Web.UI.Page
{
string con = System.Configuration.ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Search_Click(object sender, EventArgs e)
{
gridview1.DataSource = null;
gridview1.DataBind();
txtname1.Text = Request.Form["txtno"];
string sp = txtname1.Text;
string query = "SELECT p.Pers_FirstName+p.Pers_LastName as Name, ph.Phon_Number FROM Person p, Phone ph, Phonelink pl Where p.Pers_PersonId = pl.PLink_RecordID and pl.PLink_PhoneId = ph.Phon_PhoneId and Phon_Number = RTRIM('" + sp + "')";
gridview1.DataSource = GetDataTable(con, query);
gridview1.Visible = true;
gridview1.AllowPaging = true;
gridview1.DataBind();
}
public DataTable GetDataTable(String connectionString, String query)
{
DataTable dataTable = new DataTable();
try
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command = new SqlCommand(query, connection))
{
using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
{
dataAdapter.Fill(dataTable);
}
}
}
}
catch
{
}
return dataTable;
}
[WebMethod]
public static List<string> GetAutoCompleteData(string Phon_Number)
{
List<string> result = new List<string>();
using (SqlConnection con = new SqlConnection("Data Source=<abcdgd>;Initial Catalog=<abbdbdb>;Integrated Security=True;User ID=<gsfadg>;Password=<dadfhags>"))
{
using (SqlCommand cmd = new SqlCommand("select DISTINCT Phon_Number from Phone where Phon_Number LIKE '%'+@SearchText+'%'", con))
{
con.Open();
cmd.Parameters.AddWithValue("@SearchText", Phon_Number);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
result.Add(dr["Phon_Number"].ToString());
}
return result;
}
}
}
[WebMethod]
public static List<string> GetNames(string Pers_FirstName)
{
List<string> result = new List<string>();
string query = string.Format("SELECT Pers_FirstName+' '+Pers_LastName FROM Person WHERE Pers_FirstName+Pers_LastName LIKE '%{0}%'", Pers_FirstName);
//Note: you can configure Connection string in web.config also.
using (SqlConnection conn = new SqlConnection("Data Source=<abcdgd>;Initial Catalog=<abbdbdb>;Integrated Security=True;User ID=<gsfadg>;Password=<dadfhags>"))
{
using (SqlCommand cmd = new SqlCommand(query, conn))
{
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
result.Add(reader.GetString(0));
}
}
}
return result;
}
protected void gridview1_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
DataTable dt = new DataTable();
gridview1.PageIndex = e.NewPageIndex;
gridview1.DataSource = dt;
gridview1.DataBind();
}
protected void Searchname_Click(object sender, EventArgs e)
{
gridview1.DataSource = null;
gridview1.DataBind();
txtname1.Text = Request.Form["txtname"];
string sp = txtname1.Text;
sp = sp.Replace(" ", "");
txtname1.Text = sp.ToString();
SqlConnection co = new SqlConnection(con);
SqlDataReader rdr = null;
string costr = "SELECT p.Pers_FirstName+p.Pers_LastName as Name, ph.Phon_Number FROM Person p, Phone ph, Phonelink pl Where p.Pers_PersonId = pl.PLink_RecordID and pl.PLink_PhoneId = ph.Phon_PhoneId and UPPER(RTRIM(LTRIM(Pers_FirstName))) + UPPER(RTRIM(LTRIM(Pers_LastName)))Like UPPER('%" + sp + "%')";
co.Open();
SqlCommand cmd = new SqlCommand(costr, co);
rdr = cmd.ExecuteReader();
gridview1.DataSource = rdr;
gridview1.DataBind();
}
}
search.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="search.aspx.cs" Inherits="search" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<script src="Scripts/myjava.js"></script>
<script src="Scripts/jqury.js"></script>
<script src="Scripts/forauto.js"></script>
<link href="stylesheet/mystylesheet.css" rel="stylesheet" />
<script type="text/javascript">
$(document).ready(function () {
SearchText();
});
function SearchText() {
$(".autosuggest").autocomplete({
source: function (request, response) {
$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
url: "search.aspx/GetAutoCompleteData",
data: "{'Phon_Number':'" + document.getElementById('txtno').value + "'}",
dataType: "json",
success: function (data) {
response(data.d);
},
error: function (result) {
alert("Error");
}
});
}
});
}
</script>
<script type="text/javascript">
$(document).ready(function () {
$("#txtno").keypress(function (e) {
if (e.which != 8 && e.which != 0 && (e.which < 48 || e.which > 57)) {
$("#errmsg").html("Digits Only").show().fadeOut("slow");
return false;
}
});
});
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
Search(number) : <input type="text" name="txtno" id="txtno" class="autosuggest" />
<asp:Button ID="Searchno" runat="server" Text="Check" OnClick="Search_Click" />
Search(name) : <input type="text" id="txtname" name="txtname" class="suggest" />
<asp:Button ID="Searchname" runat="server" Text="Search" OnClick="Searchname_Click"/><br />
<asp:TextBox ID="txtname1" runat="server" Visible="false" />
<script type="text/javascript">
$(document).ready(function () {
Searchname();
});
function Searchname() {
$(".suggest").autocomplete({
source: function (request, response) {
$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
url: "search.aspx/GetNames",
data: "{'Pers_FirstName':'" + document.getElementById('txtname').value + "'}",
dataType: "json",
success: function (data) {
response(data.d);
},
error: function (result) {
alert("Error");
}
});
}
});
}
</script>
<br />
<br />
<asp:GridView ID="gridview1" runat="server" OnPageIndexChanging="gridview1_PageIndexChanging">
<Columns>
<asp:HyperLinkField
DataTextField="Phon_Number"
HeaderText="Dial"
DataNavigateUrlFields="Phon_Number"
DataNavigateUrlFormatString="Dial.aspx?no={0}" />
</Columns>
</asp:GridView>
</div>
</form>
</body>
</html>