float numbers vs localizationobj.decimalseparator = ',';

[rubenfernande3Participant]

Im having problems when i read a number from a sql-server. This number has 2 decimal positions. The database is in spanish format so the decimalseparator is a ‘,’ instead of the english ‘.’.
I am unable to view the number correctly in the grid. It seems that the localization change that i use in the grid doesn´t work.

I use rendergridrows for recovering the records of the data adapter and it doesnt maintain the format of the number.

I have tried changing the database field with numeric(10,2), float and decimal and in the web page i have tried with type float and number and none of these seems to work.

Any advice ?…

This is my source:

var source = null;
function sourceInicial() {
source =
{
datatype: “json”,
datafields: [
{ name: ‘FechaSolicitud’, type: ‘date’, format: “dd/MM/yyyy HH:mm:ss” },
{ name: ‘ImporteTotalAutorizado’, type: ‘float’ }

],
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;
rendergridrows = returnData.records;
return returnData; //AT THIS POINT I HAVE A BREAK POINT AND I SEE THE NUMBER CORRECTLY = 484,53

},
type: ‘get’,
sortcolumn: ‘FechaSolicitud’,
sortdirection: ‘desc’,
formatdata: function (data) {
data.pagenum = data.pagenum || 0;
data.pagesize = data.pagesize || 15;
data.sortdatafield = data.sortdatafield || ‘FechaSolicitud’;
data.sortorder = data.sortorder || ‘desc’;
data.filterscount = data.filterscount || 0;
formatedData = buildQueryString(data);
return formatedData;
},
updaterow: function (rowid, newdata, commit) {
updaterow(newdata);
},

url: ‘NotificacionCompras.aspx/GetSolicitudesPendientes’
};

dataAdapter = new $.jqx.dataAdapter(source, {
contentType: ‘application/json; charset=utf-8’,
loadError: function (xhr, status, error) {
alert(error);
}

});

}

This is the grid (i use a function for localization settings)

var dataAdapter = null;
sourceInicial();

//Instanciamos el grid
$(“#jqxgrid”).jqxGrid({
source: dataAdapter,
theme: theme,
width: ‘95%’,
localization: getLocalization(),
showstatusbar: true,
statusbarheight: 90,
showtoolbar: true,
sortable: true,
pageable: true,
filterable: true,
virtualmode: true,
pagesize: 15,
rendergridrows: function (args) {
return args.data; //AT THIS POINT I HAVE ANOTHER BREAK POINT AND THE NUMBER IS 484. THE DECIMAL NUMBERS DISAPPEAR

//return rendergridrows;
},
autoheight: true,
enablebrowserselection: true,
autorowheight: false,
rowdetails: true,
showfilterrow: false,
rowdetailstemplate: { rowdetails: “<div style=’margin: 10px;’><ul style=’margin-left: 30px;’><li class=’solicitante’>Solicitud<li class=’justificacion’>Datos complementarios solicitud<li class=’adjuntos’>Adjuntos<li class=’evaluacionEconomica’>Valoración económica<div class=’solicitante’></div><div class=’justificacion’></div><div class=’adjuntos’></div><div class=’evaluacionEconomica’></div></div>”, rowdetailsheight: 760 },
columnsreorder: true,
autoshowfiltericon: false,
columnsresize: true,
ready: function () {

},
initrowdetails: initRowDetailsNotificacionCompras,
rendertoolbar: function (toolbar) {

}
,
renderstatusbar: function (statusbar) {
var container = $(“<div style=’overflow: hidden; position: relative; margin: 5px;’></div>”);
var Excel = $(“<div style=’float: left; margin-left: 5px;’><span style=’margin-left: 4px; position: relative; top: -7px;’>Excel</span></div>”);

container.append(Excel);
statusbar.append(container);

Excel.click(function () {
var exportinfo = $(“#jqxgrid”).jqxGrid(‘exportdata’, ‘xls’, ‘sit-descarga’, true, null, true, null, ‘utf-8’);
});

Excel.jqxButton({ theme: theme, width: 100, height: 25 });

},
columns: [

{ text: ‘Imp. Autorizado’, dataField: ‘ImporteTotalAutorizado’, width: ‘11%’, cellsformat: ‘c2’}
]

});

Finally this is my localization function:

var getLocalization = function () {
var localizationobj = {};
localizationobj.pagergotopagestring = “Ir a página”;
localizationobj.pagershowrowsstring = “Mostrar filas:”;
localizationobj.pagerrangestring = ” de “;
localizationobj.pagernextbuttonstring = “Siguiente”;
localizationobj.pagerpreviousbuttonstring = “Anterior”;
localizationobj.sortascendingstring = “Ordenar ascendente”;
localizationobj.sortdescendingstring = “Ordenar descendente”;
localizationobj.sortremovestring = “Quitar ordenación”;
localizationobj.firstDay = 1;
localizationobj.emptydatastring = “No hay datos para mostrar”;
localizationobj.percentsymbol = “%”;
localizationobj.currencysymbol = “€”;
localizationobj.filterorconditionstring = “o”;
localizationobj.filterandconditionstring = “y”;
localizationobj.filtershowrowstring = “Mostrar filas cuando:”;
localizationobj.filternumericcomparisonoperators = [‘igual’, ‘no igual’, ‘menor que’, ‘menor que o igual’, ‘mayor que’, ‘mayor que o igual’, ‘nulo’, ‘no nulo’];
localizationobj.filterdatecomparisonoperators = [‘igual’, ‘no igual’, ‘menor que’, ‘menor que o igual’, ‘mayor que’, ‘mayor que o igual’, ‘nulo’, ‘no nulo’];
localizationobj.filterstringcomparisonoperators = [“vaciossss”, “no vacio”, “contiene”, “contiene(match case)”, “no contiene”, “no contiene(match case)”, “empieza con”, “empieza con(match case)”, “termina con”, “termina con(match case)”, “igual”, “igual(match case)”, “null”, “no null”];
localizationobj.loadtext = “Cargando …”;
localizationobj.currencysymbolposition = “after”;
localizationobj.decimalseparator = ‘,’;
localizationobj.thousandsseparator = ‘.’;
return localizationobj;
}

[rubenfernande3Participant]

i give the solution to my own problem:

It works if i retrieve from the database the number like this 484.53 with the ‘.’ like decimal separator.
So i have changed the culture of the aplication to english and it works.

[rubenfernande3Participant]

in my opinion this is a bug of the library.

[DimitarParticipant]

Hello rubenfernande3,

This is not a bug. Numbers loaded with a comma as a decimal separator are just not recognised as such by the data adapter. Here is what you can do as a workaround:

<!DOCTYPE html>
<html lang="en">
<head>
    <link rel="stylesheet" href="../../jqwidgets/styles/jqx.base.css" type="text/css" />
    <script type="text/javascript" src="../../scripts/jquery-1.11.1.min.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxcore.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxdata.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxbuttons.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxscrollbar.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxlistbox.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxdropdownlist.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxmenu.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxgrid.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxgrid.filter.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxgrid.sort.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxgrid.edit.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxgrid.selection.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxpanel.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxcalendar.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxdatetimeinput.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxgrid.pager.js"></script>
    <script type="text/javascript" src="../../jqwidgets/jqxnumberinput.js"></script>
    <script type="text/javascript" src="../../scripts/demos.js"></script>
    <script type="text/javascript" src="../../jqwidgets/globalization/globalize.js"></script>
    <script type="text/javascript" src="generatedata.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            var sampleData = '[{"ProductName": "Apple", "UnitPrice": "2,2"}, {"ProductName": "Pomegranate", "UnitPrice": "3,1"}]';

            // prepare the data
            var source =
            {
                datatype: "json",
                datafields: [
                    { name: 'ProductName', type: 'string' },
                    { name: 'UnitPrice', type: 'string' }
                ],
                localdata: sampleData
            };

            var dataAdapter = new $.jqx.dataAdapter(source, {
                beforeLoadComplete: function (records) {
                    // update the loaded records. Dynamically add EmployeeName and EmployeeID fields. 
                    for (var i = 0; i < records.length; i++) {
                        var product = records[i];
                        product.UnitPrice = parseFloat(product.UnitPrice.replace(',', '.'));
                    }
                    return records;
                }
            });

            var getLocalization = function () {
                var localizationobj = {};
                localizationobj.pagergotopagestring = "Ir a página";
                localizationobj.pagershowrowsstring = "Mostrar filas:";
                localizationobj.pagerrangestring = " de ";
                localizationobj.pagernextbuttonstring = "Siguiente";
                localizationobj.pagerpreviousbuttonstring = "Anterior";
                localizationobj.sortascendingstring = "Ordenar ascendente";
                localizationobj.sortdescendingstring = "Ordenar descendente";
                localizationobj.sortremovestring = "Quitar ordenación";
                localizationobj.firstDay = 1;
                localizationobj.emptydatastring = "No hay datos para mostrar";
                localizationobj.percentsymbol = "%";
                localizationobj.currencysymbol = "€";
                localizationobj.filterorconditionstring = "o";
                localizationobj.filterandconditionstring = "y";
                localizationobj.filtershowrowstring = "Mostrar filas cuando:";
                localizationobj.filternumericcomparisonoperators = ['igual', 'no igual', 'menor que', 'menor que o igual', 'mayor que', 'mayor que o igual', 'nulo', 'no nulo'];
                localizationobj.filterdatecomparisonoperators = ['igual', 'no igual', 'menor que', 'menor que o igual', 'mayor que', 'mayor que o igual', 'nulo', 'no nulo'];
                localizationobj.filterstringcomparisonoperators = ["vaciossss", "no vacio", "contiene", "contiene(match case)", "no contiene", "no contiene(match case)", "empieza con", "empieza con(match case)", "termina con", "termina con(match case)", "igual", "igual(match case)", "null", "no null"];
                localizationobj.loadtext = "Cargando ...";
                localizationobj.currencysymbolposition = "after";
                localizationobj.decimalseparator = ',';
                localizationobj.thousandsseparator = '.';
                return localizationobj;
            }

            // initialize jqxGrid
            $("#jqxgrid").jqxGrid(
            {
                width: 850,
                source: dataAdapter,
                pageable: true,
                autoheight: true,
                sortable: true,
                altrows: true,
                enabletooltips: true,
                editable: true,
                selectionmode: 'multiplecellsadvanced',
                localization: getLocalization(),
                columns: [
                  { text: 'Product Name', datafield: 'ProductName', width: 250 },
                  { text: 'Unit Price', datafield: 'UnitPrice', align: 'right', cellsalign: 'right', cellsformat: 'f', width: 200 }
                ]
            });
        });
    </script>
</head>
<body class='default'>
    <div id='jqxWidget' style="font-size: 13px; font-family: Verdana; float: left;">
        <div id="jqxgrid">
        </div>
    </div>
</body>
</html>

Best Regards,
Dimitar

jQWidgets team
http://www.jqwidgets.com/

[rubenfernande3Participant]

thanks dimitar your solution works.

[rubenfernande3Participant]

Although Dimitar´s solution is a good aproach i see that in my case doesn´t work.

My final solution for this is making the same solution but in the beforeprocessing event of the source not in the data adaptar as dimitar suggested.

This is the source:

var source = null;
function sourceInicial() {
source =
{
datatype: “json”,
datafields: [

{ name: ‘FechaSolicitud’, type: ‘date’, format: “dd/MM/yyyy HH:mm:ss” },

{ name: ‘ImporteTotalAutorizado’, type: ‘number’ }

],
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;
rendergridrows = returnData.records;

for (var i = 0; i < returnData.totalrecords; i++) {
var registro = returnData.records[i];
registro.ImporteTotalAutorizado = parseFloat(registro.ImporteTotalAutorizado.toString().replace(‘,’, ‘.’));
returnData.records[i] = registro;
}
return returnData;
},
type: ‘get’,
sortcolumn: ‘FechaSolicitud’,
sortdirection: ‘desc’,
formatdata: function (data) {
data.pagenum = data.pagenum || 0;
data.pagesize = data.pagesize || 15;
data.sortdatafield = data.sortdatafield || ‘FechaSolicitud’;
data.sortorder = data.sortorder || ‘desc’;
data.filterscount = data.filterscount || 0;
formatedData = buildQueryString(data);
return formatedData;
},
updaterow: function (rowid, newdata, commit) {
updaterow(newdata);
},

url: ‘NotificacionCompras.aspx/GetSolicitudesPendientes’
};

Now the problem is that when i try to find in the grid a number with ‘,’ it gives me an error. It only works with the english format with the ‘.’ character.

[DimitarParticipant]

Hi rubenfernande3,

My solution works as expected in this situation and the localization is applied correctly, too. If you are implementing it on your side, make sure you have set the type of the ImporteTotalAutorizado datafield to ‘string’, not ‘number’.

Best Regards,
Dimitar

jQWidgets team
http://www.jqwidgets.com/

[rubenfernande3Participant]

Thanks for your help Dimitar.
I have tested and it works but if i dont declare as number the field in the source the grid doesn´t shows the currency number correctly with the ‘€’ character.
My solution works also so i prefer to use mine.

Anyway i have solved all the problems but is a bit hard to accomplish the job. It would be better if the data adapter recognize the ‘,’ in function of the language we indicate on it. 🙂

For filter purposes i have solved the problem also.
As im using this kind of solution for making paging, filter and sort http://www.jqwidgets.com/jquery-widgets-documentation/documentation/asp.net-integration/asp.net-grid-paging-sorting-filtering.htm

I have made some changes to the buildfilters so i can manage now this decimal fields (c# solution) correctly.

public static string BuildFilters3(int filtersCount, System.Collections.Specialized.NameValueCollection query)
{
//Sin en and del principio.
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];

//Tratamiento para los campos que son decimales para cambiar la , por el . de manera que no de error.
switch(filterDataField)
{
case “ImporteTotalAutorizado”:
filterValue = filterValue.ToString().Replace(‘,’,’.’);
break;
default:
break;
}

if (tmpDataField == “”)
{
tmpDataField = filterDataField;
where += ” ( “;
}
else if (tmpDataField != filterDataField)
{
where += “) AND (“;
}
else if (tmpDataField == filterDataField)
{
if (tmpFilterOperator == “0”)
{
where += ” AND “;
}
else
{
where += ” OR “;
}
}
// build the “WHERE” clause depending on the filter’s condition, value and datafield.
where += GetFilterCondition(filterCondition, filterDataField, filterValue);
if (i == filtersCount – 1)
{
where += “)”;
}
tmpFilterOperator = filterOperator;
tmpDataField = filterDataField;
}
return where;
}

[Author]

Posts