?
Solved

Converting to 2 decimal places in sql query

Posted on 2011-04-26
20
Medium Priority
?
1,528 Views
Last Modified: 2012-08-13
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

0
Comment
Question by:linkcube1
  • 6
  • 6
  • 3
  • +4
20 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35468838

You can use String.Format

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

Expert Comment

by:Om Prakash
ID: 35468902
Try:

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

Author Comment

by:linkcube1
ID: 35468965
Its still returning more than 2 places :om_prakash_p:
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 4

Expert Comment

by:malikirfan28
ID: 35469003
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 + "'",
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35469037

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

Expert Comment

by:malikirfan28
ID: 35469044
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
0
 

Author Comment

by:linkcube1
ID: 35469246
Its returning 3 decimal places..  using
CONVERT(money, @value,  1)  1
0
 
LVL 4

Expert Comment

by:malikirfan28
ID: 35469307
And what about CONVERT(money, @value,  0)??

Kindly have a look here for CONVERT function detail.

I hope it will help.
0
 

Author Comment

by:linkcube1
ID: 35469412
Same result with 0  three decimal places.
0
 
LVL 4

Expert Comment

by:malikirfan28
ID: 35469477
hmmmmmmm.
its mean you price column is also MONEY type. kindly try this one. It will give you the result :)
convert(varchar, @value, 0)
0
 

Author Comment

by:linkcube1
ID: 35469654
Converting brings me back to .0000 decimal places
0
 
LVL 4

Expert Comment

by:malikirfan28
ID: 35469665
I have checked at my side and it works.

declare @value money
set @value = 4532.784
select convert(varchar, @value, 0)
0
 

Author Comment

by:linkcube1
ID: 35469846
I'm  using compact edition so I dont have Varchar, so im using nvarchar but that shouldn't make a difference.
0
 
LVL 3

Assisted Solution

by:kotukunui
kotukunui earned 664 total points
ID: 35471359
I'm not sure that this is a SQL Server issue. How the data is being returned from the database isn't as important as the function call in the line

cashOutTotal += Convert.ToDecimal(reader["Price"]);

Open in new window


This is the function that is accumulating the total value. The number of decimal places to be displayed should be set in the line that processes the string i.e. instead of :

txtTotal.Text = "$ " +  cashOutTotal.ToString();

Open in new window


try this

]txtTotal.Text = "$ " +  string.Format("${0:#,#.00}", cashOutTotal);

Open in new window

0
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 668 total points
ID: 35471384

Just as I said before, use String.Format

txtTotal.Text = String.Format("$ {0:0.00}",  cashOutTotal);
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35471534
>>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.  

0
 

Author Comment

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

Thanks for the help guys!
0
 
LVL 3

Expert Comment

by:kotukunui
ID: 35471984
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

0
 
LVL 4

Assisted Solution

by:malikirfan28
malikirfan28 earned 668 total points
ID: 35473261
Kindly also note that then you can convert it in your required format in C# as given below.

data[4] = Convert.ToDouble(reader["Price"].ToString()).ToString("#0.00");

I hope it will work.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35473782
Use math.Round () function  in C#, VB
or ROUND in SQL
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question