Round off the amount in the text box

Question

User810354248 posted

i have a amount in a text box

  Dim con As New SqlConnection(Str)
        con.Open()
 Dim qstr, qstr1, qstr2 As String
        qstr = "SELECT SUM(Amount) FROM PO_Items WHERE PONO='" + pnotxt.Text + "'"
        qstr1 = "SELECT SUM(GST) FROM PO_Items WHERE PONO='" + pnotxt.Text + "'"
        qstr2 = "SELECT SUM(GTotal) FROM PO_Items WHERE PONO='" + pnotxt.Text + "'"


        Dim sqladp1 As New SqlDataAdapter(qstr, con)
        Dim sqladp2 As New SqlDataAdapter(qstr1, con)
        Dim sqladp3 As New SqlDataAdapter(qstr2, con)

        Dim dta, dta1, dta2 As New DataSet
        dta.Clear()
        dta1.Clear()
        dta2.Clear()

        sqladp1.Fill(dta, "PO_Items")
        sqladp2.Fill(dta1, "PO_Items")
        sqladp3.Fill(dta2, "PO_Items")

        subttl.Text = dta.Tables("PO_Items").Rows(0).Item(0)
        gstttl.Text = dta1.Tables("PO_Items").Rows(0).Item(0)
        gttl.Text = dta2.Tables("PO_Items").Rows(0).Item(0)

I am getting out put as

in some cases 12252.45

in some cases 12252.80

if its 12252.45 then i want amount as 12252

if its 12252.80 then i want amount as 12253

Answer 1

User475983607 posted

Do you want to round the user's input or do you want to round the result if the TSQL SUM aggregate function?

I assume the SUM???

"SELECT ROUND(SUM(Amount),0) FROM PO_Items WHERE PONO='" + pnotxt.Text + "'"

TSQL Reference

https://docs.microsoft.com/en-us/sql/t-sql/functions/round-transact-sql?view=sql-server-ver15

Also, as recommended in your other posts, you should use a parameter query not string concatenation.

Google

https://www.google.com/search?q=TSQL+parameter+query

Answer 2

User-1716253493 posted

subttl.Text = string.Format("{0:N0}", dta.Tables("PO_Items").Rows(0).Item(0))

Answer 3

User1535942433 posted

Hi Baiju EP,

As far as I think,you could  use Math.Round , ToString format or use String.Format to convert the amount.

Just like as this:

Math.Round:

Math.Round(12252.80);

ToString format:

ToString("0.0");

String.Format:

String.Format("{0:0}", 12252.80);

Best regards,

Yijing Sun