Can't read float value from SQL Server database

Question

I use SQL Server to build my database and SqlDataReader to read data from it.

command.Connection = cn;
command.CommandText = "SELECT * FROM test";
SqlDataReader rd = command.ExecuteReader();
while(rd.Read())
{
    double d = (double) rd.GetValue(0);
}

The column (0) I am trying to get value from is a 'float' type and has value '3.5' . As mapping data type from this MSDN link, the type of the object returned by rd.GetValue(0) must be 'double'. But the code above returns to variable 'd' value '0.0'. I tried this line:

double d = Convert.ToDouble(rd.GetValue(0));

But it still returns '0.0' to variable 'd'.

I tried searching on Google and StackOverflow but there is no result.

What am I missing? Help me!

Is there only 1 entry in given table?? Without an order by you cant verify that indeed row with field value = 3.5 is returned in case you have multiple records on your table — apomene, Nov 23 '17 at 13:47
@apomene I checked by placing a breakpoint and go into debug mode, the object of rd.GetValue(0) is indeed '3.5' and of type 'object {double}'. — Phạm Thành, Nov 23 '17 at 13:53
Try it like this, `select columnName * 1.0 from test`. It is also good practice to select by column names instead of '*'. — Rigerta, Nov 23 '17 at 13:53
@plaidDK I know I'm just trying to build a function to read from database and return a 'List of List of object' (List>) like a two-demensional array so that I could reuse it mutiple times. — Phạm Thành, Nov 23 '17 at 13:55
@PhạmThành My question remains....Do you have only 1 row?? If for example you have 3 rows, yes you can on debug mode get this object to be 3,5 for row 1 or 2 but your code takes the value of last iteration — apomene, Nov 23 '17 at 13:57
OMG i have just made another function similar to the one above and now it is working WTF!!! — Phạm Thành, Nov 23 '17 at 14:05
@apomene no I use ToString() method to check it and it is the exact column I am working on. — Phạm Thành, Nov 23 '17 at 14:06
What are you actually doing with d? Currently `d` falls out of scope immediately so its not immediately apparent what you are actually expecting out of this code... And if you have a similar function that is working then compare the two and work out what the difference is. Coding isn't magic. It does what you tell it and you are clearly telling it two different things in your code. If you honestly can't find the difference post both methods in a question and we can play spot the difference for you (though you really shouldn't need this). — Chris, Nov 23 '17 at 14:29

apomene · Answer 1 · 2017-11-23T14:09:42.207

As it is now, your code iterates over all the records (if there are many) an takes the last entry, which since you have no order by clause, may differ in every query execution. If indeed you want to only take 1 value, use ExecuteScalar together with an order by clause:

command.Connection = cn;
command.CommandText = "SELECT  TOP 1 * FROM test order by myfield desc"; //or asc
double result = (double)command.ExecuteScalar();

Otherwise have all the result saved in a list:

...

List<double> result = new List<doulbe>();
while(rd.Read())
{
    result.Add(double.Parse(rd[0].ToString());
}

Finally, if you need only the 1st field, for performance reasons, is far better not to use * but explicit set the field you want:

"SELECT  TOP 1 myfield  FROM test order by myfield desc"; //or asc

score 1 · Answer 2 · 2017-11-23T14:54:59.997

1

you can try it;

double d = (double) rd.GetValue(0);

to

double d = 0;
double.TryParse(rd["ColumnName"].ToString().Replace('.',','),out d);

OR:

double d = double.Parse(rd["ColumnName"].ToString(), CultureInfo.InvariantCulture);

edited Nov 23 '17 at 14:54

answered Nov 23 '17 at 14:03

A good answer doesn't just provide a block of code, it explains what you have done and why. If you don't do this then its impossible to learn from the answer (or at least much harder). – Chris Nov 23 '17 at 14:30
I have found the solution and I posted an answer about it. By the way thank you very much! – Phạm Thành Nov 23 '17 at 15:42

SqlKindaGuy · Answer 3 · 2017-11-23T14:36:19.250

0

This here works fine for me, im getting 3,5 in my list

List<double> columnData = new List<double>();
            using (SqlConnection connection = new SqlConnection("Server=EGC25199;Initial Catalog=LegOgSpass;Integrated Security=SSPI;Application Name=SQLNCLI11.1"))
            {
                connection.Open();
                    string query = "SELECT * FROM [dbo].[floattable]";
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            columnData.Add(reader.GetDouble(0));

                        }
                    }
                }
            }

edited Nov 23 '17 at 14:36

answered Nov 23 '17 at 14:14

SqlKindaGuy

3,501
2
12
29

I'm very confused by your first line. floats are a decimal number. The display of it will depend on your culture. For me three and a half would be "3.5". For others who use `,` as a decimal separator it might be "3,5". Maybe I'm misreading what you are saying, I'm not sure. Also you should explain why you think your new code is better. I see you are using `GetDouble` instead of `GetValue`. Perhaps you should explain this in your answer and why you think this is better. – Chris Nov 23 '17 at 14:33
I never said its better. Its an answer to your solution which is working. Float is of cause culture based my bad, i will delete that. – SqlKindaGuy Nov 23 '17 at 14:36
@Chris The GetDouble will get the datatype as it is. GetValue saves the value in an object and hereby you have to do conversion to get it to match what you want. Link here: https://stackoverflow.com/questions/37344167/how-to-get-float-value-with-sqldatareader – SqlKindaGuy Nov 23 '17 at 14:37
@Chris An answer doesnt need to be better. There is also many ways to solve a problem. I solved a problem using my way to do it. If you wanna use GetValue or GetDouble is pretty biased. – SqlKindaGuy Nov 23 '17 at 14:39
By "better" I was meaning compared to the OPs non-functioning code. You have made changes that you believe fix the problem and you should try to explain what they are rather than just giving a large code block and relying on the OP (and anybody else) looking through your code and working out what changes you've made and why they work when the original code didn't. – Chris Nov 23 '17 at 15:23
I have found the solution and I posted an answer about it. By the way thank you very much! – Phạm Thành Nov 23 '17 at 15:42

score 0 · Accepted Answer · answered Nov 23 '17 at 15:41

Oh I have found the answer. Nothing wrong with the code I wrote. The problem is that I place the breakpoint on the 'double d = (double) rd.GetValue(0)' line. That is, 'd' value is not assigned yet so that on the debug screen it returns '0.0'. Sorry for this mistake and thank you all Stack-Over-flowers for spending your time helping me!!!!

Can't read float value from SQL Server database

4 Answers4