Link to home
Start Free TrialLog in
Avatar of triphen
triphen

asked on

SQL Query Producing decimal places when it shouldn;t be

Hello Experts,

I am running the below query then displaying the results in a DataGridView in VS2015 (C#)

SELECT
                                                    employee.empnum AS [Employee_ID],
                                                    punchclock.snum AS [DeptCode],
                                                    CONVERT(VARCHAR,punchclock.opendate,101) AS [Date],
                                                    CONVERT(VARCHAR(5), punchclock.punchin,108) AS [PunchTime],
                                                    CASE WHEN punchclock.typepunch = 4 THEN 'ID' WHEN punchclock.typepunch = 5 THEN 'OB' ELSE 'Error' END AS [PunchType],
                                                    punchclock.jobtype AS [EarnCode],
                                                    '' AS [TaxCode],
                                                    '' AS [Comments],
                                                    '' AS [LaborAllocationCode],
                                                    '' AS [Hours],
                                                    CAST(tip AS DECIMAL(10,2)) AS [Dollars],
                                                    '' AS [TemporaryRate]
                                                    FROM
                                                    punchclock, employee
                                                    WHERE
                                                    employee.empnum = punchclock.empnum AND
                                                    punchclock.opendate BETWEEN 20160205 and 20160205 

                                                   UNION ALL

                                                    SELECT
                                                    employee.empnum AS [Employee_ID],
                                                    punchclock.snum AS [DeptCode],
                                                    CONVERT(VARCHAR,punchclock.opendate,101) AS [Date],
                                                    CONVERT(VARCHAR(5), punchclock.punchout,108) AS [PunchTime],
                                                    CASE WHEN punchclock.typepunch = 4 THEN 'OD' WHEN punchclock.typepunch = 5 THEN 'IB' ELSE 'Error' END AS [PunchType],
                                                    punchclock.jobtype AS [EarnCode],
                                                    '' AS [TaxCode],
                                                    '' AS [Comments],
                                                    '' AS [LaborAllocationCode],
                                                    '' AS [Hours],
                                                    '' AS [Dollars],
                                                    '' AS [TemporaryRate]
                                                    FROM
                                                    punchclock, employee
                                                    WHERE
                                                    employee.empnum = punchclock.empnum AND
                                                    punchclock.opendate BETWEEN 20160205 and 20160205 
                                                    ORDER BY [Date], [Employee_ID], [PunchTime]

Open in new window


When the query runs and the results display, the "Dollars" column shows 6 digits after the decimal?!? I only want 2. I have a feeling the issues is not the query but the DataGridView itself.

VS2015 C#
SyBase Anywhere v10
sql-results.PNG
Avatar of Brian Benson
Brian Benson

Try this out, may work...

From:
CAST(tip AS DECIMAL(10,2)) AS [Dollars]
To:
CAST(ROUND(tip, 2) AS MONEY) AS [Dollars]

Open in new window

Avatar of triphen

ASKER

Same result......


It has to be my DataGridView messing with it somehow...

This is how I am getting the results into the DataGridView.

string connection_string = "DSN=sqlbase;uid=dba;pwd=password;";
        OdbcConnection connection = new OdbcConnection(connection_string);
        try
        {
            OdbcDataAdapter adapter = new OdbcDataAdapter(sql_query, connection);
            DataSet dataset = new DataSet();
            adapter.Fill(dataset);
            dgv.DataSource = dataset.Tables[0];

            if (auto_resize_columns == true)

            {
                dgv.AutoResizeColumnHeadersHeight();
                dgv.AutoResizeColumns();
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); ;
        }
        finally
        {
            connection.Close();
        }

Open in new window

SOLUTION
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

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
Avatar of triphen

ASKER

How can I format the grid? Not really sure how to do that...can you point me in the right direction?
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
Avatar of triphen

ASKER

Thanks!

This worked to drop the extra decimals and only leave 2, but it added a "$".....how can I get rid of that now? Is there another format I can use for 2 decimals but no currency symbol?
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
Avatar of triphen

ASKER

Thank you!