Link to home
Start Free TrialLog in
Avatar of linkcube1
linkcube1

asked on

Converting to 2 decimal places in sql query

In the following code below I want to take 80% of the price for each item and return a total amount. It does this fine but doesn't round to 2 decimal places I'm using SQL CE edition so I can only use money as the field type.

I have tried  this "("SELECT BookID, MemberID, Title, Author, (Price=Round([Price],2) * 0.8) As Price FROM [CashOutDB] Where MemberID = '" + m.MemberID + "'", con);"
but the select isn't returning anything so I assume its wrong. Any tips or suggestions would be great.
public static void LoadCashOutList(ListView lv, Member m, TextBox txtTotal, Label label1)
        {
            SqlCeConnection con = BookStoreDB.GetConnection();
            SqlCeCommand comm = new SqlCeCommand("SELECT BookID, MemberID, Title, Author, (Price * 0.8) As Price FROM [CashOutDB] Where MemberID = '" + m.MemberID + "'", con);
            con.Open();
            SqlCeDataReader reader = comm.ExecuteReader();

            decimal cashOutTotal = 0;
            while (reader.Read())
            {
                string[] data = new string[5];
                data[0] = reader["BookID"].ToString();
                data[1] = reader["MemberID"].ToString();
                data[2] = reader["Title"].ToString();
                data[3] = reader["Author"].ToString();
                data[4] = reader["Price"].ToString();

                cashOutTotal += Convert.ToDecimal(reader["Price"]);
                ListViewItem liv = new ListViewItem(data);
                lv.Items.Add(liv);
            }
            label1.Text = " Hello " + m.FirstName + " " + m.LastName + ", Here are your books avaliable for cashing out";
            txtTotal.Text = "$ " +  cashOutTotal.ToString();
            con.Close();
        }

Open in new window

Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image


You can use String.Format

String.Format("{0:0.00}", YourValue);        
Try:

"SELECT BookID, MemberID, Title, Author, Round([Price]* 0.8,2) As Price FROM [CashOutDB] Where MemberID = '" + m.MemberID + "'"
Avatar of linkcube1
linkcube1

ASKER

Its still returning more than 2 places :om_prakash_p:
You can use the following query for converting price with two decimals.

SELECT BookID, MemberID, Title, Author, CONVERT((Price * 0.8),  float, 0) As Price FROM [CashOutDB] Where MemberID = '" + m.MemberID + "'",

 data[4] =  String.Format("{0:0.00}", reader.GetDouble(reader.getOrdinal("Price")));
sorry one type mistake above. So here is your final query.

SELECT BookID, MemberID, Title, Author, CONVERT(money, (Price * 0.8),  0) As Price FROM [CashOutDB] Where MemberID = '" + m.MemberID + "'"

There are three styles to use in Convert as given below. For example @value is 4532.784

CONVERT(money, @value,  0) will return 4532.78
CONVERT(money, @value,  1) will return 4,532.78
Its returning 3 decimal places..  using
CONVERT(money, @value,  1)  User generated image
And what about CONVERT(money, @value,  0)??

Kindly have a look here for CONVERT function detail.

I hope it will help.
Same result with 0  three decimal places.
hmmmmmmm.
its mean you price column is also MONEY type. kindly try this one. It will give you the result :)
convert(varchar, @value, 0)
Converting brings me back to .0000 decimal places
I have checked at my side and it works.

declare @value money
set @value = 4532.784
select convert(varchar, @value, 0)
I'm  using compact edition so I dont have Varchar, so im using nvarchar but that shouldn't make a difference.
SOLUTION
Avatar of kotukunui
kotukunui

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>>It does this fine but doesn't round to 2 decimal places I'm using SQL CE edition so I can only use money as the field type. <<
Unfortunately money is always going to have 4 decimal places.  

ok the String format works great, but how to I format this line
data[4] = reader["Price"].ToString();

Thanks for the help guys!
ewangoya gave the solution to that bit further back up the thread

 data[4] =  String.Format("{0:0.00}", reader.GetDouble(reader.getOrdinal("Price")));

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Use math.Round () function  in C#, VB
or ROUND in SQL