1 Attachment(s)
[RESOLVED] Bulk Update in Gridview
Hii
I have a gridview with 2 editable columns, after entering some data in these two columns. I want to update the database on button click.
Below is the code for gridview
Code:
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource2">
<Columns>
<asp:TemplateField HeaderText="ItemID" SortExpression="ItemID">
<EditItemTemplate>
<asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("ItemID") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("ItemID") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="MatName" HeaderText="MatName" SortExpression="MatName" />
<asp:BoundField DataField="Qty" HeaderText="Qty" SortExpression="Qty" />
<asp:BoundField DataField="QtyOrdered" HeaderText="QtyOrdered" SortExpression="QtyOrdered" />
<asp:BoundField DataField="Balance" HeaderText="Balance" ReadOnly="True" SortExpression="Balance" />
<asp:TemplateField HeaderText="QtyOrder" SortExpression="QtyOrder">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("QtyOrder") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:TextBox ID="TextBox4" runat="server" Width="66px"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Price" SortExpression="Price">
<EditItemTemplate>
<asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("Price") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:TextBox ID="TextBox5" runat="server" Width="79px"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:Admin_EstimateConnectionString2 %>" SelectCommand="SELECT PODetails.ItemID, EstMaterials.MatName, EstMaterials.Qty, EstMaterials.QtyOrdered, EstMaterials.Qty - EstMaterials.QtyOrdered AS Balance, PODetails.Qty AS QtyOrder, PODetails.Price FROM EstMaterials INNER JOIN PODetails ON EstMaterials.MatID = PODetails.ItemID">
</asp:SqlDataSource>
Re: Bulk Update in Gridview
Hey,
Set the following property to true for the GridView:
http://msdn.microsoft.com/en-us/libr...ditbutton.aspx
Then handle the RowEditing event:
http://msdn.microsoft.com/en-us/libr...owediting.aspx
Hope that helps!!
Gary
Re: Bulk Update in Gridview
The desired columns in my gridview are already in editable mode as you can see in ScreenShot.
Now i want to do is after filling values in these columns when i click on Save Button which is outside gridview, the database get updated.
Re: Bulk Update in Gridview
Hey,
The problem with the approach that you have though is that you have no way of knowing which rows within the GridView have actually changed, unless you are keeping a track of this separately. Since this is the case, you have no option but to take all the rows in the GridView, and perform an Update statement for each row, which means unnecessary trips to the database. That is why I was suggesting a per row change.
Gary
Re: Bulk Update in Gridview
I understand your point but my requirement is to update all at once.
and my data is coming from two tables
this is my select query
SelectCommand="SELECT PODetails.ItemID, EstMaterials.MatName, EstMaterials.Qty, EstMaterials.QtyOrdered, EstMaterials.Qty - EstMaterials.QtyOrdered AS Balance, PODetails.Qty AS QtyOrder, PODetails.Price FROM EstMaterials INNER JOIN PODetails ON EstMaterials.MatID = PODetails.ItemID
I want to update only 2 fields of "PODetails" table i.e Qty and Price
Re: Bulk Update in Gridview
Hey,
I understand your requirement, but I still think it is a bad idea. You are adding a lot of updates that don't need to happen, which is my opinion is a bad design. Better to keep a track of which cells actually change, and only update those.
Gary
Re: Bulk Update in Gridview
But plz can you help, with my current requirement
Re: Bulk Update in Gridview
Hey,
If you are going to stick with this approach, then you would need to do something like the following:
On the click event of the save button, loop through all the rows in the GridView. For each row, find the controls that contain the values that you require. Create a connection to the database, and create a command object that contains the query that you need update the row of the table. Create a parameter for each part of the query, add the value using the controls from the GridView row, and then execute the query.
Gary
Re: Bulk Update in Gridview
Can you give an sample code for this.
Re: Bulk Update in Gridview
Re: Bulk Update in Gridview
In the save button's click event, loop through the rows. For each row, do a findcontrol. Get the value out, then perform an ExecuteNonQuery against the database for each row. Note that you can't do this with the sqldatasource, you'll have to do it via code.
Code:
foreach (GridViewRow gvrow in GridView1.Rows)
{
TextBox txtQty = gvrow.FindControl("TextBox1") as TextBox;
TextBox txtAmt = gvrow.FindControl("TextBox2") as TextBox;
if(txtQty != null && txtAmt != null)
{
string quantity = txtQty.Text;
string amount = txtAmt.Text;
//Execute a stored procedure
}
}
Example of executing a stored proc:
Code:
SqlConnection sqlConnection1 = new SqlConnection("Your Connection String");
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "StoredProcedureName";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Param1", quantity);
cmd.Parameters.AddWithValue("@Param2", amount);
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();