DataRow is zeroising the decimal part of a decimal when updating Datatable

Question

I am trying to update a DataTable that is retrieved from a DB before binding it to a Gridview.

However, when I update decimal fields the part after the decimal point is zeroised. What am I missing?

if (HttpContext.Current.Request.IsAuthenticated)
{
    // Get additional price matches
    using (SqlConnection stockConn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString))
    {
        // Check for trade match offer
        SqlCommand tradePCCheck = new SqlCommand("getAllMyPriceMatches", stockConn);
        tradePCCheck.CommandType = CommandType.StoredProcedure;
        SqlParameter email = tradePCCheck.Parameters.Add("@email", SqlDbType.NVarChar);
        try
        {
            email.Value = this.Context.User.Identity.Name;
        }
        catch
        {
            email.Value = " ";
        }
        SqlParameter thedate = tradePCCheck.Parameters.Add("@theDate", SqlDbType.DateTime);
        thedate.Value = DateTime.Now.AddHours(-50);

        stockConn.Open();
        SqlDataReader pcReader = tradePCCheck.ExecuteReader();
        pms.Load(pcReader);
        pcReader.Close();
        stockConn.Close();
    }
}

//Set Connection, Open the DB & Fill Data Set

using (SqlConnection stockConn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString))
{
    SqlCommand stockCommand = new SqlCommand("getTISearchResults", stockConn);
    stockCommand.CommandType = CommandType.StoredProcedure;
    SqlParameter keyword = stockCommand.Parameters.Add("@keyword", SqlDbType.NVarChar);
    keyword.Value = prefixText;
    stockConn.Open();
    SqlDataReader rd = stockCommand.ExecuteReader();
    searchResults.Load(rd);
    stockCommand.Dispose();
    rd.Dispose();
}

// Update Results with elevated prices...
foreach (DataRow dr in searchResults.Rows)
{
    // Check for PMS
    DataRow[] thePMS = pms.Select("tpc_stockid = '" + dr["stockitem_number"].ToString() + "'");

    if (thePMS.Length > 0)
    {
        decimal px = 0;
        decimal cash = 0;

        if (thePMS[0]["tpc_pricepx"] != null && !thePMS[0]["tpc_pricepx"].ToString().Equals(""))
        {
            px = Convert.ToDecimal(thePMS[0]["tpc_pricepx"]);
        }

        if (thePMS[0]["tpc_price"] != null && !thePMS[0]["tpc_price"].ToString().Equals(""))
        {
            cash = Convert.ToDecimal(thePMS[0]["tpc_price"]);
        }
        // update table and accept changes
        DataRow[] theRows = searchResults.Select("stockitem_number = '" + dr["stockitem_number"].ToString() + "' ");

        if (theRows.Length > 0)
        {
            theRows[0]["stockitem_pxprice"] = px;
            theRows[0]["stockitem_cashprice"] = cash;
            searchResults.AcceptChanges();
        }
    }
}

gvSearchResults.DataSource = searchResults;
gvSearchResults.DataBind();

I have output PX and Cash before the assignment and they hold the correct values of 800.19 and 500.12, but after the AcceptChanges and once they are bound, the output is 800.00 and 500.12.

theRows[0]["stockitem_pxprice"] & theRows[0]["stockitem_cashprice"] are both decimal(5,2) on the DB where the searchResultsDT is populated from.

Any help greatly appreciated.

Thanks.

What are the intermediate values of `px` and `cash` after the `Convert.ToDecimal()` call? I would look toward the possibility of a culture issue, here, but the fact that one works properly and the other does not seems really odd. — user, Dec 19 '12 at 12:54
Do you declare the structure (schema) of the `searchResults` before loading it from the `SqlReader`? Maybe your `stockitem_pxprice` is declared as `typeof(int)`? — Alex Filipovici, Dec 19 '12 at 12:57
Check the declaration/definition of datatable searchResults. There could be some type mismatch — gee'K'iran, Dec 26 '12 at 09:18
The search results fields are declared as decimal(5,2) and both px and cash have the correct decimals immediately before the calculation. — Ben Drury, Dec 29 '12 at 19:33
@BenDrury ensure that the table you are updating does not contain some sort of TRIGGER ([T-SQL TRIGGERS](http://msdn.microsoft.com/en-us/library/ms189799.aspx)), and that this trigger isn't rounding/truncating the decimal values. — Jesse, Feb 15 '13 at 17:13

score 2 · Answer 1 · answered Oct 28 '13 at 12:49

Yes you are missing string.format while setting value into grid. You need to format double before setting it.

Say for if you get a number like 4.506 it will display something like 4.5060 or if you have a number like 4.5 then it will display as 4.50.

I have faced this problem in templated gridview and had to use string.format and format specifier to resolve it.

score 0 · Answer 2 · edited Aug 30 '13 at 10:43

0

I think your table has below two fields as int data type:

stockitem_pxprice
stockitem_cashprice

modify your table with data type as numeric (18,2) for these two fields

or

modify your table with data type as [decimal](18, 2) for these two fields

no need of typecasting, asp.net will do it implicitly

edited Aug 30 '13 at 10:43

Alicia

1,152
1
23
41

answered Jan 19 '13 at 10:15

Sanjay

13
3

The fields in the table are decimal (5,2) – Ben Drury Jan 19 '13 at 10:30

score 0 · Answer 3 · edited Jul 25 '13 at 13:43

Foreach over the theRows.Columns and dump the .Type property to trace. You've mentioned several times that the type is decimal(5,2) which is a TSQL type. The DataTable holds c# types, which is decimal and that is like a very large float. It's the c# type that matters here.

There may have been a conversion in your tsql, e.g. Select myVal * 1 which turns your myVal decimal into an int. That then will be the type your datatable holds. I usually set my constants to something like myVal *1.0 to prevent values from turning into ints. Just like how you have to declare a decimal in c# like 100m, in TSQL you have to make sure literals are declared specially to prevent data type conversions

Ref http://msdn.microsoft.com/en-us/library/ms179899.aspx

score 0 · Answer 4 · answered Nov 12 '13 at 03:10

I'm not sure if you've got an answer yet, but I would share that I could fixed this by using method of SqlDataReader, it's GetDecimal method. You could code like this

using (SqlConnection stockConn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString))
{
        // Check for trade match offer
        SqlCommand tradePCCheck = new SqlCommand("getAllMyPriceMatches", stockConn);
        tradePCCheck.CommandType = CommandType.StoredProcedure;
        SqlParameter email = tradePCCheck.Parameters.Add("@email", SqlDbType.NVarChar);
        try
        {
            email.Value = this.Context.User.Identity.Name;
        }
        catch
        {
            email.Value = " ";
        }
        SqlParameter thedate = tradePCCheck.Parameters.Add("@theDate", SqlDbType.DateTime);
        thedate.Value = DateTime.Now.AddHours(-50);

        stockConn.Open();

    SqlDataReader pcReader = tradePCCheck.ExecuteReader();

    decimal px = 0;
    decimal cash = 0;

    if (pcReader.Read())
    {
        px = pcReader.GetDecimal(0);
        cash = pcReader.GetDecimal(1);
    }
    pcReader.Close();
    stockConn.Close();
}

Where pcReader.GetDecimal(0) mean get data field at index 0 as Decimal value from the result set, it's an order of your selected columns in SELECT command. And the Stored Procedure getAllMyPriceMatches, you could modify the query script by using JOIN command, between two table results, then you don't need to have the second query scope.

DataRow is zeroising the decimal part of a decimal when updating Datatable

4 Answers4