Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

How to display data from SQL Server 2008 onto a datagridview in different format

I have the following data, which I pull from a SQL Server 2008 database.

ProjectHourID      TaskID WeekEndingDate      ActualHours BudgetHours RemainingHours TaskName
2                         1           9/30/2011                10                      50                      40                       test 1
3                         2           9/30/2011                15                    100                      75                       test 2
4                         1           9/23/2011                  5                      45                      40                       test 1
5                         2           9/23/2011                10                    100                      90                       test 2

I want to display the data above in a VS2010 windows forms application, using a datagridviewcontrol. I want to display the data in the following format on the datagridview (see screenshot). How would I do this?

Right now I am using the following code to populate the datagridview:

                OperationsDataContext opDC = new OperationsDataContext();

                dgTime.DataSource = opDC.Tasks_GetByProjectIDAndWeekEndingDate(Convert.ToInt32(cboTimeProject.SelectedValue), dtWeekEnding.Value);

                dgTime.AutoResizeColumns();

DatagridviewFormat.png
0
jjliu4492
Asked:
jjliu4492
  • 5
  • 5
1 Solution
 
Bob LearnedCommented:
I don't understand your question...what is it that you don't understand?  Are you looking for formatting advice?
0
 
jjliu4492Author Commented:
I want the data from the database to look like the above screenshot when I display it in a datagridview control.
0
 
Bob LearnedCommented:
Did you define columns for the DataGridView?  Did you bind the data to the DataGridView?  What are you having difficulty with?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
jjliu4492Author Commented:
I want the data that is pulled from the database to be displayed in a certain format within the datagridview control.

ProjectHourID      TaskID WeekEndingDate      ActualHours BudgetHours RemainingHours TaskName
2                         1           9/30/2011                10                      50                      40                       test 1
3                         2           9/30/2011                15                    100                      75                       test 2
4                         1           9/23/2011                  5                      45                      40                       test 1
5                         2           9/23/2011                10                    100                      90                       test 2

0
 
jjliu4492Author Commented:
0
 
Bob LearnedCommented:
OK, it seems like you want to create a cross-tab view of that data.  I would suggest doing that work on the back end.  What kind of data source are you working with?
0
 
jjliu4492Author Commented:
I am using linq to sql to pull the data from the database.

OperationsDataContext opDC = new OperationsDataContext();
                dgTime.DataSource = null;

                dgTime.DataSource = opDC.ProjectHours_GetByProjectIDAndWeekEndingDate(Convert.ToInt32(cboTimeProject.SelectedValue), dtWeekEnding.Value);
0
 
Bob LearnedCommented:
If you are working with SQL Server, on the back-end, then you could create a stored procedure to create a pivot.
0
 
jjliu4492Author Commented:
How do I create a pivot using a stored procedure?
0
 
Bob LearnedCommented:
The PIVOT keyword was introduced in 2005, so if you have an older version, I wouldn't know what the equivalence would be.

Using PIVOT and UNPIVOT
http://msdn.microsoft.com/en-us/library/ms177410(v=SQL.90).aspx

SQL SERVER – PIVOT and UNPIVOT Table Examples
http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/
SELECT PRODUCT, FRED, KATE
FROM (
SELECT CUST, PRODUCT, QTY
FROM Product) up
PIVOT (SUM(QTY) FOR CUST IN (FRED, KATE)) AS pvt
ORDER BY PRODUCT

Open in new window

0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now