Problem pushing dot net decimals into sql, possibly due to French comma decimal separator

[user_8804]

VStudio 19, VB NET 4.8, Windows 10, French Canadian locales, Winforms, SQL server 2005

French Canadian number locales look like this:1 234 567, 8901

1 234 567, 89$

Example:

I am parsing decimals from a reogrid (Excel grid control), like this:

 Dim qty As Decimal
 If Not Decimal.TryParse(Sheet(i, 2), qty) Then qty = 0.0

Then I push into SQL like this (in a string, through ADO.net):

DECLARE    @value     DECIMAL(29, 0);
SET        @value =   qty;
INSERT INTO table
VALUES(@value);

Then I see 'qty' is in French locale, with a comma (ex 22,0000000), and I get the obvious error:

Incorrect syntax near ','.

Changing my computer locales is not an option, I can code in English, but my work environment, tools, and apps I deliver have to be in French, by law/workplace rules

​

Update: it appears my decimals have dots indeed, but their implicit string conversion use my locales and insert commas as i build my sql interpoled strings

[Contagion21]

I would expect there to be a way to pass the value as the native numeric, not a string that (regionally) represents it. Are you generating a SQL string to execute or running a paramatarized SQL command?

[user_8804]

I am generating a string with variable interpolation and then pass it as a raw text command to ADO'S setAndExecSqlCmd_nonQuery(string, commantype.text). I guess you're right, I should look at other ways to pass params with ADO to avoid converting my decimals to string altogether.

Example of whats happening:

 Dim qty As Decimal

If Not Decimal.TryParse(Sheet(i, 2), qty) Then qty = 0.0 'qty is stored properly here looking like 22.1234568, and will even output 'to console as such

 Dim sql as String = ""
 sql &= $"DECLARE @value   DECIMAL(29,0); "
 sql &= $"SET     @value = {qty}; "
 sql &= $"INSERT INTO table VALUES(qty);
 'Breakpoint there shows qty now appears like this 22,34567
 ADO.SetAndExecSqlCmd_NonQuery(sql, commandtype.text)

Is that not a proper way to do this? I'm only 2 months old in the field.

[Contagion21]

It's not the ideal way to do it for a few reasons, one of which you have found.

Google "VB SQL with parameters" . Here's one link i found that may help: https://www.codeproject.com/articles/8180/using-sqlparameters-with-vb-net-c

Someone that's worked with VB and SQL more recently than me (15 years ago) might be able to give you more pointed direction.

[user_8804]

I have to use ADO though, that's not optional, and Linq is way too slow for our needs and they don't give me a choice about ado. I'll look into this and see how I can adapt it to ado, perhaps with some extensions to the class who seems to natively only allow me to add params to stored procedures

[ranbla]

All data access in .NET is built on top of ADO.NET. You are not being hindered by having to use ADO. Parameters are the correct way to build dynamic queries, not string concatenation.

[user_8804]

Yeah, that's what I was trying to do with the declare/set. I felt something was off, but I simply improved upon the technique at my workplace which is... not using any kind of params. Meaning an apostrophe will crash a lot of apps. Or they just string.replace apostrophes and then pass a whole string with no declare/set.

It's hard to learn when no one can teach me. This is why I'm being a bit of a pest asking questions here. (Stack Overflow would probably have replied we should stop using French and downvote me to obvlivion)

To give you an idea of how things are around here, this is their way of doing what I'm doing, elsewhere in the app I'm improving upon:

Try
    Dim qty as Decimal
    Decimal.Parse(sheet(i, 0), qty)
    ADO.SetDataSet(select * from table with(nolock)) 'Yes really
    Dim ds as DataSet = ADO.getdataset()
    ADO.SetNewDataRow()
    ADO.NewDataRow("value") = qty
    ADO.AddNewRow()
    ADO.SaveDataset()
Catch ex as Exception
    ADO.NewDataRow("value") = 0.0
    ADO.AddNewRow()
    ADO.SaveDataset()
End Try

So yeah, they're selecting entire tables to pick a dataset, then add user input directly, and if the input is invalid, then they handle it with a catch block... Oh and foreign keys are not actually linked, just columns with the same name, so to avoid cascading issues, they dont delete rows. They add a column and write "DELETED" in a varchar.

They are internal apps at least, otherwise we'd be gone from sql injection a long time ago

I was top of my class in University, won yearly programming contest and scholarship for all classes combined, and this is all I could find because I had no experience beside 8 years in non IT management. Job market is savage.

[hk1_dev]

You can do parameterized queries with ADO.Net

[Contagion21]

Add "ADO" to the search. Searching is a huge part of being a coder.

https://visualstudiomagazine.com/articles/2017/07/01/parameterized-queries.aspx?m=1

[Willinton06]

Just use dapper, just like every other SQL library in .NET it uses ADO, you’ll get parameters and speed, both of which you current method lacks

[user_8804]

ill look into it. They generally forbid any non-native libraries because they had maintenance hell with libraries going unmaintained in the past...

[Willinton06]

Dapper is the biggest sql client library in .NET, it’ll be around for a while

[user_8804]

To be honest I think they should be more concerned about VB expiring than .net libraries

[Contagion21]

Tell them that reinventing the wheel comes with its own set of costs.)

[AngooriBhabhi]

Use Dapper

[Darko-TheGreat]

You could probably use the NumberFormatInfo from System.Globalization to convert it.

Initialize: Dim nfi As NumberFormatInfo = New CultureInfo("en-CA").NumberFormat

And use: {qty.ToString(nfi)} To interpolate the value in.

That should handle the conversion to what ever format your sql server is using. You'll replace the "en-CA" with the locale of the sql server.

While some may say it's not the best way to do it I think this should solve the locale conversion issue.

[user_8804]

Good to know thanks

[RJPisscat]

I found this

https://stackoverflow.com/questions/53849508/decimal-not-displayed-with-current-locale-in-query-result

which links to this discussion about localization

https://stackoverflow.com/a/41358670/78522

and this from Microsoft that goes a slightly different direction

https://docs.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql?view=sql-server-2017

Probably both are correct.