[RESOLVED] Bulk Update in Gridview

[sandy15981]

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

<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>

[gep13]

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

[sandy15981]

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.

[gep13]

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

[sandy15981]

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

[gep13]

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

[sandy15981]

But plz can you help, with my current requirement

[gep13]

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

[sandy15981]

Can you give an sample code for this.

[gep13]

Hey,

I haven't got a code sample to hand, but you should be able to search for the concepts that I have suggested, for instance:

http://www.aspdotnetcodes.com/ReadTi...View_Rows.aspx

http://msdn.microsoft.com/en-us/libr...on(VS.71).aspx

http://msdn.microsoft.com/en-us/libr...nd(VS.71).aspx

http://msdn.microsoft.com/en-us/libr...parameter.aspx

If you are having a specific problem then post back.

Gary

[mendhak]

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.

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:

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();