c# - jQuery DataTable and Server Side Data issue with webservice -
i trying create datatable not in success. kindly can me.
this stored procedure:
create procedure [dbo].[pr_searchmember] @filterterm nvarchar(250) = null --parameter search columns , @sortindex int = 1 -- 1 based index indicate column order , @sortdirection char(4) = 'asc' --the direction sort in, either asc or desc , @startrownum int = 1 --the first row return , @endrownum int = 10 --the last row return , @totalrowscount int output , @filteredrowscount int output begin --wrap filter term % search values contain @filterterm set @filterterm = '%' + @filterterm + '%' declare @person table ( civilid nvarchar(12), firstname nvarchar(50) , lastname nvarchar(50) , mobile nvarchar(20) , parentid nvarchar(12) , rownum int ) insert @person(civilid , firstname , lastname , mobile , parentid , rownum) select civilid , firstname , lastname , mobile , parentid , row_number() on ( order /*varchar, nvarchar, char order by*/ case @sortdirection when 'asc' case @sortindex when 1 civilid when 2 firstname when 3 lastname when 4 mobile when 5 parentid end end asc, case @sortdirection when 'desc' case @sortindex when 1 civilid when 2 firstname when 3 lastname when 4 mobile when 5 parentid end end desc ) rownum dbo.hierarchy @filterterm null or civilid @filterterm or firstname @filterterm or lastname @filterterm or mobile @filterterm or parentid @filterterm select civilid , firstname , lastname , mobile , parentid , rownum @person rownum between @startrownum , @endrownum select @totalrowscount = count(*) dbo.hierarchy select @filteredrowscount = count(*) @person end this web service
[webmethod] [scriptmethod(usehttpget = true)] public static string getdata() { string connstring = configurationmanager.connectionstrings["connectionstring"].tostring(); list<paluwagan> lstpaluwagans = new list<paluwagan>(); int totalrowscount = 0, filteredrowscount = 0; string data = "", ssearch = httpcontext.current.request.params["ssearch"], ssortdir_0 = httpcontext.current.request.params["ssortdir_0"]; int? isortcol_0 = toint(httpcontext.current.request.params["isortcol_0"]); int? idisplaystart = toint(httpcontext.current.request.params["idisplaystart"]); int? idisplaylength = toint(httpcontext.current.request.params["idisplaylength"]); int? secho = toint(httpcontext.current.request.params["secho"]); ; using (sqlconnection cn = new sqlconnection(connstring)) { using (sqlcommand cmd = new sqlcommand("pr_searchmember", cn)) { try { cmd.commandtype = commandtype.storedprocedure; cmd.parameters.addwithvalue("@filterterm", ssearch); cmd.parameters.addwithvalue("@sortindex", isortcol_0); cmd.parameters.addwithvalue("@sortdirection", ssortdir_0); cmd.parameters.addwithvalue("@startrownum", idisplaystart); cmd.parameters.addwithvalue("@endrownum", idisplaystart + idisplaylength); cmd.parameters.add("@totalrowscount", sqldbtype.int); cmd.parameters["@totalrowscount"].direction = parameterdirection.output; cmd.parameters.add("@filteredrowscount", sqldbtype.int); cmd.parameters["@filteredrowscount"].direction = parameterdirection.output; cn.open(); using (sqldatareader dr = cmd.executereader()) { if (dr.hasrows) { while (dr.read()) { paluwagan lstpaluwagan = new paluwagan(); lstpaluwagan.civilid = dr["civilid"].tostring(); lstpaluwagan.firstname = dr["firstname"].tostring(); lstpaluwagan.lastname = dr["lastname"].tostring(); lstpaluwagan.mobile = dr["mobile"].tostring(); lstpaluwagan.parentid = dr["parentid"].tostring(); lstpaluwagan.rownum = int.parse(dr["rownum"].tostring()); lstpaluwagans.add(lstpaluwagan); } dr.close(); totalrowscount = convert.toint32(cmd.parameters["@totalrowscount"].value.tostring()); filteredrowscount = convert.toint32(cmd.parameters["@filteredrowscount"].value.tostring()); } } javascriptserializer serializer = new javascriptserializer(); data += "{\"secho\":\"" + secho + "\","; data += "\"aadata\":" + serializer.serialize(lstpaluwagans) + ","; data += "\"itotalrecords\":\"" + totalrowscount.tostring() + "\","; data += "\"itotaldisplayrecords\":\"" + filteredrowscount.tostring() + "\""; data += "}"; } catch (exception ex) { throw ex.innerexception ; } if (cn.state == system.data.connectionstate.open) cn.close(); return data; } } } this javascript code
<script type="text/javascript"> $(document).ready(function () { $("#org").jorgchart({ chartelement: '#chart', draganddrop: false }); $('#members').datatable({ "bserverside": true, "sajaxsource": "civilid.aspx/getdata", "sajaxdataprop": "", "bprocessing": true, "bdestroy": true, "fnserverdata": function (ssource, aodata, fncallback) { $.ajax({ "datatype": 'json', "contenttype": "application/json; charset=utf-8", "type": "get", "url": ssource, "data": aodata, "success": function (msg) { var json = jquery.parsejson(msg.d); fncallback(json); //$("#members").show(); } }); }, "aocolumns": [ { "sname": "civilid" }, { "sname": "firstname" }, { "sname": "lastname" }, { "sname": "mobile" }, { "sname": "parentid" }, { "sname": "rownum" } ] }); }); </script> i don't know getting typeerror. please note don't have json error
below json data
{"secho":"1","aadata":[{"civilid":"123456789012","firstname":"adel","lastname":"alshammeri","mobile":"99466444","parentid":"","rownum":1},{"civilid":"267122700454","firstname":"adel","lastname":"s","mobile":"99466444","parentid":"","rownum":2},{"civilid":"275030107784","firstname":"mahfuz","lastname":"s","mobile":"66390129","parentid":"267122700454","rownum":3},{"civilid":"265103002897","firstname":"antonio","lastname":"s","mobile":"99073592","parentid":"267122700454","rownum":4},{"civilid":"272110203967","firstname":"reco","lastname":"s","mobile":"50956026","parentid":"267122700454","rownum":5},{"civilid":"259061403367","firstname":"eliseo","lastname":"s","mobile":"50956026","parentid":"275030107784","rownum":6},{"civilid":"265020307919","firstname":"liza","lastname":"s","mobile":"66642749","parentid":"275030107784","rownum":7},{"civilid":"244101000772","firstname":"alasmar","lastname":"s","mobile":"99089648","parentid":"275030107784","rownum":8},{"civilid":"273100700825","firstname":"alhadad","lastname":"s","mobile":"60993977","parentid":"265103002897","rownum":9},{"civilid":"268082304389","firstname":"aileen","lastname":"s","mobile":"66481975","parentid":"265103002897","rownum":10}],"itotalrecords":"42","itotaldisplayrecords":"42"}
it worked changing sname mdata in aocolumns , sajaxdataprop aodata
Comments
Post a Comment