Answered by:
save gridivew data based on split

Question
-
User-807418713 posted
Hello
This is my gridview data
Process Item Qty AAA A 2 AAA B 4 AAA C 6 BBB A 4 BBB K 8 CCC C 4 DDD M 1 DDD N 5 I have two table Table1 & Table2
Table1 I want to store like this
Process RefNo AAA 1 BBB 2 CCC 3 DDD 4 Table2 I Want to store like this below
Process Item Qty RefNo AAA A 2 1 AAA B 4 1 AAA C 6 1 BBB A 4 2 BBB K 8 2 CCC C 4 3 DDD M 1 4 DDD N 5 4 How would be my insert query in c# code behind.
Thanking You
Thursday, May 28, 2020 6:47 PM
Answers
-
User-939850651 posted
Hi, Gopi.MCA
You could store the data in the collection after filtering, and then traverse them and insert it into the data table.
More details, you could refer to the below codes:
StoreTo2Page.aspx
<body> <form id="StoreData" runat="server"> <div> <asp:GridView runat="server" ID="TableData" AutoGenerateColumns="false"> <Columns> <asp:TemplateField HeaderText="Process Column"> <ItemTemplate> <asp:Label runat="server" ID="Process" Text='<%#Eval("Process") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Item Column"> <ItemTemplate> <asp:Label runat="server" ID="Item" Text='<%#Eval("Item") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Qty Column"> <ItemTemplate> <asp:Label runat="server" ID="Qty" Text='<%#Eval("Qty") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> <asp:Button runat="server" OnClick="store_Click" ID="store" Text="StroeToTable" /> </div> </form> </body>
StoreTo2Page.aspx.cs
public partial class StoreTo2Table : System.Web.UI.Page { String conStr = "data source=.; database=TestDB; integrated security=SSPI"; protected void Page_Load(object sender, EventArgs e) { bindData(); } private void bindData() { DataTable dt = new DataTable(); using (SqlConnection conn = new SqlConnection(conStr)) { conn.Open(); String query = "select * from tb"; using (SqlCommand cmd = new SqlCommand(query, conn)) { SqlDataAdapter sda = new SqlDataAdapter(cmd); sda.Fill(dt); TableData.DataSource = dt; TableData.DataBind(); } } } protected void store_Click(object sender, EventArgs e) { //store data to table1 List<String> Pros = getProcess(); for (int i = 0; i < Pros.Count; i++) { try { using (SqlConnection conn = new SqlConnection(conStr)) { conn.Open(); String insertQuery = "insert into table1 (Process,RefNo) values(@Process,@RefNo)"; using (SqlCommand cmd = new SqlCommand(insertQuery, conn)) { cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@Process", Pros[i]); cmd.Parameters.AddWithValue("@RefNo", i + 1); cmd.ExecuteNonQuery(); } } } catch (Exception) { //do something here } } //store data to table2 List<Example> allData = getWholeGrid(); for (int i = 0; i < allData.Count; i++) { try { using (SqlConnection conn = new SqlConnection(conStr)) { conn.Open(); String insertQuery = "insert into table2 (Process,Item,Qty,RefNo) values(@Process,@Item,@Qty,(select RefNo from table1 where Process=@Process))"; using (SqlCommand cmd = new SqlCommand(insertQuery, conn)) { cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@Process", allData[i].Process); cmd.Parameters.AddWithValue("@Item", allData[i].Item); cmd.Parameters.AddWithValue("@Qty", allData[i].Qty); cmd.ExecuteNonQuery(); } } } catch (Exception) { //do something here } } Response.Write("<script>alert('stroe success')</script>"); } private List<String> getProcess() { List<String> Pros = new List<string>(); foreach (GridViewRow row in TableData.Rows) { String Pro = ((Label)row.FindControl("Process")).Text; if (!Pros.Contains(Pro)) { Pros.Add(Pro); } } return Pros; } private List<Example> getWholeGrid() { List<Example> Examples = new List<Example>(); foreach (GridViewRow row in TableData.Rows) { Example example = new Example(); example.Process = ((Label)row.FindControl("Process")).Text; example.Item = ((Label)row.FindControl("Item")).Text; example.Qty = int.Parse(((Label)row.FindControl("Qty")).Text); Examples.Add(example); } return Examples; } }
Example.cs
public class Example { public String Process { set; get; } public String Item { set; get; } public int Qty { set; get; } }
Result: In page is
When click the button, the data table is
Hope this can help you.
Best regards,
Xudong Peng
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Friday, May 29, 2020 3:40 AM
All replies
-
User-939850651 posted
Hi, Gopi.MCA
You could store the data in the collection after filtering, and then traverse them and insert it into the data table.
More details, you could refer to the below codes:
StoreTo2Page.aspx
<body> <form id="StoreData" runat="server"> <div> <asp:GridView runat="server" ID="TableData" AutoGenerateColumns="false"> <Columns> <asp:TemplateField HeaderText="Process Column"> <ItemTemplate> <asp:Label runat="server" ID="Process" Text='<%#Eval("Process") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Item Column"> <ItemTemplate> <asp:Label runat="server" ID="Item" Text='<%#Eval("Item") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Qty Column"> <ItemTemplate> <asp:Label runat="server" ID="Qty" Text='<%#Eval("Qty") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> <asp:Button runat="server" OnClick="store_Click" ID="store" Text="StroeToTable" /> </div> </form> </body>
StoreTo2Page.aspx.cs
public partial class StoreTo2Table : System.Web.UI.Page { String conStr = "data source=.; database=TestDB; integrated security=SSPI"; protected void Page_Load(object sender, EventArgs e) { bindData(); } private void bindData() { DataTable dt = new DataTable(); using (SqlConnection conn = new SqlConnection(conStr)) { conn.Open(); String query = "select * from tb"; using (SqlCommand cmd = new SqlCommand(query, conn)) { SqlDataAdapter sda = new SqlDataAdapter(cmd); sda.Fill(dt); TableData.DataSource = dt; TableData.DataBind(); } } } protected void store_Click(object sender, EventArgs e) { //store data to table1 List<String> Pros = getProcess(); for (int i = 0; i < Pros.Count; i++) { try { using (SqlConnection conn = new SqlConnection(conStr)) { conn.Open(); String insertQuery = "insert into table1 (Process,RefNo) values(@Process,@RefNo)"; using (SqlCommand cmd = new SqlCommand(insertQuery, conn)) { cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@Process", Pros[i]); cmd.Parameters.AddWithValue("@RefNo", i + 1); cmd.ExecuteNonQuery(); } } } catch (Exception) { //do something here } } //store data to table2 List<Example> allData = getWholeGrid(); for (int i = 0; i < allData.Count; i++) { try { using (SqlConnection conn = new SqlConnection(conStr)) { conn.Open(); String insertQuery = "insert into table2 (Process,Item,Qty,RefNo) values(@Process,@Item,@Qty,(select RefNo from table1 where Process=@Process))"; using (SqlCommand cmd = new SqlCommand(insertQuery, conn)) { cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@Process", allData[i].Process); cmd.Parameters.AddWithValue("@Item", allData[i].Item); cmd.Parameters.AddWithValue("@Qty", allData[i].Qty); cmd.ExecuteNonQuery(); } } } catch (Exception) { //do something here } } Response.Write("<script>alert('stroe success')</script>"); } private List<String> getProcess() { List<String> Pros = new List<string>(); foreach (GridViewRow row in TableData.Rows) { String Pro = ((Label)row.FindControl("Process")).Text; if (!Pros.Contains(Pro)) { Pros.Add(Pro); } } return Pros; } private List<Example> getWholeGrid() { List<Example> Examples = new List<Example>(); foreach (GridViewRow row in TableData.Rows) { Example example = new Example(); example.Process = ((Label)row.FindControl("Process")).Text; example.Item = ((Label)row.FindControl("Item")).Text; example.Qty = int.Parse(((Label)row.FindControl("Qty")).Text); Examples.Add(example); } return Examples; } }
Example.cs
public class Example { public String Process { set; get; } public String Item { set; get; } public int Qty { set; get; } }
Result: In page is
When click the button, the data table is
Hope this can help you.
Best regards,
Xudong Peng
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Friday, May 29, 2020 3:40 AM -
User-807418713 posted
Thank You
Friday, May 29, 2020 7:07 AM