TrinityBob TrinityBob - 3 months ago 12
SQL Question

How to round a number to 2nd decimal from a sql query returned to textbox

I have 3 textboxes that receives a int value from 3 sql queries that already uses the ROUND() function. But the result in the textbox returns 4 decimal points..ie i require the value xxxxx.xx as per the sql query but instead get xxxx.xxxx

here is my code

private void totalRef_btn_Click(object sender, EventArgs e)
{


SqlConnection conn = new SqlConnection("Data source=10.0.0.3,1434;Initial Catalog=client_orders_test;**Logins omitted**");
SumTotalquery = "Select ROUND(SUM(value),2) from costings_cur";
SumExVatquery = "Select ROUND(SUM(value-(value*14/100)),2) from costings_cur";
SumVatquery = "Select ROUND(SUM(value*14/100),2) from costings_cur";

SqlCommand totalQry = new SqlCommand(SumTotalquery, conn);
SqlCommand exVatQry = new SqlCommand(SumExVatquery, conn);
SqlCommand vatQry = new SqlCommand(SumVatquery, conn);
conn.Open();
SqlDataReader DR1 = totalQry.ExecuteReader();

if (DR1.Read())
{
total_txtbox.Text = DR1.GetValue(0).ToString();
}
conn.Close();

conn.Open();
SqlDataReader DR2 = exVatQry.ExecuteReader();

if (DR2.Read())
{
exvat_txtbox.Text = DR2.GetValue(0).ToString();
}
conn.Close();

conn.Open();
SqlDataReader DR3 = vatQry.ExecuteReader();

if (DR3.Read())
{
vat_txtbox.Text = DR3.GetValue(0).ToString();
}

conn.Close();
}


i have even tried using this:

exvat_txtbox.Text = DR2.GetValue(0).ToString().Trim();


Thanks in advance

Answer

USE Decimal(18,2) to get correct result

SELECT CAST(ROUND(SUM(value),2) AS DECIMAL(18,2)) FROM costings_cur

Similar way for others SELECT Statments.

Note : Round function just make rounding of number for specified length, it is not responsible for to get data in specific format.