Frustrating webservice problem

Smallzoo

I can successfully call a webservice and return the data

aspx page...

[code]
$(document).ready(function () {
$("#get").click(function (e) {
e.preventDefault();
getUserNames();

});
});


var getUserNames = function () {
$("#sampleTable").dataTable({
"oLanguage": {
"sZeroRecords": "No records to display",
"sSearch": "Search on UserName"
},
"aLengthMenu": [[25, 50], [25, 50]],
"iDisplayLength": 150,
"bSortClasses": false,
"bStateSave": false,
"bPaginate": true,
"bAutoWidth": false,
"bProcessing": true,
"bServerSide": true,
"bDestroy": true,
"sAjaxSource": "WebService1.asmx/GetItems",
"bJQueryUI": true,
"sPaginationType": "full_numbers",
"bDeferRender": true,
"fnServerData": function (sSource, aoData, fnCallback) {
$.ajax({
"dataType": 'json',
"contentType": "application/json; charset=utf-8",
"type": "GET",
"url": sSource,
"data": aoData,
"success":
function (msg) {

! what goes here !


}
});
}
});
}
[/code]

and the webservice returns data as follows :-


{"Name": "A Q S Bathrooms Lyons Electrical","Town": "SPENNYMOOR"},{"Name": "H & S Bathrooms","Town": "BLACKBURN"},{"Name": "Homecare Heating","Town": "DARLINGTON"},{"Name": "Midland Showers & Bathrooms Ltd","Town": "NOTTINGHAM"},{"Name": "City Plumbing Supplies","Town": "CARDIFF"},{"Name": "Home & Trade Ltd","Town": "BURTON-ON-TRENT"},{"Name": "City Plumbing Supplies DUPP AT 4838","Town": "PLYMOUTH"},{"Name": "City Plumbing Supplies","Town": "HALIFAX"},{"Name": "Harmony Interiors","Town": "LUTON"},{"Name": "Pure Bathrooms","Town": "CAMBRIDGE"},{"Name": "Bathrooms Kitchens By Design","Town": "NUNEATON"},{"Name": "Fire & Water","Town": "WHITEHAVEN"},{"Name": "City Plumbing Supplies","Town": "EXETER"},{"Name": "City Plumbing Supplies","Town": "GOSPORT"},{"Name": "City Plumbing Supplies","Town": "GLASGOW"},{"Name": "City Plumbing Supplies","Town": "CHEPSTOW"},{"Name": "Drakes Plumbing Supplies Ltd.","Town": "UCKFIELD"},{"Name": "City Plumbing Supplies","Town": "MEXBOROUGH"},{"Name": "City Plumbing Supplies","Town": "ROSS-ON-WYE"},{"Name": "Neptune Supplies Ltd","Town": "NORTHWICH"},{"Name": "Ray Grahams","Town": "NEWTOWNARDS"},{"Name": "City Plumbing Supplies","Town": "BRIDGEND"},{"Name": "Ipswich Tile and Bathroom Centre","Town": "IPSWICH"},{"Name": "Plumbing Supplies","Town": "CRAIGAVON"},{"Name": "City Plumbing Supplies","Town": "CARMARTHEN"},
....etc

BUT ...I just cant figure out what I need in the success section to populate the datatable


PLEASE HELP its driving me mad

Thanks

allan

It doesn't look like it is valid JSON since it has that XML tag at the start. Are you able to return just plain, valid JSON?

Also, you will want to read this blog post for consuming data from objects: http://datatables.net/blog/Extended_data_source_options_with_DataTables

Allan

Smallzoo

This is the webservice.. what am I doing wrong ?

[code]
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Text;
using System.Web.Script.Services;

using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.ServiceModel.Web;


namespace NewWebsite
{
///
/// Summary description for WebService1
///
[WebService(Namespace = "NewWebsite")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
[System.Web.Script.Services.ScriptService]
public class WebService1 : System.Web.Services.WebService
{

[WebMethod]
[ScriptMethod(UseHttpGet = true)]
[WebInvoke(ResponseFormat = WebMessageFormat.Json,
BodyStyle = WebMessageBodyStyle.Bare, Method = "GET")]
public string GetItems()
{
var sb = new StringBuilder();
var connectionString = ConfigurationManager.ConnectionStrings["kudos"].ConnectionString;
SqlConnection conn = new SqlConnection(connectionString);

try
{ conn.Open(); }
catch (Exception e)
{ Console.WriteLine(e.ToString()); }

var DB = new SqlCommand();
DB.Connection = conn;
DB.CommandText = "select customer_name,town from customers";
var data = DB.ExecuteReader();
string outputJson = string.Empty;
while (data.Read())
{
sb.Append("{");
sb.AppendFormat(@"""Name"": ""{0}""", data["Customer_Name"]);
sb.Append(",");
sb.AppendFormat(@"""Town"": ""{0}""", data["Town"]);
sb.Append("},");
}
outputJson = sb.ToString();
return outputJson;
}

}
}
[/code]

allan

No idea I'm afraid - I can't help with server-side scripts. You'd need to ask in a forum specific to that language (C#?).

Allan

rowel

This site was useful to me when troubleshooting JSON data errors. It will tell you where the errors are, and hopefully clue you in if it's a data problem (doublequotes in native sql data, example), etc, or a problem with your code.

http://jsonformatter.curiousconcept.com/