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
IIS.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >
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
IIS.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today. Learn more >
Member
309 Points
716 Posts
Add Auto Generate Column In Grdiview
Sep 21, 2019 01:59 PM|Gopi.MCA|LINK
Hi
This is my gridview look like this
I want to save this data to sql table only one time like this below
how would be c# insert query
Thanking You
Contributor
3370 Points
1409 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
Member
309 Points
716 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
Contributor
3370 Points
1409 Posts
Re: Add Auto Generate Column In Grdiview
Sep 24, 2019 02:21 AM|samwu|LINK
Hi Gopi.MCA,
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