inline edit zero comma is giving error

burckardas

i am using datatable inline edit.
the cell is numeric if i write** 1,3 it works** but if i try to write 0,3 i get error "Error converting data type nvarchar to numeric." but if i write ,3 without zero , it works. Could you please help me about this ?

Thanks,

colin

Hi @burckardas ,

I just tried it on this page, and it's working as expected.

Could you link to a running test case, please, showing the issue so we can offer some help. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

burckardas

@colin

Thank you,
you could see running test on http://82.222.153.50:5559/SupplierOffer/EditRfq/BE05B95AE730D1C99E9BE4693D2473DBE396FBBDC297FB7736BAEB42405A1939

the column : Fiyatınız is the numeric cell.

Regards,
Burc

colin

Hi @burckardas ,

That helps, thanks, it's always good to see the problem.

The error is coming from the client, it's being returned by the server. If you check the network response, the server scripts returns this:

{"draw":null,"data":[],"recordsTotal":null,"recordsFiltered":null,"error":"Error converting data type nvarchar to numeric.","fieldErrors":[],"id":null,"meta":{},"options":{},"files":{},"upload":{"id":null},"debug":null,"cancelled":[]}

so that would be the place to look,

Cheers,

Colin

burckardas

hi ,

i have checked sql update querry after updates and that error message is coming from sql.

if i write ",3" it converts to "0.3" and no error

sql querry is :
exec sp_executesql N'UPDATE [SupplierPriceLineItems] SET [SupplierPrice] = @SupplierPrice WHERE [SupplierPriceLineItems].[id] = @where_0 ',N'@SupplierPrice decimal(1,1),@where_0 nvarchar(4)',@SupplierPrice=0.3,@where_0=N'9197'

if i write "0,3" it does not converts to "0.3" and got error

sql querry is :
exec sp_executesql N'UPDATE [SupplierPriceLineItems] SET [SupplierPrice] = @SupplierPrice WHERE [SupplierPriceLineItems].[id] = @where_0 ',N'@SupplierPrice nvarchar(3),@where_0 nvarchar(4)',@SupplierPrice=N'0,3',@where_0=N'9197'

if i write "1,3" it converts to "1.3" and no error

sql querry is :
exec sp_executesql N'UPDATE [SupplierPriceLineItems] SET [SupplierPrice] = @SupplierPrice WHERE [SupplierPriceLineItems].[id] = @where_0 ',N'@SupplierPrice nvarchar(3),@where_0 nvarchar(4)',@SupplierPrice="1.3,@where_0=N'9197'

what should i do to solve that problem ?.

rf1234

You could use set and get formatters to have your numbers right. I wouldn't rely on the DBMS to get this done.

Here is a simple set Formatter in PHP for numbers like 1.000.000,00 (German) and 1,000,000.00 (English) both numbers are converted into 1000000.00 because that's what the DBMS expects.

//explode to get rid of the 1,000 separators
//works also for rates!!
if ($_SESSION['lang'] === 'de') {     
    $numberArray = explode('.', $val);
} else {
    $numberArray = explode(',', $val);
}
//implode without delimiter to join the pieces again
$numberString = implode($numberArray);
//replace the German decimal comma with a period
if ($_SESSION['lang'] === 'de') {   
    $numberString = str_replace(',', '.', $numberString);
}
return $numberString

And here is the getFormatter to convert the db-number-format into German or English format. For 0 I want to return spaces.

if ($val == '0') {
    return '';
} else {
    if ($_SESSION['lang'] === 'de') {     
        return number_format($val, 2, ',', '.');
    } else {
        return number_format($val, 2);
    }
}

rf1234

And the same thing in Java Script

setFormatter:

if (lang == 'de') {
    yourNumber = yourNumber.toString().replace( /[\.]/g, "" );
    yourNumber = yourNumber.toString().replace( /[\,]/g, "." );
} else {
    yourNumber = yourNumber.toString().replace( /[\,]/g, "" );
}
yourNumber = parseFloat(yourNumber);

getFormatter:

var numberRenderer;
if (lang == 'de') {
    numberRenderer = $.fn.dataTable.render.number( '.', ',', 2 ).display;
} else {
    numberRenderer = $.fn.dataTable.render.number( ',', '.', 2 ).display;
}
yourNumber = numberRenderer(yourNumber);

allan

Are you using the Editor .NET libraries? If so, then as @rf1234 says, using formatters is the way to do it here.

Allan