client side ajax throws Unknown parameter for row 0, column 0

billb503

I have been at this all day and I need another set of eyes to spot what I am sure is a simple error or omission. I know my web service is returning JSON and the JSON looks good to me, except for the leading quote " and the trailing quote "

'''

ID	FirstName	LastName	Type	Updated
ID	FirstName	LastName	Type	Updated

<script type="text/javascript">
    $(document).ready( function () {
        $("#example").DataTable({
            ajax: {
                url: "/api/demo/get",
                dataSrc: ""
            },
             columns: [
                { "data": "ID" },
                { "data": "FirstName" },
                { "data": "LastName" },
                { "data": "Type" },
                { "data": "Updated" }]
        });
    })
</script>

"[{\"ID\":285,\"FirstName\":\"Syed\",\"LastName\":\"Abbas\",\"Type\":\"SP\",\"Updated\":\"2007-04-08T00:00:00\"},{\"ID\":16867,\"FirstName\":\"Aaron\",\"LastName\":\"Adams\",\"Type\":\"IN\",\"Updated\":\"2007-10-30T00:00:00\"},{\"ID\":10312,\"FirstName\":\"Allison\",\"LastName\":\"Adams\",\"Type\":\"IN\",\"Updated\":\"2007-03-26T00:00:00\"},{\"ID\":10274,\"FirstName\":\"Amanda\",\"LastName\":\"Adams\",\"Type\":\"IN\",\"Updated\":\"2007-10-17T00:00:00\"},{\"ID\":10292,\"FirstName\":\"Amber\",\"LastName\":\"Adams\",\"Type\":\"IN\",\"Updated\":\"2007-02-08T00:00:00\"}]"

billb503

I found the problem - I was double serializing my result set to JSON in my web service. My HTML and JS code were fine. This JSON displays fine.

[{"ID":285,"FirstName":"Syed","LastName":"Abbas","Type":"SP","Updated":"2007-04-08T00:00:00"},{"ID":16867,"FirstName":"Aaron","LastName":"Adams","Type":"IN","Updated":"2007-10-30T00:00:00"},{"ID":10312,"FirstName":"Allison","LastName":"Adams","Type":"IN","Updated":"2007-03-26T00:00:00"},{"ID":10274,"FirstName":"Amanda","LastName":"Adams","Type":"IN","Updated":"2007-10-17T00:00:00"},{"ID":10292,"FirstName":"Amber","LastName":"Adams","Type":"IN","Updated":"2007-02-08T00:00:00"}]

billb503

I am posting my Web API controller method for a query from the AdventureWorks2012 database in case others are struggling with the same problem. :)

''' // GET api/<controller>
public List<personmodel> Get()
{
List<personmodel> mylist = new List<personmodel>();

        // open db
        using (SqlConnection cn = new SqlConnection(GetConnectionString()))
        {
            // open db
            cn.Open();

            // setup cmd
            string sql = "SELECT top 5 p.BusinessEntityID, p.FirstName, p.LastName, p.ModifiedDate, p.PersonType "
                + "FROM [AdventureWorks2012].[Person].[Person] p "
                + "WHERE year(modifieddate) = 2007 "
                + "ORDER BY LastName, FirstName"
                ;

            using (SqlCommand cmd = new SqlCommand(sql, cn))
            {
                // input parameters
                cmd.CommandType = CommandType.Text;

                // walk the reader
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        personmodel itm = new personmodel();
                        itm.ID = rdr.GetInt32(rdr.GetOrdinal("BusinessEntityID"));
                        itm.FirstName = rdr.GetString(rdr.GetOrdinal("FirstName"));
                        itm.LastName = rdr.GetString(rdr.GetOrdinal("LastName"));
                        itm.Type = rdr.GetString(rdr.GetOrdinal("PersonType"));
                        itm.Updated = rdr.GetDateTime(rdr.GetOrdinal("ModifiedDate"));
                        mylist.Add(itm);
                    }
                }
            }

            //close
            cn.Close();
        }
        //done
        return mylist;
    }