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#)
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
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]
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
ASKER
Same result......
It has to be my DataGridView messing with it somehow...
This is how I am getting the results into the DataGridView.
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();
}
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
How can I format the grid? Not really sure how to do that...can you point me in the right direction?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you!
Open in new window