Server Sorting, Paging and Filtering with jqxGrid problem

[wormie007Participant]

Hi. I have a problem with making a grid with virtual mode, i’m trying to use the sort and filter like the example in http://www.jqwidgets.com/jquery-widgets-documentation/documentation/asp.net-integration/asp.net-grid-paging-sorting-filtering.htm but i cant execute becouse when it try to make the dataadpter it trhows an error that the metoth is uknown. I dont use a web service(asmx) to call the functions. what could be the problem?

code:

using [...]

[...]

[ScriptService]
public partial class Paginas_Metodos : System.Web.UI.Page
{
[...]

[...]
[WebMethod]
    [ScriptMethod(UseHttpGet = true, ResponseFormat = ResponseFormat.Json)]
    public object GetCustomers(){ 
        var queryString = this.Context.Request.QueryString;       
        var query = this.BuildQuery(queryString);         
        var pagenum = int.Parse(queryString.GetValues("pagenum")[0]);     
        var pagesize = int.Parse(queryString.GetValues("pagesize")[0]);   
        var endWhere = " a WHERE row > " + pagenum * pagesize + " AND row <= " + (pagenum + 1) * pagesize;    
        query = "SELECT * FROM (" + query + ")" + endWhere;     
        SqlCommand cmd = new SqlCommand(query);      
        // Populate the DataSet.          
        var data = this.GetDatajson(cmd);
        data = "{ \"count\": " + this.GetTotalRowsCountGridServer() + ", \"data\":" + data + "}";        
        return new JavaScriptSerializer().DeserializeObject(data);    
    }

    public int GetTotalRowsCountGridServer()        {        
        string query = this.BuildQuery(this.Context.Request.QueryString);        
        SqlCommand countCmd = new SqlCommand("SELECT COUNT(*) as c FROM (" + query + ") as T");       
        int count = this.GetRowsCount(countCmd);  
        return count;      
    }

    private string GetDatajson(SqlCommand cmd){     
        string json;       
        string strConnString = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;   
        using (SqlConnection con = new SqlConnection(strConnString)){    
            cmd.Parameters.AddWithValue("@Param", "value");           
            cmd.Connection = con;          
            con.Open();         
            using (SqlDataReader reader = cmd.ExecuteReader())    
            {            
                json = this.ReadToJson(reader);       
            }           
            con.Close();        
        }         
        return json;   
    }
 
    public string ReadToJson(SqlDataReader reader){ 
        List<string> cols = new List<string>(10);      
        int ncols = reader.FieldCount;   
        for (int i = 0; i < ncols; ++i)        
        {          
            cols.Add(reader.GetName(i));    
        }          
        StringBuilder sbJson = new StringBuilder("[");       
        //process each row     
        while (reader.Read())         
        {           
            sbJson.Append("{");         
            foreach (string col in cols)    
            {                 
                sbJson.AppendFormat("\"{0}\":\"{1}\", ", col, reader[col]);   
            }             
            sbJson.Replace(", ", "},", sbJson.Length - 2, 2);  
        }         
        if (sbJson.Length < 2) return "[]";   
        sbJson.Replace("},", "}]", sbJson.Length - 2, 2);    
        return sbJson.ToString();       
    }

    private int GetRowsCount(SqlCommand cmd){    
        string strConnString = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;   
        SqlConnection con = new SqlConnection(strConnString);     
        cmd.Connection = con;         
        con.Open();        
        SqlDataReader reader = cmd.ExecuteReader();      
        reader.Read();       
        int rows = int.Parse(reader["c"].ToString());      
        con.Close();    
        return rows;   
    }
    
    private string BuildQuery(System.Collections.Specialized.NameValueCollection query){    
        string queryString = ""            + "  SELECT *, ROW_NUMBER() OVER (ORDER BY " + query.GetValues("sortdatafield")[0] + " "            + query.GetValues("sortorder")[0].ToUpper() + ") as row FROM T_Tratamientos "            + " ";       
        var filtersCount = int.Parse(query.GetValues("filterscount")[0]);     
        var where = "";         
        if (filtersCount > 0){    
            where += " WHERE (" + this.BuildFilters(filtersCount, query);      
        }                    
        queryString += where;     
        return queryString;      
    }

    private string BuildFilters(int filtersCount, System.Collections.Specialized.NameValueCollection query){    
        var tmpDataField = "";   
        var where = "";          
        var tmpFilterOperator = "";      
        for (var i = 0; i < filtersCount; i += 1)            {   
            var filterValue = query.GetValues("filtervalue" + i)[0];   
            var filterCondition = query.GetValues("filtercondition" + i)[0]; 
            var filterDataField = query.GetValues("filterdatafield" + i)[0];   
            var filterOperator = query.GetValues("filteroperator" + i)[0];    
            
            if (tmpDataField == ""){        
                tmpDataField = filterDataField;      
            }                else if (tmpDataField != filterDataField){   
                where += ") AND (";            
            }                else if (tmpDataField == filterDataField){              
                if (tmpFilterOperator == "")      
                {   
                    where += " AND ";       
                }else{     
                    where += " OR ";      
                }        
            }             
            // build the "WHERE" clause depending on the filter's condition, value and datafield. 
            where += this.GetFilterCondition(filterCondition, filterDataField, filterValue);  
            if (i == filtersCount - 1){      
                where += ")";            
            }               
            tmpFilterOperator = filterOperator;    
            tmpDataField = filterDataField;        
        }           
        return where;  
    }

    private string GetFilterCondition(string filterCondition, string filterDataField, string filterValue){    
        switch (filterCondition){ 
            case "NOT_EMPTY":           
            case "NOT_NULL":        
            return " " + filterDataField + " NOT LIKE '" + "" + "'";    
            case "EMPTY":           
            case "NULL":               
            return " " + filterDataField + " LIKE '" + "" + "'";    
            case "CONTAINS_CASE_SENSITIVE":       
                return " " + filterDataField + " LIKE '%" + filterValue + "%'" + " COLLATE SQL_Latin1_General_CP1_CS_AS"; 
            case "CONTAINS":                 
                return " " + filterDataField + " LIKE '%" + filterValue + "%'";     
            case "DOES_NOT_CONTAIN_CASE_SENSITIVE":     
                return " " + filterDataField + " NOT LIKE '%" + filterValue + "%'" + " COLLATE SQL_Latin1_General_CP1_CS_AS";      
            case "DOES_NOT_CONTAIN":            
                return " " + filterDataField + " NOT LIKE '%" + filterValue + "%'";  
            case "EQUAL_CASE_SENSITIVE":          
                return " " + filterDataField + " = '" + filterValue + "'" + " COLLATE SQL_Latin1_General_CP1_CS_AS";          
            case "EQUAL":             
                return " " + filterDataField + " = '" + filterValue + "'";   
            case "NOT_EQUAL_CASE_SENSITIVE":                  
                return " BINARY " + filterDataField + " <> '" + filterValue + "'";      
            case "NOT_EQUAL":             
                return " " + filterDataField + " <> '" + filterValue + "'";      
            case "GREATER_THAN":                
                return " " + filterDataField + " > '" + filterValue + "'"; 
            case "LESS_THAN":              
                return " " + filterDataField + " < '" + filterValue + "'";     
            case "GREATER_THAN_OR_EQUAL":     
                return " " + filterDataField + " >= '" + filterValue + "'";   
            case "LESS_THAN_OR_EQUAL":             
                return " " + filterDataField + " <= '" + filterValue + "'";      
            case "STARTS_WITH_CASE_SENSITIVE":           
                return " " + filterDataField + " LIKE '" + filterValue + "%'" + " COLLATE SQL_Latin1_General_CP1_CS_AS";   
            case "STARTS_WITH":               
                return " " + filterDataField + " LIKE '" + filterValue + "%'";      
            case "ENDS_WITH_CASE_SENSITIVE":                 
                return " " + filterDataField + " LIKE '%" + filterValue + "'" + " COLLATE SQL_Latin1_General_CP1_CS_AS"; 
            case "ENDS_WITH":                
                return " " + filterDataField + " LIKE '%" + filterValue + "'";   
        }           
        return "";        
    }
}

And code of the aspx that have the grid:

[...]

<script type="text/javascript">
        $(document).ready(function () {
            function buildQueryString(data) {
                var str = '';
                for (var prop in data) {
                    if (data.hasOwnProperty(prop)) {
                        str += prop + '=' + data[prop] + '&';
                    }
                }
                return str.substr(0, str.length - 1);
            }
            var formatedData = '';
            var totalrecords = 0;
            //Getting the source data with ajax GET request            
            var source = {
                datatype: "json",
                datafields: [
                                            { name: 'Historia', type: 'string' },
                                            { name: 'Nombre', type: 'string' },
                                            { name: 'Apellido1', type: 'string' },
                                            { name: 'Apellido2', type: 'string' },
                                            { name: 'Cic', type: 'string' },
                                            { name: 'Fecha_Prescripcion', type: 'string' },
                                            { name: 'Procedencia', type: 'string' },
                                            { name: 'Tipo_Tratamiento', type: 'string' },
                                            { name: 'Turno', type: 'string' },
                                            { name: 'Medico_Medulares', type: 'int' },
                                            { name: 'Fecha_Alta', type: 'string' },

                                            { name: 'Fisioterapeuta', type: 'int' },
                                            { name: 'Transporte', type: 'boolean' },
                                            { name: 'Fecha_Inicio', type: 'string' },
                                            { name: 'Fecha_Revision', type: 'string' },
                                            { name: 'Sesion', type: 'string' },
                                            { name: 'L', type: 'boolean' },
                                            { name: 'M', type: 'boolean' },
                                            { name: 'X', type: 'boolean' },
                                            { name: 'J', type: 'boolean' },
                                            { name: 'V', type: 'boolean' },
                                            { name: 'S', type: 'boolean' },
                                            { name: 'D', type: 'boolean' },
                                            { name: 'medicoNombre', type: 'string' },
                                            { name: 'medicoApellido1', type: 'string' },
                                            { name: 'medicoApellido2', type: 'string' },
                                            { name: 'Estado', type: 'string' },
                                            { name: 'Observaciones', type: 'string' }
    ],
                sort: function () {
                    $("#jqxgrid").jqxGrid('updatebounddata', 'sort');
                },
                filter: function () {
                    $("#jqxgrid").jqxGrid('updatebounddata', 'filter');
                },
                beforeprocessing: function (data) {
                    var returnData = {};
                    data = data.d;
                    totalrecords = data.count;
                    returnData.totalrecords = data.count;
                    returnData.records = data.data;
                    return returnData;
                },
                type: 'get',
                sortcolumn: 'Fecha_Prescripcion',
                sortdirection: 'asc',
                formatdata: function (data) {
                    data.pagenum = data.pagenum || 0;
                    data.pagesize = data.pagesize || 10;
                    data.sortdatafield = data.sortdatafield || 'Fecha_Prescripcion';
                    data.sortorder = data.sortorder || 'asc';
                    data.filterscount = data.filterscount || 0;
                    formatedData = buildQueryString(data);
                    return formatedData;
                },
                url: 'Paginas/Metodos.aspx/GetCustomers'
            };
            var dataAdapter = new $.jqx.dataAdapter(source, { contentType: 'application/json; charset=utf-8',
                loadError: function (xhr, status, error) {
                    alert(xhr.responseText);
                }
            });

            $("#jqxgrid").jqxGrid({
                source: dataAdapter,
                pageable: true,
                autoheight: true,
                virtualmode: true,
                sorttogglestates: 1,
                filterable: true,
                sortable: true,
                rendergridrows: function (args) {
                    return args.data;
                },
                columns: [
                    { text: 'Historia', datafield: 'Historia', width: '9%', align: 'center' },
                    { text: 'Cic', datafield: 'Cic', width: '9%', align: 'center' },
                    { text: 'Nombre', datafield: 'Nombre', width: '9%', align: 'center' },
                    { text: 'Apellido1', datafield: 'Apellido1', width: '9%', align: 'center' },
                    { text: 'Apellido2', datafield: 'Apellido2', width: '9%', align: 'center' },
                    { text: 'F. Prescripcion', datafield: 'Fecha_Prescripcion', width: '9%', align: 'center', cellsformat: 'dd/MM/yyyy HH:mm' },
                    { text: 'Procedencia', datafield: 'Procedencia', width: '9%', align: 'center' },
                    { text: 'Tipo de Tratamiento', datafield: 'Tipo_Tratamiento', width: '9%', align: 'center' },
                    { text: 'Turno', datafield: 'Turno', width: '9%', align: 'center' },
                    { text: 'Medico Medulares', datafield: 'Medico_Medulares', width: '9%', align: 'center' },
                    { text: 'Estado', datafield: 'Estado', width: '10%' }
        ]
            });
        });   
    </script>
[...]

[ivanParticipant]

Hi wormie007,

Our suggestion for you is to check whether the required JavaScript and CSS references are added to your project and also to check whether the path to those references is correct. Please, note that a working copy of the example described in the help topic can be downloaded on the bottom of the help topic.

Best Regards,
Ivan

jQWidgets Team
http://www.jqwidgets.com

[Author]

Posts