Fetching the Data from db and displaying it in table format using Ajax, Jquery, Asp.Net MVC

Question

I have established the connection to database and i want to display all the details in the db in the view in the table format but am unable to do it as my new can any one help.

 public class EmployeeModel
 {
    public int EmpId { get; set; }

    public string EmpName { get; set; }

    public int Age { get; set; }

    public int Salary { get; set; }

 }

Controller :

 private static readonly string connectionString =    ConfigurationManager.ConnectionStrings["ConnStringDb1"].ConnectionString;
    public ActionResult GetUser()
    {
        return View();
    }

    public JsonResult GetAllUser(int EmpId)
    {
        List<EmployeeModel> employee = new List<EmployeeModel>();
        string query = string.Format("Select * From Employee", EmpId);
        SqlConnection connection = new SqlConnection(connectionString);
        {
            using (SqlCommand cmd = new SqlCommand(query, connection))
            {
                connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    employee.Add(
                        new EmployeeModel
                        {
                            EmpId = int.Parse(reader["EmpId"].ToString()),
                            EmpName = reader.GetValue(0).ToString(),
                            Age = int.Parse(reader["Age"].ToString()),
                            Salary = int.Parse(reader["Salary"].ToString())
                        }
                    );
                }
            }
            return Json(employee, JsonRequestBehavior.AllowGet);
        }
    }

View:

   @{
    ViewBag.Title = "Home Page";
    var EmployeeModel = (List<second_day.Models.EmployeeModel>)Model;
}
<button>Click me</button>

<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script type="text/javascript">
    $(document).ready(function () {
        $(':button').click(function () {
            GetEmployeeUsingAjax();
        });
    });

    function GetEmployeeUsingAjax() {
        var EmpId = 2;
        $.ajax({
            type: 'GET',
            url: '@Url.Action("GetAllUser")',
            data: { "EmpId": EmpId},
            dataType: 'json',
            success: function (data) {
                $.each(data, function (i, item) {
                    var rows = "<tr>"
                    + "<td>" + item.EmpID + "</td>"
                    + "<td>" + item.EmpName + "</td>"
                    + "<td>" + item.Age + "</td>"
                    + "<td>" + item.Salary + "</td>"
                    + "</tr>";
                    $('#tblProducts tbody').append(rows);
                });
            },
            error: function (emp) {
                alert('error');
            }
        });
    }
</script>
<table class="tblProducts">
    <thead>
        <tr class="headings" style="background-color:#4495d1;">
            <th>EmpId</th>
            <th>EmpName</th>
            <th>Age</th>
            <th>Salary</th>
         </tr>
</thead>
    <tbody >
    </tbody>
</table>

Can anyone suggest me solution

Data is fetched in console but not displaying in table format

it is hitting .I get error in console jquery-1.10.2.js:645 Uncaught TypeError: Cannot read property 'length' of undefined — Sundar Stalin, Dec 20 '16 at 11:37
Show your controller method (the error is almost certainly because `data.data` is undefined - and I'm guessing its should be just `data`) — , Dec 20 '16 at 11:41
@StephenMuecke check here http://stackoverflow.com/questions/41235212/how-to-access-and-display-the-data-from-database-using-ajax-jquery-asp-net-mvc/41236226#41236226 — Sundar Stalin, Dec 20 '16 at 11:42

score 1 · Answer 1 · answered Dec 20 '16 at 11:52

1

Your problem is this selector: $('#tblProducts tbody')

You have no table with that ID.

Changing it to $('.tblProducts tbody') or renaming your table to <table id="tblProducts"> should do the trick.

As a suggestion, move the DOM manipulation outside the loop, it will have better performance:

success: function (data) {
    var rows;
    $.each(data, function (i, item) {
        rows += "<tr>"
                  + "<td>" + item.EmpID + "</td>"
                  + "<td>" + item.EmpName + "</td>"
                  + "<td>" + item.Age + "</td>"
                  + "<td>" + item.Salary + "</td>"
             + "</tr>";
    });
    $('#tblProducts tbody').append(rows);
 },

answered Dec 20 '16 at 11:52

rabelloo

376
2
5

1

I need to display all the data has i have specified EmpId=2 i only get that id how to get all employee details – Sundar Stalin Dec 20 '16 at 11:56
If you want all data of all the employees then why you are passing EmpId=2 as parameter in GetAllUser function. – keerti Dec 20 '16 at 12:06
1

@KeertiSystematixInfotech thanks you opened my eye am new trying to learn on myself I am getting id as undefined when ID is displayed – Sundar Stalin Dec 20 '16 at 12:09
@sundar please check my code once again I have included $('.tblProducts tbody') in my code also I have removed parameters as you said you need to display all employee information. Than why you unanswred my reply. – keerti Dec 21 '16 at 04:38
@rabelloo this is the incomplete answer given. By wirting this code will not show details of all the employes – keerti Dec 21 '16 at 04:40
@KeertiSystematixInfotech There is no mention in the question that his problem is fetching only one while he wanted all employees. His problem is that the data fetched is not displayed on screen. Not only I explained why it happens, I fixed it for him AND gave a performance suggestion. Maybe I lack the insight of compatriots... Your code still won't work by the way – rabelloo Dec 21 '16 at 10:52

score 1 · Accepted Answer · edited Jun 02 '19 at 16:26

Controller :-

private static readonly string connectionString =  ConfigurationManager.ConnectionStrings["ConnStringDb1"].ConnectionString;
        public ActionResult GetUser()
        {
            return View();
        }

        public JsonResult GetAllUser()
        {
            List<EmployeeModel> employee = new List<EmployeeModel>();
            string query = string.Format("Select * From Employee");
            SqlConnection connection = new SqlConnection(connectionString);
            {
                using (SqlCommand cmd = new SqlCommand(query, connection))
                {
                    connection.Open();
                    SqlDataReader reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        employee.Add(
                            new EmployeeModel
                            {
                                EmpId = int.Parse(reader["EmpId"].ToString()),
                                EmpName = reader.GetValue(0).ToString(),
                                Age = int.Parse(reader["Age"].ToString()),
                                Salary = int.Parse(reader["Salary"].ToString())
                            }
                        );
                    }
                }
                return Json(employee, JsonRequestBehavior.AllowGet);
            }
        }

View:

function GetEmployeeUsingAjax() {           
        $.ajax({
            type: 'GET',
            url: '@Url.Action("GetAllUser")',
            data: { },
            dataType: 'json',
            success: function (data) {
    var rows;
    $.each(data, function (i, item) {
        rows += "<tr>"
                  + "<td>" + item.EmpID + "</td>"
                  + "<td>" + item.EmpName + "</td>"
                  + "<td>" + item.Age + "</td>"
                  + "<td>" + item.Salary + "</td>"
             + "</tr>";
    });
    $('#tblProducts tbody').append(rows);
 },
            error: function (emp) {
                alert('error');
            }
        });
    }
</script>
<table id="tblProducts">
    <thead>
        <tr class="headings" style="background-color:#4495d1;">
            <th>EmpId</th>
            <th>EmpName</th>
            <th>Age</th>
            <th>Salary</th>
         </tr>
</thead>
    <tbody >
    </tbody>
</table>

I removed parameter from query and function as you said you want data of all the employees — keerti, Dec 20 '16 at 12:18
I have no idea why an answer that does not explain what was wrong or what has been changed, appropriates code from another answer and manages to still have errors gets accepted... @SundarStalin please be aware that you will need to rename your table as suggested in my answer for this code to work — rabelloo, Dec 20 '16 at 13:28
@sundar please check my code once again I have included $('.tblProducts tbody') in my code also I have removed parameters as you said you need to display all employee information. Than why you unanswred my reply. — keerti, Dec 21 '16 at 04:38
@KeertiSystematixInfotech both of you gave correct answer only one option to accept thats why ok i go with you as you provided detailed answer — Sundar Stalin, Dec 21 '16 at 04:54

Fetching the Data from db and displaying it in table format using Ajax, Jquery, Asp.Net MVC

2 Answers2