How to retrieve data from sql table into Datatable?
How to retrieve data from sql table into Datatable?
Rizwan1
Posts: 4Questions: 1Answers: 0
Hello! I want to display result from database to Jquery DataTable in asp.net . I have web service file 'Employee.asmx' which shows the data but still data is not displaying in DataTable. I am new to Jquery this is what i have tried so far:
Employee.asmx:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data.SqlClient;
using System.Web.Script.Serialization;
namespace WebApplication13
{
/// <summary>
/// Summary description for Employee
/// </summary>
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
[System.Web.Script.Services.ScriptService]
public class Employee : System.Web.Services.WebService
{
[WebMethod]
public void GetEmployee()
{
SqlConnection con = new SqlConnection("Data Source=localhost;Initial Catalog=Ajax;Persist Security Info=True;User ID=sa;Password=sa123");
List<Employ> employee = new List<Employ>();
SqlCommand cmd = new SqlCommand("spGetEmployees", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Employ employ = new Employ();
employ.Id = Convert.ToInt32(rdr["Id"]);
employ.FirstName = rdr["FirstName"].ToString();
employ.LastName = rdr["LastName"].ToString();
employ.Gender = rdr["Gender"].ToString();
employ.JobTitle = rdr["JobTitle"].ToString();
employ.WebSite = rdr["WebSite"].ToString();
employ.Salary = Convert.ToInt32(rdr["Salary"]);
employee.Add(employ);
}
JavaScriptSerializer js = new JavaScriptSerializer();
Context.Response.Write(js.Serialize(employee));
}
}
}
Employ.cs:
namespace WebApplication13
{
public class Employ
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Gender { get; set; }
public string JobTitle { get; set; }
public string WebSite { get; set; }
public int Salary { get; set; }
}
}
WebForm1.aspx:
<script type="text/javascript" >
$(document).ready(function () {
$.ajax({
method: 'post',
url: 'Employee.asmx/GetEmployee',
datatype: 'json',
sucess: function (response) {
$('#datatable').dataTable({
data: response,
columns: [
{ 'data': 'Id' },
{ 'data': 'FirstName' },
{ 'data': 'LastName' },
{ 'data': 'Gender' },
{ 'data': 'JobTitle' },
{ 'data': 'WebSite' },
{ 'data': 'Salary' }
]
});
}
});
});
</script>
</head>
<body>
<table id="datatable">
<thead>
<tr>
<th> Id </th>
<th> FirstName </th>
<th> LastName </th>
<th> Gender </th>
<th> JobTitle </th>
<th> WebSite </th>
<th> Salary </th>
</tr>
</thead>
<tbody>
</tbody>
</table>
</form>
</body>
</html>
This question has an accepted answers - jump to answer
Answers
Can you run the debugger on your page and let me know the debug code it gives you please.
Allan
I run script in chrome debugger but its didn't showed any error
You are expected to use DataTables' own debugger, through the link Allan has given you.
I copy that javascript snippet and run it on my browser it showed me error: Data table is not available on this page
You are expected to run the debugger from a page which has a DataTable on it.
I have resolved my issue
Thanks for your response!