Hi holly75,
I have created sample code which fulfill your requirement.
HTML
<form id="form1" runat="server">
<div>
<table border="0" cellpadding="0" cellspacing="0">
<tr>
<td>
Subcategories:
</td>
<td>
<asp:DropDownList ID="ddlSubcategories" runat="server">
<asp:ListItem Text="=== Select ===" />
<asp:ListItem Text="Webdesign " />
<asp:ListItem Text="ERP" />
<asp:ListItem Text="C#" />
</asp:DropDownList>
</td>
</tr>
<tr>
<td>
Works hours:
</td>
<td>
<asp:DropDownList ID="ddlhours" runat="server">
<asp:ListItem Text="=== Select ===" />
<asp:ListItem Text="1" />
<asp:ListItem Text="2" />
<asp:ListItem Text="3" />
<asp:ListItem Text="4" />
<asp:ListItem Text="5" />
<asp:ListItem Text="6" />
<asp:ListItem Text="7" />
<asp:ListItem Text="8" />
</asp:DropDownList>
</td>
</tr>
<tr>
<td>
<asp:Button Text="Submit" OnClick="OnSubmit" runat="server" />
</td>
<td>
<asp:Label ID="lblMsg" runat="server" />
</td>
</tr>
</table>
</div>
<asp:LinkButton Text="Result" OnClick="OnClick" runat="server" />
<div>
<style type="text/css">
body
{
font-family: Arial;
font-size: 10pt;
}
table
{
border: 1px solid #ccc;
}
table th
{
background-color: #F7F7F7;
color: #333;
font-weight: bold;
}
table th, table td
{
padding: 5px;
border-color: #ccc;
}
</style>
</div>
</form>
C#
protected void OnSubmit(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(contring);
SqlCommand cmd = new SqlCommand("INSERT INTO ReportDetails Values(@Subcategories,@Works,@SubmitedDate)", con);
cmd.Parameters.AddWithValue("@Subcategories", ddlSubcategories.SelectedItem.Text.Trim());
cmd.Parameters.AddWithValue("@Works", ddlhours.SelectedItem.Text.Trim());
cmd.Parameters.AddWithValue("@SubmitedDate", DateTime.Now);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
lblMsg.Text = "Record Inserted Successfully";
lblMsg.ForeColor = System.Drawing.Color.Green;
}
protected void OnClick(object sender, EventArgs e)
{
Response.Redirect("ReportViews.aspx");
}
vb
Protected Sub OnSubmit(sender As Object, e As EventArgs)
Dim con As New SqlConnection(contring)
Dim cmd As New SqlCommand("INSERT INTO ReportDetails Values(@Subcategories,@Works,@SubmitedDate)", con)
cmd.Parameters.AddWithValue("@Subcategories", ddlSubcategories.SelectedItem.Text.Trim())
cmd.Parameters.AddWithValue("@Works", ddlhours.SelectedItem.Text.Trim())
cmd.Parameters.AddWithValue("@SubmitedDate", DateTime.Now)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
lblMsg.Text = "Record Inserted Successfully"
lblMsg.ForeColor = System.Drawing.Color.Green
End Sub
Protected Sub OnClick(sender As Object, e As EventArgs)
Response.Redirect("ReportViews.aspx")
End Sub
ReportViews.aspx
HTML
<form id="form1" runat="server">
<asp:TextBox ID="txtSubcategories" Pleaceholder="Enter a categories" runat="server" />
<asp:Button ID="btnSearch" Text="Search" OnClick="OnSearch" runat="server" />
<br />
<br />
<div>
<asp:GridView ID="gvReport" AutoGenerateColumns="true" runat="server" />
</div>
<br />
<asp:LinkButton Text="Back" OnClick="OnClick" runat="server" />
</form>
C#
protected void OnSearch(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection(contring);
SqlCommand cmd = new SqlCommand("Sp_Subcategories", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Subcategories", txtSubcategories.Text.Trim());
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
gvReport.DataSource = dt;
gvReport.DataBind();
}
protected void OnClick(object sender, EventArgs e)
{
Response.Redirect("Default.aspx");
}
Vb
Protected Sub OnSearch(sender As Object, e As EventArgs)
Dim con As New SqlConnection(contring)
Dim cmd As New SqlCommand("Sp_Subcategories", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@Subcategories", txtSubcategories.Text.Trim())
Dim sda As New SqlDataAdapter(cmd)
Dim dt As New DataTable()
sda.Fill(dt)
gvReport.DataSource = dt
gvReport.DataBind()
End Sub
Protected Sub OnClick(sender As Object, e As EventArgs)
Response.Redirect("Default.aspx")
End Sub
Sql
CREATE TABLE ReportDetails(
Id int IDENTITY PRIMARY KEY NOT NULL
,Subcategories VARCHAR(20)
,WorksHours NVARCHAR(20)
,SubmitedDate DATETIME
);
-----------------------------------------------------------------------------
CREATE PROCEDURE Sp_Subcategories
@Subcategories VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
SELECT SUM(Hours) TotalHours,Subcategories,REPLACE(CONVERT(VARCHAR(11),Monday,106), ' ','-') as Monday
,REPLACE(CONVERT(VARCHAR(11),Sunday,106), ' ','-') as Sunday
FROM(
SELECT SUM(WorksHours)Hours
,Subcategories
,DATEADD(WEEK, DATEDIFF(WEEK, '19000101', submitedDate), '19000101') as Monday
,DATEADD(WEEK, DATEDIFF(WEEK, '19000101', submitedDate)+1, '19000101')-1 as Sunday
FROM ReportDetails WHERE Subcategories=@Subcategories
GROUP BY Subcategories,submitedDate,WorksHours
)t
GROUP BY t.Subcategories,t.Monday,t.Sunday
END
GO
Screenshot