none
SQL Decimal data displaying in the ADO.net dataview is rounding to a dollar RRS feed

  • Question

  • One of the data returned from a stored procedure is Decimal(10,2). Running the stored procedure on the query analyzer consistently returns correct data (i.e. 156.85). However, when the returned data is loaded on ado.net dataview, it's rounding to the nearest dollar (i.e. 157.00).

    What I don't understand is how the query analyzer result is different from the dataview result?

    Wednesday, March 11, 2015 12:05 AM

All replies

  • Hi Laura_Oct,

    I am moving your thread into the ADO.NET DataSet Forum for dedicated support. Thanks for your understanding.

    Best Regards,

    Jack


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, March 12, 2015 8:44 AM
  • Hello Laura,

    It is not vary clear how you read the data, I assume that you use the ADO.NET api, and according to your description, I created a test demo for reading the decimal value both with and without the store produce:

    The table and value:

    ID            Name    DecimalValue
    
    1              1              156.85

    Without store produce:

    SqlConnection con = new SqlConnection(@"Server=(localdb)\Projects;Database=ADO.NET;Trusted_Connection=True;");
    
                    try
    
                    {
    
                        con.Open();
    
                        SqlCommand cmd = new SqlCommand("select * from [T20141230]", con);
    
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
    
                        DataTable dt = new DataTable();
    
                        //da.FillSchema(dt, SchemaType.Mapped);
    
                        da.Fill(dt);
    
                    }
    
                    catch (Exception)
    
                    {
    
                    }
    
                    finally
    
                    {
    
                        con.Close();
    
                    }


    With a store produce:

    CREATE PROCEDURE [dbo].[ProTestDecimalValue]
    
    AS
    
           SELECT * from [T20141230]
    
    RETURN 0

    Code used to call the SP:

    SqlConnection con = new SqlConnection(@"Server=(localdb)\Projects;Database=ADO.NET;Trusted_Connection=True;");
    
                    try
    
                    {
    
                        con.Open();
    
                        SqlCommand cmd = new SqlCommand("[ProTestDecimalValue]", con);
    
                        SqlDataAdapter da = new SqlDataAdapter(cmd);
    
                        DataTable dt = new DataTable();
    
                        //da.FillSchema(dt, SchemaType.Mapped);
    
                        da.Fill(dt);
    
                    }
    
                    catch (Exception)
    
                    {
    
                    }
    
                    finally
    
                    {
    
                        con.Close();
    
                    }

    However, both two approaches return 156.85. Since as you mentions, you check the value through a DataView, I am not sure what it is, if it is a control used to display data, I suggest that you could confirm it on this control related forum. Or you could have a try with my demo to see if it works.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.



    Thursday, March 12, 2015 9:19 AM
    Moderator
  • Laura,

    Can you show the code to create the DataView 

    (has to be something likewise)

    var or dim X = new DataView(Y) 'where Y is a datatable.

    However mostly it looks that somewhere on the path from the database to the dataview some code is used to force what you get.

    Be aware that standard .Net is by default rounding to even and then your valuer should have been 156.00. 

    http://en.wikipedia.org/wiki/Rounding#Round_half_to_even

    Used can be math.ceiling or a special round format likewise "away from zero"


    Success
    Cor

    Thursday, March 12, 2015 9:26 AM