I am using PageMethod to retrieve Table data in Json format using the following C# Code

[WebMethod]
public static string GetJson2()
{
    StringBuilder sb = new StringBuilder();
    StringWriter sw = new StringWriter(sb);
    JsonWriter jsonWriter = new JsonTextWriter(sw);

    try
    {
        string connstr = "server=localhost;user=root;database=cm_users;port=3306;password=root";
        MySqlConnection conn = new MySqlConnection(connstr);
        conn.Open();
        string sql = "select * from users";
        MySqlCommand cmd = new MySqlCommand(sql, conn);
        MySqlDataReader reader = cmd.ExecuteReader();

        while (reader.Read())
        {

            int fieldcount = reader.FieldCount; // count how many columns are in the row
            object[] values = new object[fieldcount]; // storage for column values
            reader.GetValues(values); // extract the values in each column

            jsonWriter.WriteStartObject();
            for (int index = 0; index < fieldcount; index++)
            { // iterate through all columns

                jsonWriter.WritePropertyName(reader.GetName(index)); // column name
                jsonWriter.WriteValue(values[index]); // value in column

            }
            jsonWriter.WriteEndObject();
        }
        reader.Close();

    }
    catch (MySqlException mySqlException)
    { // exception
        return mySqlException + "error";
    }

    // END of method
    // the above method returns sb and another uses it to return as HTTP Response...
    string jsonString = sb.ToString();
    return jsonString; ;
}

Now I am Catching the out put of the method into an html Page using an Java Scipt

Using Ajax JavaScript I am consuming the returned string which is in Json format.

function getUsers() {
    $.ajax({
        type: "POST",
        url: "http://{address}:8078/Default.aspx/GetJson2",
        data: "{}",
        contentType: "application/json",
        dataType: "json",
        success:  function (msg) {
            $("#Result").text(msg.d);
            var myTable1 = ''; 
            myTable1 += '<table id="myTable1" cellspacing=0 cellpadding=2 border=1>';
            myTable1 += "<tr><td><b>ID</b></td><td><b>UserName</b></td><td><b>Password</b></td><td><b>Email</b></td></tr>";


            $.each(msg, function(i,v){
                alert(i + v);

                myTable1 += "<tr><td>" + v.id + "</td><td>" + v.username + "</td><td>" + v.password + "</td><td>" + v.Email + "</td></tr>"; 
            });

            $("#user_tb1").html(myTable1) ;
        },
        error:    function () {
            alert("error");
        } 

    });
};

I am getting Json string as

{"id":1,"username":"karthik","password":"karthik","Email":"[email protected]"}{"id":2,"username":"Lohith","password":"Lohith","Email":"[email protected]"}

and Html as

A table structure in which each cell is filled with "undefined"

What might be the Issue in the above code.

I am using PageMethod to retrieve Table data in Json format using the following C# Code

[WebMethod]
public static string GetJson2()
{
    StringBuilder sb = new StringBuilder();
    StringWriter sw = new StringWriter(sb);
    JsonWriter jsonWriter = new JsonTextWriter(sw);

    try
    {
        string connstr = "server=localhost;user=root;database=cm_users;port=3306;password=root";
        MySqlConnection conn = new MySqlConnection(connstr);
        conn.Open();
        string sql = "select * from users";
        MySqlCommand cmd = new MySqlCommand(sql, conn);
        MySqlDataReader reader = cmd.ExecuteReader();

        while (reader.Read())
        {

            int fieldcount = reader.FieldCount; // count how many columns are in the row
            object[] values = new object[fieldcount]; // storage for column values
            reader.GetValues(values); // extract the values in each column

            jsonWriter.WriteStartObject();
            for (int index = 0; index < fieldcount; index++)
            { // iterate through all columns

                jsonWriter.WritePropertyName(reader.GetName(index)); // column name
                jsonWriter.WriteValue(values[index]); // value in column

            }
            jsonWriter.WriteEndObject();
        }
        reader.Close();

    }
    catch (MySqlException mySqlException)
    { // exception
        return mySqlException + "error";
    }

    // END of method
    // the above method returns sb and another uses it to return as HTTP Response...
    string jsonString = sb.ToString();
    return jsonString; ;
}

Now I am Catching the out put of the method into an html Page using an Java Scipt

Using Ajax JavaScript I am consuming the returned string which is in Json format.

function getUsers() {
    $.ajax({
        type: "POST",
        url: "http://{address}:8078/Default.aspx/GetJson2",
        data: "{}",
        contentType: "application/json",
        dataType: "json",
        success:  function (msg) {
            $("#Result").text(msg.d);
            var myTable1 = ''; 
            myTable1 += '<table id="myTable1" cellspacing=0 cellpadding=2 border=1>';
            myTable1 += "<tr><td><b>ID</b></td><td><b>UserName</b></td><td><b>Password</b></td><td><b>Email</b></td></tr>";


            $.each(msg, function(i,v){
                alert(i + v);

                myTable1 += "<tr><td>" + v.id + "</td><td>" + v.username + "</td><td>" + v.password + "</td><td>" + v.Email + "</td></tr>"; 
            });

            $("#user_tb1").html(myTable1) ;
        },
        error:    function () {
            alert("error");
        } 

    });
};

I am getting Json string as

{"id":1,"username":"karthik","password":"karthik","Email":"[email protected]"}{"id":2,"username":"Lohith","password":"Lohith","Email":"[email protected]"}

and Html as

A table structure in which each cell is filled with "undefined"

What might be the Issue in the above code.

• c#
• javascript
• jquery
• jquery-mobile

• The Java Script for it is – Karthik Dheeraj Commented May 22, 2013 at 13:18
• And what is a question here? – Gajotres Commented May 22, 2013 at 13:21
• Java Script is sliced above please find the script between 2 code blocks – Karthik Dheeraj Commented May 22, 2013 at 13:25
• There is probably some wrong with your JSON. Is it missing mas? I would map your database result to an object and use json.codeplex. to serialize to JSON. – topherd Commented May 22, 2013 at 13:39
• Your json is not valid. It should be [{...},{....}] – I4V Commented May 22, 2013 at 13:48

3 Answers 3

It looks like the json being retrieved from the server is incorrect, it's not an array of objects.

The correct format should be:

[
{"id":1,"username":"karthik","password":"karthik","Email":"[email protected]"},
{"id":2,"username":"Lohith","password":"Lohith","Email":"[email protected]"}
]

Here's a plnkr showing your table filling code working with correctly formatted data

There's something up with the JSON that you are getting back. The proper format needs to be:

var json = [{
    "id": 1,
    "username": "karthik",
    "password": "karthik",
    "Email": "[email protected]"
}, {
    "id": 2,
    "username": "Lohith",
    "password": "Lohith",
    "Email": "[email protected]"
}];

Below is a fiddle I created showing that the loop now alerts the username properly. http://jsfiddle/77YBq/

After more investigation:
To continue to drill into this issue I believe the root of your JSON problem "if the documentation is correct" JsonWriter Documentation

I beleive your server code needs to have

    jsonWriter.WriteStartArray(); // Starts Json Array notation;

    // This is your existing code
    //================================================================================
    while (reader.Read())
    {

        int fieldcount = reader.FieldCount; // count how many columns are in the row
        object[] values = new object[fieldcount]; // storage for column values
        reader.GetValues(values); // extract the values in each column

        jsonWriter.WriteStartObject();
        for (int index = 0; index < fieldcount; index++)
        { // iterate through all columns

            jsonWriter.WritePropertyName(reader.GetName(index)); // column name
            jsonWriter.WriteValue(values[index]); // value in column

        }
        jsonWriter.WriteEndObject();
    }
    reader.Close();
    //================================================================================
    // End of your existing code

    jsonWriter.WriteEndArray();  // Ends Json Array notation;

The JsonTextWriter is not intended to be used in the way you are using it.

You should take advantage of a serialization library so that you aren't writing code to serialize JSON.

Here is a solution that uses JSON.NET.

Include the package at http://json.codeplex./ in your solution.

Add this using statement to your file:

using Newtonsoft.Json;

Add a class to map your records to.

public class User{
   ... properties here

}



[WebMethod]
public static string GetJson2()
{
    StringBuilder sb = new StringBuilder();
    StringWriter sw = new StringWriter(sb);
    JsonWriter jsonWriter = new JsonTextWriter(sw);
    var users = new List<User>();
    try
    {
        string connstr = "server=localhost;user=root;database=cm_users;port=3306;password=root";
        MySqlConnection conn = new MySqlConnection(connstr);
        conn.Open();
        string sql = "select * from users";
        MySqlCommand cmd = new MySqlCommand(sql, conn);
        MySqlDataReader reader = cmd.ExecuteReader();

        while (reader.Read())
        {

            int fieldcount = reader.FieldCount; // count how many columns are in the row
            object[] values = new object[fieldcount]; // storage for column values
            reader.GetValues(values); // extract the values in each column

            users.add(new User { id = reader["id"], username = reader["username"] ..});
        }
        reader.Close();

    }
    catch (MySqlException mySqlException)
    { // exception
        return mySqlException + "error";
    }

    return JsonConvert.SerializeObject(users);
}

You should also consider naming your id, username etc as Id, Username etc so that you are following the correct naming conventions.