the below is my gridview which I just thought to looks like. The grid statrs from the head named
project code. When I select a week from my dropdownlist named
Dropdownlistweek, suppose I select May 5 - May 11, 2012, so all week dates (from 5 to 11 May), should come on gridview's header. how it can be done,
and one more thing in which way I need to create my database table for inserting records for a particular user, as you can see in my gridview.
Select the week for timesheet entry
Dropdownlistweek
When the user selects the dates from the dropdown, lets assume, if the user selects May 5 - May 11, 2012
Then, a Gridview should show up with
GridView1
Save
Project code
Task No
6-May-12
7-May-12
8-May-12
9-May-12
10-May-12
11-May-12
Total
Dropdown list
DDL
8
8
8
8
8
8
Min of 40hrs required
Edit
Cancel
Dropdown list
DDL
8
8
8
8
8
8
Edit
Cancel
Total
16
16
16
16
16
16
It is our choices that show what we truly are, far more than our abilities...
You could follow below method to change your headText.
In .aspx:
<asp:DropDownList ID="DropDownList1" runat="server">
<asp:ListItem Selected="True">May 5 - May 11, 2012</asp:ListItem>
<asp:ListItem>May 12 - May 18, 2012</asp:ListItem>
<asp:ListItem>May 19 - May 25, 2012</asp:ListItem>
<asp:ListItem>May 26 - Jun 1, 2012</asp:ListItem>
</asp:DropDownList>
In .cs:
SqlDataSource ds = new SqlDataSource();
ds.ConnectionString = ConfigurationManager.ConnectionStrings["your_database"].ToString();
ds.SelectCommandType = SqlDataSourceCommandType.Text;
ds.SelectParameters.Clear();
ds.SelectCommand = "select...";//query
GridView1.DataSource = ds;
GridView1.DataBind();
string year =
DropDownList1.SelectedItem.ToString().Substring(DropDownList1.SelectedItem.ToString().Length-12, 12);
for (int i = 0; i < 7; i++)
{
GridView1.HeaderRow.Cells[i].Text = Convert.ToDateTime(year).AddDays(-6 + i).ToShortDateString().Replace("/","-");//change cell[] will change the position.
}
From your example Gridview, you need to calculate the number in each day, correct? If so, you could use PIVOT in your query. There is an example below:
declare @table1 table (Date date)
insert @table1 values('2011-1-1')
insert @table1 values('2011-2-1')
insert @table1 values('2011-3-1')
insert @table1 values('2011-4-1')
insert @table1 values('2011-5-1')
insert @table1 values('2011-6-1')
declare @table2 table( item varchar(2), starttime date, Endtime date)
insert @table2 values('A','2011-2-1','2011-3-16')
insert @table2 values('B','2011-1-1','2011-4-20')
insert @table2 values('C','2011-3-10','2011-6-1')
insert @table2 values('D','2011-4-15','2011-5-1')
SELECT item ,[2011-1-1], [2011-2-1], [2011-3-1], [2011-4-1], [2011-5-1],[2011-6-1]
FROM
(SELECT item ,Date, starttime,endtime
FROM @table2 join @table1 on date between starttime and Endtime) AS SourceTable
PIVOT
(
count(Date)
FOR Date IN ([2011-1-1], [2011-2-1], [2011-3-1], [2011-4-1], [2011-5-1],[2011-6-1])
) AS PivotTable;
Thanks.
Please mark the replies as answers if they help or unmark if not.
Feedback to us
demoninside9
Participant
1182 Points
1697 Posts
What should be the datatable structure
May 08, 2012 05:25 AM|LINK
Hi all,
the below is my gridview which I just thought to looks like. The grid statrs from the head named project code. When I select a week from my dropdownlist named Dropdownlistweek, suppose I select May 5 - May 11, 2012, so all week dates (from 5 to 11 May), should come on gridview's header. how it can be done,
and one more thing in which way I need to create my database table for inserting records for a particular user, as you can see in my gridview.
Chen Yu - MS...
All-Star
21569 Points
2493 Posts
Microsoft
Re: What should be the datatable structure
May 11, 2012 09:45 AM|LINK
Hi,
You could follow below method to change your headText.
In .aspx: <asp:DropDownList ID="DropDownList1" runat="server"> <asp:ListItem Selected="True">May 5 - May 11, 2012</asp:ListItem> <asp:ListItem>May 12 - May 18, 2012</asp:ListItem> <asp:ListItem>May 19 - May 25, 2012</asp:ListItem> <asp:ListItem>May 26 - Jun 1, 2012</asp:ListItem> </asp:DropDownList> In .cs: SqlDataSource ds = new SqlDataSource(); ds.ConnectionString = ConfigurationManager.ConnectionStrings["your_database"].ToString(); ds.SelectCommandType = SqlDataSourceCommandType.Text; ds.SelectParameters.Clear(); ds.SelectCommand = "select...";//query GridView1.DataSource = ds; GridView1.DataBind(); string year = DropDownList1.SelectedItem.ToString().Substring(DropDownList1.SelectedItem.ToString().Length-12, 12); for (int i = 0; i < 7; i++) { GridView1.HeaderRow.Cells[i].Text = Convert.ToDateTime(year).AddDays(-6 + i).ToShortDateString().Replace("/","-");//change cell[] will change the position. }From your example Gridview, you need to calculate the number in each day, correct? If so, you could use PIVOT in your query. There is an example below:
declare @table1 table (Date date) insert @table1 values('2011-1-1') insert @table1 values('2011-2-1') insert @table1 values('2011-3-1') insert @table1 values('2011-4-1') insert @table1 values('2011-5-1') insert @table1 values('2011-6-1') declare @table2 table( item varchar(2), starttime date, Endtime date) insert @table2 values('A','2011-2-1','2011-3-16') insert @table2 values('B','2011-1-1','2011-4-20') insert @table2 values('C','2011-3-10','2011-6-1') insert @table2 values('D','2011-4-15','2011-5-1') SELECT item ,[2011-1-1], [2011-2-1], [2011-3-1], [2011-4-1], [2011-5-1],[2011-6-1] FROM (SELECT item ,Date, starttime,endtime FROM @table2 join @table1 on date between starttime and Endtime) AS SourceTable PIVOT ( count(Date) FOR Date IN ([2011-1-1], [2011-2-1], [2011-3-1], [2011-4-1], [2011-5-1],[2011-6-1]) ) AS PivotTable;Thanks.
Feedback to us
Develop and promote your apps in Windows Store