How to enable working of jqgrid Filter Toolbar From Server In asp.net

Question

Hii Guys!!!. I developed a Jqgrid to diaplay database.Now I want to add JQgrid filter Toolbar to refine the data as per user need so i added filter toolbar.But Filter Toolbar is working with only when 'loadonce:true' means locally with first page data whereas I want it to work for whole database...from server response...

Below I am posting my code for refrence ...

$(function () {
        $("#UsersGrid").jqGrid({
            url: 'getGriddahico.ashx',
            datatype: 'json',
            height: 250,
            colNames: ['UserID', 'username', 'ordinal', 'authcode', 'extension', 'trunk', 'dialnumber', 'dialdate', 'dialtime', 'duration', 'destination', 'price', 'toc'],
            colModel: [
                    { name: 'UserID', index: 'UserID', width: 100, sortable: true, align: 'center',hidden:true },
                    { name: 'username', width: 100, sortable: true, align: 'center' },
                    { name: 'ordinal', width: 100, sortable: true, align: 'center' },
                    { name: 'authcode', width: 100, sortable: true },
                    { name: 'extension', width: 100, sortable: true, align: 'center' },
                    { name: 'trunk', width: 100, sortable: true, align: 'center' },
                    { name: 'dialnumber', width: 100, sortable: true, align: 'center' },
                    { name: 'dialdate', width: 100, sortable: true, align: 'center' },
                    { name: 'dialtime', width: 100, sortable: true, align: 'center' },
                    { name: 'duration', width: 100, sortable: true, align: 'center' },
                    { name: 'destination', width: 100, sortable: true, align: 'center' },
                    { name: 'price', width: 100, sortable: true, align: 'center' },
                    { name: 'toc', width: 150, sortable: true, align: 'center' }
                ],
            rowNum: 100,
            rowList: [100, 200, 300],
            pager: '#UsersGridPager',
            sortname: 'username',
            //loadonce: true,
            viewrecords: true,
            ignoreCase:true,
            sortorder: 'asc',
            autowidth: true,
            toppager: true,
            height: '100%'

        });

        $("#UsersGrid").jqGrid('navGrid', '#UsersGridPager', { edit: false, add: false, del: false, search: false });

        jQuery("#UsersGrid").jqGrid('filterToolbar', { stringResult: true, searchOnEnter: false });
    });

My Handler(.ashx) file code:

                        int start=0;
                        int total=0;
                        int total_pages =0;


                        int intpage =Convert.ToInt32(request["page"]);
                        int limit=Convert.ToInt32(request["rows"]);
                       // int intpage = new Integer(request.getParameter("page"));
                        //int limit = new Integer(request.getParameter("rows"));



                        string sidx = request["sidx"];
                        string sord = request["sord"];

                       // String sidx = request.getParameter("sidx");
                        //String sord = request.getParameter("sord");

                        String strQuery="";
                        String json ="";

                      Boolean rc ;


                      MySqlDataReader rs;
                        //ResultSet rs = null;

                        if(sidx ==""){
                            sidx ="1";
                        }


                        /*-----------------------------------Conexión a la base de datos MySql-------------------------------------------*/
                        conexion  conexiondb = new conexion();
                        conexiondb.Conectar();
                        /*-----------------------------------------------------------------------------------------------------------*/

                        total = conexiondb.countRec("price", "processeddata_table");

                        if( total>0 ) {
                            double d = Math.Ceiling( (double)(total) / (double)(limit) );
                            total_pages = (int)(d);
                        } else {
                            total_pages = 0;
                        }

                        if (intpage > total_pages) {
                            intpage=total_pages;
                        }

                        start = limit * intpage - limit; 

                        if(start < 0 ){
                            start = 0;
                        }

                        //strQuery = "SELECT username,ordinal,authcode,extension,trunk,dialnumber,dialdate,dialtime,duration,destination,price,toc FROM processeddata_table ORDER BY username asc";

                        strQuery = "SELECT username,ordinal,authcode,extension,trunk,dialnumber,dialdate,dialtime,duration,destination,price,toc FROM processeddata_table ORDER BY " + sidx + " " + sord + " LIMIT " + start + " , " + limit;

                        rs = conexiondb.Consulta(strQuery);

                        total = conexiondb.countRec("price", "processeddata_table");

                        response.ContentType="text/x-json";
                        response.ContentType = "charset=utf-8";
                        //response.ContentEncoding="utf-8";
                        response.AddHeader("Pragma", "no-cache");
                        response.AddHeader("Cache-Control", "no-cache, must-revalidate");
                        response.AddHeader("Pragma", "no-cache");

                        json ="";
                        json = json + "{\n";
                        json = json + " \"page\":\""+intpage+"\",\n";
                        json = json + "\"total\":"+total_pages+",\n";
                        json = json + "\"records\":"+total+",\n";
                        json = json + "\"rows\": [";
                        rc =false;

                        while(rs.Read()){

                            if(rc){
                                json = json + ",";
                            }
                            json = json + "\n{";
                            json = json + "\"price\":\"" + Convert.ToInt32(rs["price"]) + "\",";
                            json = json + "\"cell\":[" + Convert.ToInt32(rs["price"]) + "";
                            json = json + ",\"" + Convert.ToString(rs["username"]) + "\"";
                            json = json + ",\"" + Convert.ToString(rs["ordinal"]) + "\"";
                            json = json + ",\"" + Convert.ToString(rs["authcode"]) + "\"";
                            json = json + ",\"" + Convert.ToString(rs["extension"]) + "\"";
                            json = json + ",\"" + Convert.ToString(rs["trunk"]) + "\"";
                            json = json + ",\"" + Convert.ToString(rs["dialnumber"]) + "\"";
                            json = json + ",\"" + Convert.ToString(rs["dialdate"]) + "\"";
                            json = json + ",\"" + Convert.ToString(rs["dialtime"]) + "\"";
                            json = json + ",\"" + Convert.ToString(rs["duration"]) + "\"";
                            json = json + ",\"" + Convert.ToString(rs["destination"]) + "\"";
                            json = json + ",\"" + Convert.ToString(rs["price"]) + "\"";
                            json = json + ",\"" + Convert.ToString(rs["toc"]) + "\"]";
                            json = json + "}";

                            rc=true;
                        }
                        json = json +"]\n";

                        json = json +"}";


                        HttpContext.Current.Response.Write(json);

Plz guys help me to resolve the issue.. Thanx in advance..

score 0 · Accepted Answer · edited May 23 '17 at 12:27

0

If you use loadonce: true option then the data will be loaded from the server at once and later searching, sorting and paging of the previously loaded data will be implemented locally by jqGrid.

If you don't want to use loadonce: true option then you have to implement the features in your server code. In the answer for example you can find an example of such implementation in the code which uses ASHX like you do.

edited May 23 '17 at 12:27

Community

1
1

answered Jan 31 '13 at 07:05

Oleg

220,925
34
403
798

Thnx oleg for ur respponse...I edited my post and pasted handler code to show u my handler .ashx file which is returning data in json formate itself .Plz help me as First time i am using Jqgrid.. – vikas Jan 31 '13 at 07:27
@vikas: You are welcome! In the answer which I referenced in my answer you can find [the demo project](http://www.ok-soft-gmbh.com/jqGrid/jqGridASHX.zip) which you can download and modify to your requirements. I think that there are too many places of your current code which I find not good. For example you use wrong `ContentType` `"text/x-json"`, you make serialization to JSON manually and you sill get errors for example in case of `"` for example in the data, I would use other caching headers as you use, ... Sorry, but it's too many problems. You can find my suggestions in my old demo. – Oleg Jan 31 '13 at 07:46
Sir will u please tell me How many files I need to add and modify for enabling filter toolbar .Right now I am getting only 'Filters' file and how to call this file ..Sir plz help me. – vikas Jan 31 '13 at 07:54
@vikas: Probably you mean `filters` parameters? It's the only parameter which represent filtering information. The format of the string `filters` is described [here](http://www.trirand.com/jqgridwiki/doku.php?id=wiki:advanced_searching#options). You have to deserialize JSON string to the object first and then generate `WHERE` part of the `SELECT` based on the information. – Oleg Jan 31 '13 at 08:03
Thnx sir.Actually Sir I am novice developer so it is difficult to get ..Plz sir guide me to the simplest link to enable the toolbar search from server side – vikas Jan 31 '13 at 08:18
@vikas: You are welcome! Sorry, but why you don't want to download [the demo project](http://www.ok-soft-gmbh.com/jqGrid/jqGridASHX.zip) from [the answer](http://stackoverflow.com/a/10871428/315935) which do **exactly** what you need? You use `filterToolbar` with `stringResult: true` option so the format of the filter is exactly the same as for advanced searching and my old answer do what you need. – Oleg Jan 31 '13 at 08:36
Sir i have putted search filter on toolbar in mygrid .Now what i have to write in handler file to complete the search which file i need to add from ur demo project to mine...Plz sir dont mind it as I am clueless.. – vikas Jan 31 '13 at 10:21
@vikas: By the way, if you need supports only `filterToolbar` and no Advanced Searching dialog you can simplify the code from my demo because all filters will contain only `AND` operation and will be not cascading. You can use `SqlCommand` in the case in relatively easy way. In any way I would recommend you to use standard `JavaScriptSerializer ` or open source [Newtonsoft.Json](http://james.newtonking.com/pages/json-net.aspx) for serializing/deserializing of objects to JSON. – Oleg Jan 31 '13 at 10:23
Sir in my handler .ashx file I am retrieving the records in string in jason formate .so how to serialize that in object .. – vikas Jan 31 '13 at 10:28
@vikas: I use in my demo `JsonConvert.DeserializeObject` from the open source [Newtonsoft.Json](http://james.newtonking.com/pages/json-net.aspx). My code show how to use Entity Framework for the access to database. If you don't use Entity Framework you can't just include a part of my code in youth. – Oleg Jan 31 '13 at 10:32
yes sir I am not using the entity framework for the access of database.Now Sir what would u suggest to get the job done in this case. – vikas Jan 31 '13 at 10:34
And sir i need only 'AND' option filter in my case – vikas Jan 31 '13 at 10:51
@vikas: Sorry, but I can't make all job for you. If you would deserialize `filters` parameter to object you will have object like [here](http://www.trirand.com/jqgridwiki/doku.php?id=wiki:advanced_searching#options) with `groupOp:"AND"`, empty `groups` part and array of `rules`. So you need to lop though `rules` array and create `WHERE` which look like `WHERE (...) AND (...)...` with one `(...)` for every rule from `rules`. The part of coding you have to write yourself. – Oleg Jan 31 '13 at 11:37
will u plz direct me by seeieng my handler json data to do the job...\ – vikas Jan 31 '13 at 12:24
@vikas: Sorry, but the code which you posted don't contains even any like which use `request["filters"]`. So the code don't use the filter at all. – Oleg Jan 31 '13 at 12:27
ok sir will you plz provide me code which uses mysql query to populate the jqgrid as i did not got any so used this logic by my own.. – vikas Jan 31 '13 at 12:30

How to enable working of jqgrid Filter Toolbar From Server In asp.net

1 Answers1