This Way
HTML:
<form id="form1" runat="server">
<div>
<asp:GridView ID="gvCategory" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="Category" HeaderText="Category" />
<asp:TemplateField>
<ItemTemplate>
<asp:TextBox ID="txtDay1" runat="server" Width="110"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:TextBox ID="txtDay2" runat="server" Width="110"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:TextBox ID="txtDay3" runat="server" Width="110"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:TextBox ID="txtDay4" runat="server" Width="110"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:TextBox ID="txtDay5" runat="server" Width="110"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:TextBox ID="txtDay6" runat="server" Width="110"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:TextBox ID="txtDay7" runat="server" Width="110"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<br />
<br />
<asp:Button ID="btnSaveCategoryPrice" OnClick="SavePriceInDataBase" runat="server"
Text="Save" />
</div>
</form>
C#:
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[8] {
new DataColumn("Category"),
new DataColumn("Day1"),
new DataColumn("Day2"),
new DataColumn("Day3"),
new DataColumn("Day4"),
new DataColumn("Day5"),
new DataColumn("Day6"),
new DataColumn("Day7"),
});
//add your category
dt.Rows.Add("Travelling");
dt.Rows.Add("Food");
dt.Rows.Add("Air Fare,");
dt.Rows.Add("Auto Rental");
gvCategory.DataSource = dt;
gvCategory.DataBind();
//for generating datewise coloumn
for (int i = 1; i < gvCategory.HeaderRow.Cells.Count; i++)
{
gvCategory.HeaderRow.Cells[i].Text = DateTime.Now.AddDays(i - 1).ToString("ddd") + " " + DateTime.Now.AddDays(i - 1).ToShortDateString();
}
}
}
//save each row of gridview
protected void SavePriceInDataBase(object sender, EventArgs e)
{
foreach (GridViewRow row in gvCategory.Rows)
{
string catName = row.Cells[0].Text;
string no = (row.Cells[1].FindControl("txtDay1") as TextBox).Text;
double day1Price = Convert.ToDouble(((TextBox)row.Cells[1].FindControl("txtDay1")).Text);
double day2Price = Convert.ToDouble(((TextBox)row.Cells[2].FindControl("txtDay2")).Text);
double day3Price = Convert.ToDouble(((TextBox)row.Cells[3].FindControl("txtDay3")).Text);
double day4Price = Convert.ToDouble(((TextBox)row.Cells[4].FindControl("txtDay4")).Text);
double day5Price = Convert.ToDouble(((TextBox)row.Cells[5].FindControl("txtDay5")).Text);
double day6Price = Convert.ToDouble(((TextBox)row.Cells[6].FindControl("txtDay6")).Text);
double day7Price = Convert.ToDouble(((TextBox)row.Cells[7].FindControl("txtDay7")).Text);
//passing each row value
this.Save(catName, day1Price, day2Price, day3Price, day4Price, day5Price, day6Price, day7Price);
}
}
// save method
private void Save(string catName, double day1Price, double day2Price, double day3Price, double day4Price, double day5Price, double day6Price, double day7Price)
{
string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
string SqlStatement = "INSERT INTO [CategoryPrices]([CatName],[day1],[day2],[day3],[day4],[day5],[day6] ,[day7])" +
"VALUES (@CatName,@Day1Price,@Day2Price,@Day3Price,@Day4Price,@Day5Price,@Day6Price,@Day7Price)";
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlCommand cmd = new SqlCommand(SqlStatement, con))
{
con.Open();
cmd.Parameters.AddWithValue("@CatName", catName);
cmd.Parameters.AddWithValue("@Day1Price", day1Price);
cmd.Parameters.AddWithValue("@Day2Price", day2Price);
cmd.Parameters.AddWithValue("@Day3Price", day3Price);
cmd.Parameters.AddWithValue("@Day4Price", day4Price);
cmd.Parameters.AddWithValue("@Day5Price", day5Price);
cmd.Parameters.AddWithValue("@Day6Price", day6Price);
cmd.Parameters.AddWithValue("@Day7Price", day7Price);
cmd.ExecuteNonQuery();
con.Close();
}
}
}
SQL:
CREATE TABLE [dbo].[CategoryPrices](
[CatName] [varchar](50) NULL,
[day1] [money] NULL,
[day2] [money] NULL,
[day3] [money] NULL,
[day4] [money] NULL,
[day5] [money] NULL,
[day6] [money] NULL,
[day7] [money] NULL
) ON [PRIMARY]
GO
Image:
