Server responded with a status of 500 (Internal Server Error) - MVC C#

Question

I'm getting the Internal Server Error when I tried to get the details by sending ajax request from the Jquery Datatable to the Controller. Even in the controller the code works fine and the result too. But I'm getting this error in both "HTTPGET" and "HTTPPOST" request.

This is my code.

Employee.cshtml

Dependencies

    <script src="~/Scripts/jquery-3.4.1.js"></script>
<script src="~/Scripts/jquery.validate.js"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.js"></script>
<script src="~/Scripts/bootstrap.js"></script>
<script src="~/Scripts/DataTables/DataTables-1.13.4/js/jquery.dataTables.js" defer="defer"></script>
<script src="~/Scripts/DataTables/DataTables-1.13.4/js/dataTables.bootstrap.js" defer="defer"></script>

    <script type="text/javascript">
    $(document).ready(function () {
        debugger;
        $('#empTable').DataTable({
            "columnDefs": [
                { "searchable": true,  targets: "_all" },
                { "className": "text-center custom-middle-align", targets: "_all" }
            ],
            "processing": true,
            "serverSide": true,
            "ajax":
            {
                "url": "/Employee/GetEmployeeDetails",
                "type": "POST",
                "dataType": "json",
                "dataSrc": function (response) {
                    debugger;
                    resultObj = $.parseJSON(response.data)
                    return resultObj.data;
                },
                "error": function (jqXhr, textStatus, errorMessage) { // error callback 
                    console.log('Error: ' + errorMessage + '    request.statusText:' + jqXhr.statusText);
                }
            }
        });

    });
</script>

Employee.cs

[HttpPost]
    public JsonResult GetEmployeeDetails()
    {
        connection();
        SqlCommand cmd = new SqlCommand("GetEmployeeDetails", con);
        cmd.CommandType = CommandType.StoredProcedure;
        con.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        DataTable dt = new DataTable();
        dt.Load(dr);

        //Note I have tried both SqlDataAdapter and SqlDataReader
        //SqlDataAdapter da = new SqlDataAdapter();
        //da.Fill(dt);

        con.Close();
        return Json(dt);
    }

Answer 1

I'm getting the Internal Server Error when I tried to get the details by sending ajax request from the Jquery Datatable to the Controller. Even in the controller the code works fine and the result too. But I'm getting this error in both "HTTPGET" and "HTTPPOST" request.

Actually, you are getting the error as expected because, you are directly binding your dataTable value into Json data without any serialization consequently getting following error.

Error:

Solution:

In order to get rid of your serialization failure which causing 500 error, you need to parse your datatable value. Let's have a look how we can do that.

        [HttpPost]
        public JsonResult GetEmployeeDetails()
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {

                SqlCommand cmd = new SqlCommand("GetEmployeeDetails", con);
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();
                SqlDataReader dr = cmd.ExecuteReader();
                DataTable dt = new DataTable();
                dt.Load(dr);

                //Note I have tried both SqlDataAdapter and SqlDataReader
                //SqlDataAdapter da = new SqlDataAdapter();
                //da.Fill(dt);

                con.Close();
                string serializeObject = JsonConvert.SerializeObject(dt);
                return Json(serializeObject);
            }
        }

Output:

However, if you would like to parse into your class object in that scenario, you would need additional deserialization as following:

string serializeObject = JsonConvert.SerializeObject(dt);
var desrializationIntoClass = JsonConvert.DeserializeObject<List<YourClassName>>(serializeObject);
return Json(desrializationIntoClass);