How to solve datatype conversions in sql server?

Question

I am having trouble inserting data into my database. I created a web api on visual studio with the following data model

public long ProductId { get; set; }
public string ProductName { get; set; }
public double Price { get; set; }
public string Url { get; set; }

I am testing using postman with the following post request

{
    "ProductID": null,
    "ProductName": "ProductA",
    "Price": 100.1,
    "Url":"https://productA.com"
}

And when debugging, I get error converting data type varchar to float. This can be seen in the image below. I do not know how to fix this i think the problem is in line 74

I created the database my running the following query

CREATE TABLE dbo.ProductInfo 
(
    ProductID bigint IDENTITY(1,1) NOT NULL,
    ProductName varchar(1000), 
    Price float,
    Url varchar (1000)
)

**Don't store prices or amounts of currency using `float` because it's an imprecise type!** Use either integer cents (using `int`) or use the built-in `decimal` type - you can also use the built-in `money` type but it has issues: https://stackoverflow.com/questions/582797/should-you-choose-the-money-or-decimalx-y-datatypes-in-sql-server — Dai, Aug 06 '20 at 11:51
And don't use Entity Types as DTOs - even though they often have the exact same members - they are not equivalent types. it also opens your application up to security issues because a malicious user could submit an entity request and change foreign-key references, for example. — Dai, Aug 06 '20 at 11:52
The problem lies in your string concatenation to build the sql command. Never do that. A part from a very serious problem called Sql Injection you have always problems in parsing those values to build the correct value for the underlying type. As @GordonLinoff says, use parameters. There are thousands examples here about _Parameterized queries_ — Steve, Aug 06 '20 at 12:08
Parametrisation will fix the error, but like others have said, you have other problems to fix here too. [Why do we always prefer using parameters in SQL statements?](https://stackoverflow.com/q/7505808/20299830) Also, images of code are not received well on this site. The cause of your error is the code in your image, which you failed to post in code fences (like the rest of your code). All of it should be in code fences, not just some. — Thom A, Aug 06 '20 at 12:08
Thanks for all your inputs. I will take that into consideration. — Robert, Aug 06 '20 at 12:24
Whilst they're all right on that you should use parameterisation, because this is a horrible way to build your query string. Your code should still work due to auto casting, meaning there is something else not apparant from the info you've given that is happening. — D Kramer, Aug 06 '20 at 12:32

D Kramer · Answer 1 · 2020-08-06T13:40:54.043

You're not using parameters. You're simply inserting variables into a string.

In your values, you're actually inserting it as a varchar, since you build the query through

... '" + prod.Price + "' ...

Which results in the actual query string to become

Values('ProductA', '100.1', 'https://productA.com')

So SQL Server now tries to convert these values for you, by casting the varchar to a float. Which it should still be capable of doing.

The error you're getting is that it can't do that. I notice you're also inserting the prod.Price, instead of the var Price variable. Are you certain prod.Price is properly formatted?

Because what it shows here on the screen is that the var price should work, due to auto casting. My guess could be that prod.Price perhaps is comma seperated decimal instead of point seperated. This would cause SQL Server to throw an error.

SQL Server side example showcasing the error:

DECLARE @floatVariable FLOAT = 100.1 
/* Proper float value, so no problems */
SELECT @floatVariable

DECLARE @floatVariable2 FLOAT = '100.1' 
/* A valid float, but as varchar so autocasted */
SELECT @floatVariable2

DECLARE @floatVariable3 FLOAT = '100,1' 
/* A varchar with improper float values, autocasted but doesn't succeed */
SELECT @floatVariable3

Or simply use proper SQL Parameterisation for your queries. Instead of concatting everything together in a single string (always fun to get SQL injected like this).

Edit:

So having tested the in application conversion. In a simple C# console application, with a locale/culture that has comma seperated decimals as their convention, a double gets auto casted to comma seperated when 'adding' it to a string.

Example code:

static void Main(string[] args)
    {

        double a = 10.1;

        Console.WriteLine(@"Double value: " + a + " .");


        Console.ReadLine();
    }

Value Output:

Double value: 10,1 .

Your comment just pointed out something which I am now noticing :). I am posting the value with a point separated but I just debugged again and final query is surprisingly comma separated. I do no know why this happens. Perhaps thats the reason why it is failing. Any idea? — Robert, Aug 06 '20 at 13:13
@Robert If it gets inserted as comma seperated, I assume it's due to localization settings/culture. Or in some previous step it's already been converted due to localization. Check the original prod.Price value and type (not it's converted form var Price). Can be that due to localization/cultureinfo settings the double is being converted into comma seperated by default. — D Kramer, Aug 06 '20 at 13:24
@Robert tested it out in Visual studio on my own Localization/Culture, and am able to reproduce the problem. Edited the answer to reflect it. — D Kramer, Aug 06 '20 at 13:42

How to solve datatype conversions in sql server?

1 Answers1