Add Auto Generate Column In Grdiview

Question

User-807418713 posted

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

Answer 1

User288213138 posted

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

Answer 2

User-807418713 posted

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

Answer 3

User288213138 posted

Hi 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