How Do I Display Sql Server Database Data Into W2ui Grid Control

Question

0.00/5 (No votes)

See more:

Hi everyone,
I want to display data from sql server to w2ui grid by converting data into json format but I got an error "data is not in a valid json format" ...please help me how can i dispaly remote data into w2ui grid
Thanks in advance..

code in w2uigrid.aspx in which i have declared the w2ui grid

XML

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="databind.aspx.cs" Inherits="databind" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script src="http://ajax.googleapis.com/ajax/libs/jquery/2.1.0/jquery.min.js"></script>
    <script type="text/javascript" src="http://w2ui.com/src/w2ui-1.4.min.js"></script>
  <link rel="stylesheet" type="text/css" href="http://w2ui.com/src/w2ui-1.4.min.css" />
</head>
<body>

    <div id="grid" style="width: 100%; height: 350px;"></div>

<script type="text/javascript">
    $(function () {
        $('#grid').w2grid({
            name: 'grid',

          url:'json.aspx/ConvertDataTabletoString',
           
           columns: [
            { field: 'recid', caption: 'recid', size: '30%' },

            { field: 'fname', caption: 'First Name', size: '30%' },
            { field: 'lname', caption: 'Last Name', size: '30%' },
            { field: 'email', caption: 'Email', size: '40%' },
           { field: 'sdate', caption: 'sdate', size: '40%' }

            ]
          
        });
    });

</script>

</body>
</html>

Code in json.aspx.cs in which i have written code for converting data into json format
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
public partial class json : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
var c=ConvertDataTabletoString();

}

public DataTable ConvertDataTabletoString()
{
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["databoundConnectionString"].ConnectionString);

SqlCommand cmd = new SqlCommand("JSONProcas2", con);
var x= cmd.CommandType=CommandType.StoredProcedure;

con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
return dt;
}
}
Stored procedure is

SQL

create procedure [dbo].[JSONProcas2]
as
begin
select '{records:[' + STUFF((
           select
            ',{"recid":' + cast(recid as varchar(max))
            +',"id":' + cast(stuid as varchar(max))
             + ',"name":"' + stuname + '"'
             +'}'

              from student2 t1
              for xml path(''), type

             ).value('.', 'varchar(max)'), 1, 1, '') + ']}'
end

Posted 6-Aug-14 23:22pm

Lavanya Jujjavarapu

Updated 6-Aug-14 23:28pm

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

AnvilRanger · Answer 1 · 2014-08-07T02:46:00

Solution 1

Your method does not return a JSON object, it returns a datatable. You need to serialize the datatable to JSON object.

http://www.aspdotnet-suresh.com/2013/05/c-convert-datatable-to-json-string-in-c.html[^]

Posted 7-Aug-14 2:46am

AnvilRanger

Comments

Lavanya Jujjavarapu 7-Aug-14 9:12am

Thanks for giving me the solutions...but by this way also i got an error "data is not in json format"

public string ConvertDataTabletoString()
{
DataTable dt = new DataTable();
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["databoundConnectionString"].ConnectionString);

SqlCommand cmd = new SqlCommand("select recid,stuid,stuname,stuaddr from student2", con);

con.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);

System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
List<dictionary<string, object="">> rows = new List<dictionary<string, object="">>();
Dictionary<string, object=""> row;
foreach (DataRow dr in dt.Rows)
{
row = new Dictionary<string, object="">();

foreach (DataColumn col in dt.Columns)
{

row.Add(col.ColumnName, dr[col]);
}
rows.Add(row);
}
return serializer.Serialize(rows);
}
}

json format must be in this format as shown below

{records:[{"recid":1,"stuid":100,"stuname":"lavanya","stuaddr":"banglore"},{"recid":2,"stuid":101,"stuname":"kk","stuaddr":"chennai"}]}

but by the above code i got like this format

[{"recid":1,"stuid":100,"stuname":"lavanya","stuaddr":"banglore"},{"recid":2,"stuid":101,"stuname":"kk","stuaddr":"chennai"}]

please give me the solution