Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

ASP:Gridview bulk edit/update fields

Posted on 2009-05-15
3
960 Views
Last Modified: 2012-06-21
I have a gridview on my web page that has two columns that are editable. These two fields are editable for all rows at the same time. How would I update all the fields that have been changed on the webpage using one update statement when the user clicks an update button? Right now I have an ontextchanged event on each textbox on the gridview. When the text is changed, I have a sub that should run called TextBox_TextChanged. Right now the TextBox_TextChanged event never fires when I change the field.
<asp:GridView ID="dgRateDetailUpdate" runat="server" AutoGenerateColumns="False"
                    DataKeyNames="RC_RateDetID" DataSourceID="SqlDataSource1">
                    <Columns>
                        <asp:BoundField DataField="RC_RateDetID" HeaderText="RC_RateDetID" InsertVisible="False"
                            ReadOnly="True" SortExpression="RC_RateDetID" />
                        <asp:TemplateField Visible="false">
                            <ItemTemplate>
                                <asp:Label ID="lblRCRateDetID" Text='<%# DataBinder.Eval(Container.DataItem,"RC_RateDetID") %>'
                                    runat="server" />
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:BoundField DataField="JS_ActBillCalcID" HeaderText="JS_ActBillCalcID" SortExpression="JS_ActBillCalcID" />
                        <asp:BoundField DataField="RC_ActBillCalcID" HeaderText="RC_ActBillCalcID" SortExpression="RC_ActBillCalcID" />
                        <asp:BoundField DataField="RC_CreateDate" HeaderText="RC_CreateDate" SortExpression="RC_CreateDate" />
                        <asp:BoundField DataField="CustNo" HeaderText="CustNo" SortExpression="CustNo" />
                        <asp:BoundField DataField="RateDetID" HeaderText="RateDetID" SortExpression="RateDetID" />
                        <asp:BoundField DataField="RateID" HeaderText="RateID" SortExpression="RateID" />
                        <asp:BoundField DataField="CategoryID" HeaderText="CategoryID" SortExpression="CategoryID" />
                        <asp:BoundField DataField="RateTypeID" HeaderText="RateTypeID" SortExpression="RateTypeID" />
                        <asp:BoundField DataField="ConsUnitID" HeaderText="ConsUnitID" SortExpression="ConsUnitID" />
                        <asp:BoundField DataField="RateDescID" HeaderText="RateDescID" SortExpression="RateDescID" />
                        <asp:BoundField DataField="EffectiveDate" HeaderText="EffectiveDate" SortExpression="EffectiveDate" />
                        <asp:BoundField DataField="ExpirationDate" HeaderText="ExpirationDate" SortExpression="ExpirationDate" />
                        <asp:BoundField DataField="RateAmt" HeaderText="RateAmt" SortExpression="RateAmt" />
                        <asp:BoundField DataField="RateAmt2" HeaderText="RateAmt2" SortExpression="RateAmt2" />
                        <asp:BoundField DataField="RateAmt3" HeaderText="RateAmt3" SortExpression="RateAmt3" />
                        <asp:BoundField DataField="FixedAdder" HeaderText="FixedAdder" SortExpression="FixedAdder" />
                        <asp:BoundField DataField="MinDetAmt" HeaderText="MinDetAmt" SortExpression="MinDetAmt" />
                        <asp:BoundField DataField="MaxDetAmt" HeaderText="MaxDetAmt" SortExpression="MaxDetAmt" />
                        <asp:BoundField DataField="GLAcct" HeaderText="GLAcct" SortExpression="GLAcct" />
                        <asp:BoundField DataField="RangeLower" HeaderText="RangeLower" SortExpression="RangeLower" />
                        <asp:BoundField DataField="RangeUpper" HeaderText="RangeUpper" SortExpression="RangeUpper" />
                        <asp:BoundField DataField="CustType" HeaderText="CustType" SortExpression="CustType" />
                        <asp:BoundField DataField="Graduated" HeaderText="Graduated" SortExpression="Graduated" />
                        <asp:BoundField DataField="Progressive" HeaderText="Progressive" SortExpression="Progressive" />
                        <asp:BoundField DataField="AmountCap" HeaderText="AmountCap" SortExpression="AmountCap" />
                        <asp:BoundField DataField="MaxRateAmt" HeaderText="MaxRateAmt" SortExpression="MaxRateAmt" />
                        <asp:BoundField DataField="MinRateAmt" HeaderText="MinRateAmt" SortExpression="MinRateAmt" />
                        <asp:BoundField DataField="CategoryRollup" HeaderText="CategoryRollup" SortExpression="CategoryRollup" />
                        <asp:BoundField DataField="Taxable" HeaderText="Taxable" SortExpression="Taxable" />
                        <asp:BoundField DataField="ChargeType" HeaderText="ChargeType" SortExpression="ChargeType" />
                        <asp:BoundField DataField="MiscData1" HeaderText="MiscData1" SortExpression="MiscData1" />
                        <asp:BoundField DataField="FixedCapRate" HeaderText="FixedCapRate" SortExpression="FixedCapRate" />
                        <asp:BoundField DataField="ScaleFactor1" HeaderText="ScaleFactor1" SortExpression="ScaleFactor1" />
                        <asp:BoundField DataField="ScaleFactor2" HeaderText="ScaleFactor2" SortExpression="ScaleFactor2" />
                        <asp:TemplateField HeaderText="ViterraPickup" SortExpression="ViterraPickup">
                            <ItemTemplate>
                                <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("ViterraPickup") %>' OnTextChanged="TextBox_TextChanged"></asp:TextBox>
                                <asp:CompareValidator runat="server" ID="cvViterraPickup" ControlToValidate="TextBox3"
                                    ErrorMessage="Must be integer, and can not be 0." ValueToCompare="0" Type="Integer" Operator="NotEqual"></asp:CompareValidator>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:BoundField DataField="ISTA_PickupDate" HeaderText="ISTA_PickupDate" SortExpression="ISTA_PickupDate" />
                        <asp:BoundField DataField="ISTA_Notes" HeaderText="ISTA_Notes" SortExpression="ISTA_Notes" />
                        <asp:TemplateField HeaderText="GM_Notes" SortExpression="GM_Notes">
                            <ItemTemplate>
                                <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("GM_Notes") %>' OnTextChanged="TextBox_TextChanged"
                                    ></asp:TextBox>
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:BoundField DataField="RC_Note" HeaderText="RC_Note" SortExpression="RC_Note" />
                        <asp:BoundField DataField="RC_Status" HeaderText="RC_Status" SortExpression="RC_Status" />
                        <asp:BoundField DataField="PremNo" HeaderText="PremNo" SortExpression="PremNo" />
                        <asp:BoundField DataField="ISTA_RateDetID" HeaderText="ISTA_RateDetID" SortExpression="ISTA_RateDetID" />
                        <asp:BoundField DataField="PremID" HeaderText="PremID" SortExpression="PremID" />
                    </Columns>
                    <HeaderStyle BackColor="#04489D" Font-Bold="true" Font-Size="10pt" ForeColor="white"
                        Font-Names="Arial" VerticalAlign="top" />
                </asp:GridView>
                <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:StikeData %>"
                    SelectCommand="SELECT * FROM [RateDetail_Update] WHERE (([ViterraPickup] <> @ViterraPickup) AND ([ViterraPickup] <> @ViterraPickup2) AND ([ViterraPickup] <> @ViterraPickup3) AND ([ViterraPickup] <> @ViterraPickup4) AND ([ViterraPickup] <> @ViterraPickup5) AND ([ViterraPickup] <> @ViterraPickup6))">
                    <SelectParameters>
                        <asp:Parameter DefaultValue="0" Name="ViterraPickup" Type="Int32" />
                        <asp:Parameter DefaultValue="9999" Name="ViterraPickup2" Type="Int32" />
                        <asp:Parameter DefaultValue="4" Name="ViterraPickup3" Type="Int32" />
                        <asp:Parameter DefaultValue="99" Name="ViterraPickup4" Type="Int32" />
                        <asp:Parameter DefaultValue="5555" Name="ViterraPickup5" Type="Int32" />
                        <asp:Parameter DefaultValue="6666" Name="ViterraPickup6" Type="Int32" />
                    </SelectParameters>
                </asp:SqlDataSource>
 
 
Public rowChanged() As Boolean
 
Protected Sub TextBox_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs)
        Dim thisTextBox As TextBox = sender
        Dim row As Integer
        Dim thisGridViewRow As GridViewRow
        thisGridViewRow = thisTextBox.Parent.Parent
 
        row = thisGridViewRow.RowIndex
        rowChanged(row) = True
 
    End Sub
 
Protected Sub btnSaveRateDetails_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSaveRateDetails.Click
        'If Page.IsPostBack Then
        Dim totalRows As Integer = dgRateDetailUpdate.Rows.Count
        Dim r As Integer
        Dim SRCommand1 As New System.Data.SqlClient.SqlCommand()
        Dim SQL_RateDetailUpdate As String
        Dim SRConn1 As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("StikeData").ConnectionString)
        If SRConn1.State <> Data.ConnectionState.Open Then
            SRConn1.Open()
        End If
        For r = 0 To totalRows
            If rowChanged(r) = True Then
                Dim thisGridViewRow As GridViewRow
                thisGridViewRow = dgRateDetailUpdate.Rows(r)
                Dim lbl1 As Label
                lbl1 = thisGridViewRow.FindControl("lblRCRateDetID")
                Dim pk As String = lbl1.Text
                Dim tb1 As TextBox
                tb1 = thisGridViewRow.FindControl("TextBox3")
                Dim cell1 As String
                cell1 = tb1.Text
                Dim tb2 As TextBox
                tb2 = thisGridViewRow.FindControl("TextBox4")
                Dim cell2 As String
                cell2 = tb2.Text
 
                SQL_RateDetailUpdate = " UPDATE dbo.RateDetailUpdate SET " _
                & " ViterraPickup = " & CInt(cell1) & ", GM_Notes = '" & cell2 & "' " _
                & " WHERE RC_RateDetID = " & CInt(pk) & "; "
 
                SRCommand1.CommandTimeout = 300
                SRCommand1.Connection = SRConn1
                SRCommand1.CommandText = SQL_RateDetailUpdate
                SRCommand1.ExecuteNonQuery()
 
            End If
            r = r + 1
        Next
        dgRateDetailUpdate.DataBind()
        If SRConn1.State <> Data.ConnectionState.Closed Then
            SRConn1.Close()
        End If
        'End If
    End Sub

Open in new window

0
Comment
Question by:utlonghornjulie
  • 2
3 Comments
 
LVL 11

Expert Comment

by:Muhammad Ousama Ghazali
ID: 24400397
What I understood from your questions is:
  1. You need to update database with changes in multiple records which are actually shown in a GridView.
  2. Trying to have TextBox_TextChanged event to fire.
To answer your first question I'll get back to you later but just keep in mind that GridView does/can not hold changes on the client for multiple records but allows you to have an Edit-Update-Cancel button/column to acheive per-record server-side based events which allows you to write your logic to appropriately handle the changes in your GridView data. If you need, I'll point you to some resources to do it and/or provide you with sample code.
For the second question, I am copying the following from one of my earlier posts:
Further, as per my knowledge there are events such As TextChanged which are not fired automatically (means they don't create postback from client), although when some other control having postback functionality such as a button will create a postback, the TextChanged property of the textbox will be executed.
Although not related to Textbox but Hyperlink but the same could be done for Textbox by using a hidden button, I have answered on relevent but not exact question to use such as trick. Please see here:
[i]http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_24412160.html[/i]
0
 
LVL 14

Expert Comment

by:Dustin Hopkins
ID: 24402872
Well, you could set your update command in the same datasource the feeds the gridview. Then place a save all button somewhere on the page that calls an update function in the code behind.

Hope this helps,
Dustin
'Update function
Sub update()
dim ind as integer
dim row as GridViewRow
for each row in dgRateDetailUpdate.Rows
ind = row.RowIndex
dgRateDetailUpdate.updaterow(ind,false)
next
End Sub

Open in new window

0
 
LVL 11

Accepted Solution

by:
Muhammad Ousama Ghazali earned 500 total points
ID: 24403971
Here is a link on MSDN for a walkthrough to perform bulk update from a GridView: http://msdn.microsoft.com/en-us/library/aa992036.aspx.
Please follow the steps and customize where necessary to acheive what you wish to do. If you don't wish to directly have textboxes being shown when the GridView is shown first time, then you may have to take both Labels and Textboxes in ItemTemplate. On first time binding use RowCreated or RowDataBound event and set Visble property of TextBox to false. For getting textboxes in all the rows, create an Edit Mode button on page level and when clicked, it will again bind the GridView with the source, but this time set the Labels Visible property to false while make TextBoxes visible. However, you may to modify/customize the code and steps mentioned in walkthrough.
There are a few more walkthroughs listed at the end of the page which might be helpful.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
how to check group permission on a file located on a remote server ? 3 24
MailAddress in vb 4 29
asp.net web application 3 29
Add a Condition in ASP Repeater 6 34
Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

861 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question