Add Auto Generate Column In Grdiview [Answered]RSS

• • Reply
  Gopi.MCA

  Member

  219 Points

  507 Posts

  Add Auto Generate Column In Grdiview

  Sep 21, 2019 01:59 PM|Gopi.MCA|LINK

  Hi

  This is  my gridview look like this

  Supplier	ItemName	Rate	Quantity	Ref No
  Ravi	AA	100	5	k19/16/19
  Ravi	KK	200	10	k19/16/19
  JOHN	CC	300	25	k20/16/19
  Mack	DD	400	20	k21/16/19
  Mack	YY	500	10	k21/16/19
  Mack	DD	400	20	k21/16/19
  Dinesh	HF	600	30	k22/16/19

  I want to save this data to sql table only one time like this below

  Supplier	Ref No
  Ravi	k19/16/19
  JOHN	k20/16/19
  Mack	k21/16/19
  Dinesh	k22/16/19

  how would be c# insert query

  Thanking You

• • Reply
  samwu

  Contributor

  2840 Points

  1194 Posts

  Re: Add Auto Generate Column In Grdiview

  Sep 23, 2019 06:29 AM|samwu|LINK

  Hi Gopi.MCA,

  According to your description, i made demo for you.

  Use select "group by" to filter out the data to be inserted and use "select into" to put the results of the query into another table.

  The code:

  <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
                  <Columns>
                      <asp:BoundField DataField="Supplier" HeaderText="Supplier" SortExpression="Supplier" />
                      <asp:BoundField DataField="ItemName" HeaderText="ItemName" SortExpression="ItemName" />
                      <asp:BoundField DataField="Rate" HeaderText="Rate" SortExpression="Rate" />
                      <asp:BoundField DataField="Quantity" HeaderText="Quantity" SortExpression="Quantity" />
                      <asp:BoundField DataField="Ref_No" HeaderText="Ref_No" SortExpression="Ref_No" />
                  </Columns>
              </asp:GridView>
              <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CaseTestConnectionString %>" SelectCommand="SELECT [Supplier], [ItemName], [Rate], [Quantity], [Ref No] AS Ref_No FROM [Test53]"></asp:SqlDataSource>
             <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />  
  
  
  protected void Button1_Click(object sender, EventArgs e)
          {
              string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
              string query = "SELECT [Supplier],[Ref No] into Test54 FROM Test53 Group by [Supplier],[Ref No]";
              using (SqlConnection con = new SqlConnection(constr))
              {
                  using (SqlCommand cmd = new SqlCommand(query, con))
                  {
                      cmd.Connection = con;
                      con.Open();
                      cmd.ExecuteNonQuery();
                      con.Close();
                  }
              }
          }

  The result:

  

  Best regards,

  Sam

  MSDN Community Support
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
  
• • Reply
  Gopi.MCA

  Member

  219 Points

  507 Posts

  Re: Add Auto Generate Column In Grdiview

  Sep 23, 2019 04:22 PM|Gopi.MCA|LINK

  Hi

  Thanks for your reply

  I want to save this data after bind in gridview

  once gridview bind the gridview data should be copy to one datatable that datatable i want to insert in to data

  Thanking You

• • Reply
  samwu

  Contributor

  2840 Points

  1194 Posts

  Re: Add Auto Generate Column In Grdiview

  Sep 24, 2019 02:21 AM|samwu|LINK

  Hi Gopi.MCA,

  Gopi.MCA

  I want to save this data after bind in gridview

  once gridview bind the gridview data should be copy to one datatable that datatable i want to insert in to data

  Do you want to bind [Supplier] and [Ref No] to a GridView and insert it into the database with the DataTable?

  if so, you can refer to below code. Iterate through the DataTable through the for loop, then insert the data into the database using the insert () values().

  <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
                  <Columns>
                      <asp:BoundField DataField="Supplier" HeaderText="Supplier" SortExpression="Supplier" />
                      <asp:BoundField DataField="ItemName" HeaderText="ItemName" SortExpression="ItemName" />
                      <asp:BoundField DataField="Rate" HeaderText="Rate" SortExpression="Rate" />
                      <asp:BoundField DataField="Quantity" HeaderText="Quantity" SortExpression="Quantity" />
                      <asp:BoundField DataField="Ref_No" HeaderText="Ref_No" SortExpression="Ref_No" />
                  </Columns>
              </asp:GridView>
              <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CaseTestConnectionString %>" SelectCommand="SELECT [Supplier], [ItemName], [Rate], [Quantity], [Ref No] AS Ref_No FROM [Test53]"></asp:SqlDataSource>
          </div>
          <asp:GridView ID="GridView2" runat="server"></asp:GridView>    
  
  
  protected void Page_Load(object sender, EventArgs e)
          {
              if (!IsPostBack)
              {
                  string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
                  string query = "SELECT [Supplier],[Ref No] FROM Test53 Group by [Supplier],[Ref No]";
                  using (SqlConnection con = new SqlConnection(constr))
                  {
                      using (SqlCommand cmd = new SqlCommand(query))
                      {
                          using (SqlDataAdapter sda = new SqlDataAdapter())
                          {
                              cmd.Connection = con;
                              sda.SelectCommand = cmd;
                              using (DataTable dt = new DataTable())
                              {
                                  sda.Fill(dt);
                                  GridView2.DataSource = dt;
                                  GridView2.DataBind();
  
                                  if (dt.Rows.Count > 0)
                                  {
                                      con.Open();
                                      for (int i = 0; i < dt.Rows.Count; i++)
                                      {
                                          cmd.CommandText += "INSERT INTO Test54 ([Supplier],[Ref No]) VALUES ('" + dt.Rows[i]["Supplier"].ToString() + "','" + dt.Rows[i]["Ref No"].ToString() + "'); ";                                        
                                      }
                                      cmd.ExecuteNonQuery();
                                  }
                              }
                          }
                      }
                  }
              }
          }

  Best regards,

  Sam

  MSDN Community Support
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.