Start Free Trial

asked on

Update Statement to Update Multiple Records

I have a Web Application that has an Edit Page which recalls the record set of the transaction you want to edit. Within the record set there can be as many as 50 records. I to have a function that would allow a user to update one of the fields on all the records within the record set with the same information say from a text box or a drop down list. How would I accomplish this on the code behind page?

If you would be so kind as to provide complete code on this function it would be greatly appreciated.

You didn't mention ASP.NET framework version, or what control you are using (GridView assumed)...

ASKER

ASP.Net 2.0 and Yes GridView1

If the GridView.DataSource property is set, you can loop through the GridView rows to get the data:

Example:

For Each row As GridViewRow In Me.GridView1.Rows
Dim drv As DataRowView = TryCast(row.DataItem, DataRowView)

If drv IsNot Nothing Then
Dim name As String = drv("Name").ToString()
End If
Next row

ASKER

Ok but where am I changing the field? Example if the field I want to change is called SEC and all the data in the Record Set for the SEC field all say "TEST" and I want to change it to "COMPLETED". How would that fit in to the previous response. Thanks

Updated example:

For Each row As GridViewRow In Me.GridView1.Rows
Dim drv As DataRowView = TryCast(row.DataItem, DataRowView)

If drv IsNot Nothing Then
drv("SEC") = "COMPLETED"
End If
Next row

ASKER

So here's the code, which didn't update the records:

Protected Sub SECBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles SECBtn.Click

For Each row As GridViewRow In Me.GridView1.Rows
Dim drv As DataRowView = TryCast(row.DataItem, DataRowView)

If drv IsNot Nothing Then
drv("SEC") = "COMPLETED"
End If
Next row

End Sub

That will only update the GridView--it won't write the changes to the database (if that's what you are asking)...

ASKER

That is what I was asking. How is it that I can have an update feature to change a field within all the records within the record set displayed in the gridview. This feature will update one field within all records instead of the user editing each single record.

Sometimes, I get caught up in the words, and lose the meaning...here I thought you want to update the GridView, and what you were really looking for is a way to write those changes to the database (d'oh).

I need to know (and not assume) what the GridView is bound to?

ASKER

Hope about if I just send u the code:

SelectCommand="SELECT DISTINCT [REQUESTOR_FNAME], [REQUESTOR_LNAME], [REQUESTOR_PHONE], [RRUU], [FIELD_APPROVER_NAME], [FIELD_APPROVED_DATE], [DATE_SUBMITTED] FROM [AA_HEADER] WHERE ([ID] = @ID)">
           <SelectParameters>
                <asp:ControlParameter ControlID="TextBox1" Name="ID" PropertyName="Text" Type="Int32" />
            </SelectParameters>
                </asp:SqlDataSource>
 
        &nbsp;<asp:GridView ID="GridView1" BorderColor="Black" BorderWidth="2px" BorderStyle="Solid" RowStyle-BorderColor="Black" RowStyle-BorderStyle="Solid" RowStyle-BorderWidth="1" AlternatingRowStyle-BackColor="Tan" runat="server" AllowPaging="True"
            AutoGenerateColumns="False" DataKeyNames="ID,AAID" DataSourceID="SqlDataSource1">
            <Columns>
                <asp:CommandField ShowEditButton="True" />
                <asp:BoundField DataField="ID" Visible="False" HeaderText="ID" InsertVisible="False" ReadOnly="True"
                    SortExpression="ID" />
                <asp:BoundField DataField="AAID" Visible="False" HeaderText="AAID" ReadOnly="True" SortExpression="AAID" />
                
                <asp:TemplateField HeaderText="ASC BRANCH" Visible="False">
                    <EditItemTemplate>
                        <asp:TextBox ID="DOCLOAD_SEC1" Visible="false" runat="server" Text='<%# Bind("DOCLOAD_SEC1") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="DOCLOAD_SEC1Label" Width="50" Visible="false" runat="server" Text='<%# Bind("DOCLOAD_SEC1") %>'></asp:Label>
                    </ItemTemplate>
                    <ItemStyle HorizontalAlign="Center" />
                    <HeaderStyle HorizontalAlign="Center"/>
                </asp:TemplateField>
 
                <asp:TemplateField HeaderText="LINE NO">
                    <EditItemTemplate>
                    <asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" validationexpression="^(050)|(0)[0-4][0-9]$" ErrorMessage="You must Enter the Line Number as 001, 010, 024 and Can't Exceed 50 Lines." ControlToValidate="Line_No" Display="Dynamic" CssClass="error" Font-Size="Small"></asp:RegularExpressionValidator>
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator1" Enabled="True" runat="server" ErrorMessage="Required Entry" ControlToValidate="Line_No" Display="Dynamic" CssClass="error" Font-Size="Small" />
                    <asp:TextBox ID="LINE_NO" ReadOnly="true" MaxLength="3" runat="server" Text='<%# Bind("LINE_NO") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="LINE_NOLabel" Width="50" runat="server" Text='<%# Bind("LINE_NO") %>'></asp:Label>
                    </ItemTemplate>
                    <ItemStyle HorizontalAlign="Center" />
                    <HeaderStyle HorizontalAlign="Center"/>
                </asp:TemplateField>
 
                <asp:TemplateField HeaderText="TT">
                    <EditItemTemplate>
                    <asp:RegularExpressionValidator ID="RegularExpressionValidator2" runat="server" validationexpression="^[A-Z]{2,2}" ErrorMessage="Enter Letters in UPPER Case.  You must the two character Transaction Type such as AC, SU, TO and so on." ControlToValidate="TRANS_TYPE" Display="Dynamic" CssClass="error" Font-Size="Small"></asp:RegularExpressionValidator>                    
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ErrorMessage="Required Entry" ControlToValidate="TRANS_TYPE" Display="Dynamic" CssClass="error" Font-Size="Small" />
                    <asp:TextBox ID="TRANS_TYPE" runat="server" Text='<%# Bind("TRANS_TYPE") %>'></asp:TextBox>
                        </EditItemTemplate>
                    <ItemTemplate>
                    <asp:Label ID="TRANS_TYPELabel" Width="50" runat="server" Text='<%# Bind("TRANS_TYPE") %>'></asp:Label>
                        </ItemTemplate>
                    <ItemStyle HorizontalAlign="Center" />
                    <HeaderStyle HorizontalAlign="Center"/>
                </asp:TemplateField>
 
                <asp:TemplateField HeaderText="ERGI">
                    <EditItemTemplate>
                    <asp:RegularExpressionValidator ID="RegularExpressionValidator3" runat="server" validationexpression="^[A-Z]{1,1}" ErrorMessage="Enter Letters in UPPER Case. You must the Single Exp Rev Gl Code. Such as E,R,G" ControlToValidate="Exp_Rev_Gl_Ind" Display="Dynamic" CssClass="error" Font-Size="Small"></asp:RegularExpressionValidator>                    
                    <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ErrorMessage="Required Entry" ControlToValidate="Exp_Rev_Gl_Ind" Display="Dynamic" CssClass="error" Font-Size="Small" />
                    <asp:TextBox ID="Exp_Rev_Gl_Ind" runat="server" Text='<%# Bind("Exp_Rev_Gl_Ind") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="Exp_Rev_Gl_IndLabel" Width="50" runat="server" Text='<%# Bind("Exp_Rev_Gl_Ind") %>'></asp:Label>
                    </ItemTemplate>
                    <ItemStyle HorizontalAlign="Center" />
                    <HeaderStyle HorizontalAlign="Center"/>
                </asp:TemplateField>
 
                <asp:TemplateField HeaderText="BFY">
                    <EditItemTemplate>
            <asp:RegularExpressionValidator ID="RegularExpressionValidator4" runat="server" validationexpression="^[0-9]{2,2}$" ErrorMessage="Enter as YY. Example 08" ControlToValidate="BEG_BFY" Display="Dynamic" CssClass="error" Font-Size="Small"></asp:RegularExpressionValidator>
                        <asp:RequiredFieldValidator ID="RequiredFieldValidator4" Enabled="False" runat="server" ErrorMessage="Required Entry" ControlToValidate="BEG_BFY" Display="Dynamic" CssClass="error" Font-Size="Small" />                    
                        <asp:TextBox ID="BEG_BFY" MaxLength="2" runat="server" Text='<%# Bind("BEG_BFY") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="BEG_BFYLabel" Width="50" runat="server" Text='<%# Bind("BEG_BFY") %>'></asp:Label>
                    </ItemTemplate>
                    <ItemStyle HorizontalAlign="Center" />
                    <HeaderStyle HorizontalAlign="Center"/>
                </asp:TemplateField>
 
                <asp:TemplateField HeaderText="EFY">
                    <EditItemTemplate>
            <asp:RegularExpressionValidator ID="RegularExpressionValidator5" runat="server" validationexpression="^[0-9]{2,2}$" ErrorMessage="Enter as YY. Example 08" ControlToValidate="END_BFY" Display="Dynamic" CssClass="error" Font-Size="Small"></asp:RegularExpressionValidator>
            <asp:RequiredFieldValidator ID="RequiredFieldValidator5" Enabled="False" runat="server" ErrorMessage="Required Entry" ControlToValidate="END_BFY" Display="Dynamic" CssClass="error" Font-Size="Small" />
                        <asp:TextBox ID="END_BFY" MaxLength="2" runat="server" Text='<%# Bind("END_BFY") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="END_BFYLabel" Width="50" runat="server" Text='<%# Bind("END_BFY") %>'></asp:Label>
                    </ItemTemplate>
                    <ItemStyle HorizontalAlign="Center" />
                    <HeaderStyle HorizontalAlign="Center" Width="100px" />
                </asp:TemplateField>
 
                <asp:TemplateField HeaderText="ORGN">
                    <EditItemTemplate>
                <asp:RegularExpressionValidator ID="RegularExpressionValidator6" runat="server" validationexpression="^[0-9]{4,6}$" ErrorMessage="Enter as XXXX. Example 2502, 0401, or 040102" ControlToValidate="Orgn" Display="Dynamic" CssClass="error" Font-Size="Small"></asp:RegularExpressionValidator>
                <asp:RequiredFieldValidator ID="RequiredFieldValidator6" Enabled="True" runat="server" ErrorMessage="Required Entry" ControlToValidate="ORGN" Display="Dynamic" CssClass="error" Font-Size="Small" />                    
                    <asp:TextBox ID="ORGN" MaxLength="6" runat="server" Text='<%# Bind("ORGN") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="ORGNLabel" Width="50" runat="server" Text='<%# Bind("ORGN") %>'></asp:Label>
                    </ItemTemplate>
                    <ItemStyle HorizontalAlign="Center" />
                    <HeaderStyle HorizontalAlign="Center"/>
                </asp:TemplateField>
 
                <asp:TemplateField HeaderText="BOC-REV">
                    <EditItemTemplate>
            <asp:RegularExpressionValidator ID="RegularExpressionValidator7" runat="server" validationexpression="^[0-9]{4,4}$" ErrorMessage="Enter as XXXX. Example: BOC 6271 or REV 1275" ControlToValidate="OBJ_REV_SRCE" Display="Dynamic" CssClass="error" Font-Size="Small"></asp:RegularExpressionValidator>
            <asp:RequiredFieldValidator ID="RequiredFieldValidator7" Enabled="FALSE" runat="server" ErrorMessage="Required Entry" ControlToValidate="OBJ_REV_SRCE" Display="Dynamic" CssClass="error" Font-Size="Small" />
                        <asp:TextBox ID="OBJ_REV_SRCE" MaxLength="4" runat="server" Text='<%# Bind("OBJ_REV_SRCE") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="OBJ_REV_SRCELabel" Width="100" runat="server" Text='<%# Bind("OBJ_REV_SRCE") %>'></asp:Label>
                    </ItemTemplate>
                    <ItemStyle HorizontalAlign="Center" />
                    <HeaderStyle HorizontalAlign="Center" Width="100px" />
                </asp:TemplateField>
                
                <asp:TemplateField HeaderText="JOB CODE">
                    <EditItemTemplate>
                    <asp:RegularExpressionValidator ID="RegularExpressionValidator8" runat="server" validationexpression="^[A-Z0-9\s]{8,8}$" ErrorMessage="Enter Letters in UPPER Case and the entry must be 8 Characters in Length." ControlToValidate="JOB_NUMBER" Display="Dynamic" CssClass="error" Font-Size="Small"></asp:RegularExpressionValidator>                    
                        <asp:TextBox ID="JOB_NUMBER" MaxLength="8" runat="server" Text='<%# Bind("JOB_NUMBER") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="JOB_NUMBERLabel" Width="100" runat="server" Text='<%# Bind("JOB_NUMBER") %>'></asp:Label>
                    </ItemTemplate>
                    <ItemStyle HorizontalAlign="Left" />
                    <HeaderStyle HorizontalAlign="Center"/>
                </asp:TemplateField>                
 
                <asp:TemplateField HeaderText="FUND">
                    <EditItemTemplate>
            <asp:RegularExpressionValidator ID="RegularExpressionValidator9" runat="server" validationexpression="^[A-Z0-9\s]{4,4}$" ErrorMessage="Enter Letters in UPPER Case and the entry must be 4 Characters in Length." ControlToValidate="FUND" Display="Dynamic" CssClass="error" Font-Size="Small"></asp:RegularExpressionValidator>
                        <asp:TextBox ID="FUND" MaxLength="4" runat="server" Text='<%# Bind("FUND") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="FUNDLabel" Width="50" runat="server" Text='<%# Bind("FUND") %>'></asp:Label>
                    </ItemTemplate>
                    <ItemStyle HorizontalAlign="Center" />
                    <HeaderStyle HorizontalAlign="Center"/>
                </asp:TemplateField>
 
                <asp:TemplateField HeaderText="PGMT">
                    <EditItemTemplate>
            <asp:RegularExpressionValidator ID="RegularExpressionValidator10" runat="server" validationexpression="^[A-Z0-9\s]{4,4}$" ErrorMessage="Enter Letters in UPPER Case and the entry must be 4 Characters in Length." ControlToValidate="PGMT" Display="Dynamic" CssClass="error" Font-Size="Small"></asp:RegularExpressionValidator>
            <asp:RequiredFieldValidator ID="RequiredFieldValidator10" Enabled="FALSE" runat="server" ErrorMessage="Required Entry" ControlToValidate="PGMT" Display="Dynamic" CssClass="error" Font-Size="Small" />
                        <asp:TextBox ID="PGMT" runat="server" Text='<%# Bind("PGMT") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="PROGRAMLabel" Width="50" runat="server" Text='<%# Bind("PGMT") %>'></asp:Label>
                    </ItemTemplate>
                    <ItemStyle HorizontalAlign="Center" />
                    <HeaderStyle HorizontalAlign="Center"/>
                </asp:TemplateField>
 
                <asp:TemplateField HeaderText="QTY\HRS">
                    <EditItemTemplate>
            <asp:RegularExpressionValidator ID="RegularExpressionValidator11" runat="server" validationexpression="^[-+]?\d+(\.\d\d)?$" ErrorMessage="Enter the Quanity \ Hours associated with this transaction with a decimal point and two places. Example: 1.25, 0.25" ControlToValidate="HOURS" Display="Dynamic" CssClass="error" Font-Size="Small"></asp:RegularExpressionValidator>
            <asp:RequiredFieldValidator ID="RequiredFieldValidator11" Enabled="false" runat="server" ErrorMessage="Required Entry" ControlToValidate="HOURS" Display="Dynamic" CssClass="error" Font-Size="Small" />                    
                        <asp:TextBox ID="HOURS" runat="server" Text='<%# Bind("HOURS") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="HOURS" Width="75" runat="server" Text='<%# Bind("HOURS") %>'></asp:Label>
                    </ItemTemplate>
                    <ItemStyle HorizontalAlign="Left" />
                    <HeaderStyle HorizontalAlign="Center"/>
                </asp:TemplateField>
 
                <asp:TemplateField HeaderText="RPTG">
                    <EditItemTemplate>
            <asp:RegularExpressionValidator ID="RegularExpressionValidator12" runat="server" validationexpression="^[A-Z0-9]{4,4}$" ErrorMessage="Enter Letters in UPPER Case and the entry must be 4 Characters in Length. Example X801" ControlToValidate="RPTG" Display="Dynamic" CssClass="error" Font-Size="Small"></asp:RegularExpressionValidator>
            <asp:RequiredFieldValidator ID="RequiredFieldValidator12" Enabled="false" runat="server" ErrorMessage="Required Entry" ControlToValidate="RPTG" Display="Dynamic" CssClass="error" Font-Size="Small" />                    
                        <asp:TextBox ID="RPTG" Width="50" runat="server" Text='<%# Bind("RPTG") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="RPTGLabel" runat="server" Text='<%# Bind("RPTG") %>'></asp:Label>
                    </ItemTemplate>
                    <ItemStyle HorizontalAlign="Left" />
                    <HeaderStyle HorizontalAlign="Center"/>
                </asp:TemplateField>
                
                <asp:TemplateField HeaderText="AMOUNT">
                    <EditItemTemplate>
            <asp:RegularExpressionValidator ID="RegularExpressionValidator13" runat="server" validationexpression="^[-+]?\d+(\.\d\d)?$" ErrorMessage="Entry must contain a decimal + 00 and no $ sign. Example: 0.13 Or 100.13 0r -100.13" ControlToValidate="LINE_AMOUNT" Display="Dynamic" CssClass="error" Font-Size="Small"></asp:RegularExpressionValidator>
            <asp:RequiredFieldValidator ID="RequiredFieldValidator13" runat="server" ErrorMessage="Required Entry" ControlToValidate="LINE_AMOUNT" Display="Dynamic" CssClass="error" Font-Size="Small" />                    
                    <asp:TextBox ID="LINE_AMOUNT" runat="server" Text='<%# Bind("LINE_AMOUNT") %>'></asp:TextBox>
                    </EditItemTemplate>
 
                    <ItemTemplate>
                        <%--<asp:Label ID="LINE_AMOUNTLabel" Width="100" runat="server" Text='<%# Bind("LINE_AMOUNT") %>'></asp:Label>--%>
                        <%#GetLineAmount(Decimal.Parse(Eval("LINE_AMOUNT").ToString())).ToString("N2")%>
                        </ItemTemplate>
                        <FooterTemplate>
                         <%# GetTotal().ToString("N2") %>
                        </FooterTemplate>
                        <FooterStyle Font-Bold="True" />
                    <ItemStyle HorizontalAlign="Right" />
                    <HeaderStyle HorizontalAlign="Center"/>
                </asp:TemplateField>
 
                <asp:TemplateField HeaderText="VENDOR ID">
                    <EditItemTemplate>
            <asp:RegularExpressionValidator ID="RegularExpressionValidator14" Enabled="false" runat="server" validationexpression="^[A-Z0-9\s]{10,10}$" ErrorMessage="Enter Letters in UPPER Case and the entry must be 10 Characters in Length. Example: 1234567890" ControlToValidate="VENDOR_ID" Display="Dynamic" CssClass="error" Font-Size="Small"></asp:RegularExpressionValidator>
            <asp:RequiredFieldValidator ID="RequiredFieldValidator14" runat="server" Enabled="false" ErrorMessage="Required Entry" ControlToValidate="VENDOR_ID" Display="Dynamic" CssClass="error" Font-Size="Small" />                    
                        <asp:TextBox ID="VENDOR_ID" runat="server" Text='<%# Bind("VENDOR_ID") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="VENDOR_IDLabel" Width="100" runat="server" Text='<%# Bind("VENDOR_ID") %>'></asp:Label>
                    </ItemTemplate>
                    <ItemStyle HorizontalAlign="Left" />
                    <HeaderStyle HorizontalAlign="Center"/>
                </asp:TemplateField>
 
                <asp:TemplateField HeaderText="VENDOR ADDRESS">
                    <EditItemTemplate>
            <asp:RegularExpressionValidator ID="RegularExpressionValidator15" Enabled="false" runat="server" validationexpression="^[A-Z0-9\s]{1,1}$" ErrorMessage="Enter Letters in UPPER Case and Numbers as is.  The entry must be 1 Character in Length. Example: A" ControlToValidate="Vendor_Address_CD" Display="Dynamic" CssClass="error" Font-Size="Small" >
            </asp:RegularExpressionValidator><asp:RequiredFieldValidator ID="RequiredFieldValidator15" Enabled="false" runat="server" ErrorMessage="Required Entry" ControlToValidate="Vendor_Address_CD" Display="Dynamic" CssClass="error" Font-Size="Small" />                  
                        <asp:TextBox ID="VENDOR_ADDRESS_CD" runat="server" Text='<%# Bind("VENDOR_ADDRESS_CD") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="VENDOR_ADDRESS_CDLabel" runat="server" Text='<%# Bind("VENDOR_ADDRESS_CD") %>'></asp:Label>
                    </ItemTemplate>
                    <ItemStyle HorizontalAlign="Center" />
                    <HeaderStyle HorizontalAlign="Center"/>
                </asp:TemplateField>
 
            </Columns>
            <RowStyle BorderColor="Black" BorderStyle="Solid" BorderWidth="1px" />
            <AlternatingRowStyle BackColor="Tan" />
            </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:********%>"
            DeleteCommand="DELETE FROM [AA_DETAIL] WHERE [ID] = @ID AND [AAID] = @AAID" InsertCommand="INSERT INTO [AA_DETAIL] ([AAID], [DOCLOAD_SEC1], [Exp_Rev_Gl_Ind], [LINE_NO], [TRANS_TYPE], [BEG_BFY], [END_BFY], [FUND], [PGMT], [ORGN], [OBJ_REV_SRCE], [HOURS], [JOB_NUMBER], [RPTG], [VENDOR_ID], [VENDOR_ADDRESS_CD], [LINE_AMOUNT], [LINE_DESCRIPTION], [AGREEMENT_NO], [ADVANCE_FL]) VALUES (@AAID, @DOCLOAD_SEC1, @HDR_EXP_REV_GL_IND, @DOCUMENT_DESCRIPTION, @LINE_NO, @TRANS_TYPE, @BEG_BFY, @END_BFY, @FUND, @PGMT, @ORGN, @OBJ_REV_SRCE, @HOURS, @JOB_NUMBER, @RPTG, @VENDOR_ID, @VENDOR_ADDRESS_CD, @LINE_AMOUNT, @LINE_DESCRIPTION, @AGREEMENT_NO, @ADVANCE_FL)"
            SelectCommand="SELECT [ID], [AAID], [DOCLOAD_SEC1], [Exp_Rev_Gl_Ind], [LINE_NO], [TRANS_TYPE], [BEG_BFY], [END_BFY], [FUND], [PGMT], [ORGN], [OBJ_REV_SRCE], [HOURS], [JOB_NUMBER], [RPTG], [VENDOR_ID], [VENDOR_ADDRESS_CD], [LINE_AMOUNT], [LINE_DESCRIPTION], [AGREEMENT_NO], [ADVANCE_FL] FROM [AA_DETAIL] WHERE ([AAID] = @AAID)"
            UpdateCommand="UPDATE [AA_DETAIL] SET [DOCLOAD_SEC1] = @DOCLOAD_SEC1, [Exp_Rev_Gl_Ind] = @Exp_Rev_Gl_Ind, [LINE_NO] = @LINE_NO, [TRANS_TYPE] = @TRANS_TYPE, [BEG_BFY] = @BEG_BFY, [END_BFY] = @END_BFY, [FUND] = @FUND, [PGMT] = @PGMT, [ORGN] = @ORGN, [OBJ_REV_SRCE] = @OBJ_REV_SRCE, [HOURS] = @HOURS, [JOB_NUMBER] = @JOB_NUMBER, [RPTG] = @RPTG, [VENDOR_ID] = @VENDOR_ID, [VENDOR_ADDRESS_CD] = @VENDOR_ADDRESS_CD, [LINE_AMOUNT] = @LINE_AMOUNT, [LINE_DESCRIPTION] = @LINE_DESCRIPTION, [AGREEMENT_NO] = @AGREEMENT_NO, [ADVANCE_FL] = @ADVANCE_FL WHERE [ID] = @ID AND [AAID] = @AAID">
            <DeleteParameters>
                <asp:Parameter Name="ID" Type="Int32" />
                <asp:Parameter Name="AAID" Type="Int32" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="DOCLOAD_SEC1" Type="String" />
                <asp:Parameter Name="Exp_Rev_Gl_Ind" Type="String" />
                <asp:Parameter Name="LINE_NO" Type="String" />
                <asp:Parameter Name="TRANS_TYPE" Type="String" />
                <asp:Parameter Name="BEG_BFY" Type="String" />
                <asp:Parameter Name="END_BFY" Type="String" />
                <asp:Parameter Name="FUND" Type="String" />
                <asp:Parameter Name="PGMT" Type="String" />
                <asp:Parameter Name="ORGN" Type="String" />
                <asp:Parameter Name="OBJ_REV_SRCE" Type="String" />
                <asp:Parameter Name="HOURS" Type="String" />
                <asp:Parameter Name="JOB_NUMBER" Type="String" />
                <asp:Parameter Name="RPTG" Type="String" />
                <asp:Parameter Name="VENDOR_ID" Type="String" />
                <asp:Parameter Name="VENDOR_ADDRESS_CD" Type="String" />
                <asp:Parameter Name="LINE_AMOUNT" Type="Decimal" />
                <asp:Parameter Name="LINE_DESCRIPTION" Type="String" />
                <asp:Parameter Name="AGREEMENT_NO" Type="String" />
                <asp:Parameter Name="ADVANCE_FL" Type="String" />
                <asp:Parameter Name="ID" Type="Int32" />
                <asp:Parameter Name="AAID" Type="Int32" />
            </UpdateParameters>
            <SelectParameters>
                <asp:ControlParameter ControlID="TextBox1" Name="AAID" PropertyName="Text" Type="Int32" />
            </SelectParameters>
            <InsertParameters>
                <asp:Parameter Name="AAID" Type="Int32" />
                <asp:Parameter Name="DOCLOAD_SEC1" Type="String" />
                <asp:Parameter Name="Exp_Rev_Gl_Ind" Type="String" />
                <asp:Parameter Name="LINE_NO" Type="String" />
                <asp:Parameter Name="TRANS_TYPE" Type="String" />
                <asp:Parameter Name="BEG_BFY" Type="String" />
                <asp:Parameter Name="END_BFY" Type="String" />
                <asp:Parameter Name="FUND" Type="String" />
                <asp:Parameter Name="PGMT" Type="String" />
                <asp:Parameter Name="ORGN" Type="String" />
                <asp:Parameter Name="OBJ_REV_SRCE" Type="String" />
                <asp:Parameter Name="HOURS" Type="String" />
                <asp:Parameter Name="JOB_NUMBER" Type="String" />
                <asp:Parameter Name="RPTG" Type="String" />
                <asp:Parameter Name="VENDOR_ID" Type="String" />
                <asp:Parameter Name="VENDOR_ADDRESS_CD" Type="String" />
                <asp:Parameter Name="LINE_AMOUNT" Type="Decimal" />
                <asp:Parameter Name="LINE_DESCRIPTION" Type="String" />
                <asp:Parameter Name="AGREEMENT_NO" Type="String" />
                <asp:Parameter Name="ADVANCE_FL" Type="String" />
            </InsertParameters>
        </asp:SqlDataSource>
    
    <div>
        <br />
        <br />
        <br />
        <br />
        <table>
            <tr>

Open in new window

You had to say SqlDataSource, huh? (d'oh).

I have to admit that I don't like those data source classes, since I can't control them very easily. I would have used good ol' ADO.NET technology, where you bind the GridView to a DataSet, update the DataTable instances for the DataSet, and then write the DataSet back to the database. With a DataSet, you can even have data sources from different database types (SQL Server, MySQL, Oracle, ...).

Would it be possible to change the GridView values, and then "update" to force a post-back, and the SqlDataSource to update the database?

ASKER

As long as I still have the ability to edit each record individually, you can change it to whatever accomplishes this project. Unless we just create a separate page to do single column updates.

I found this reference, that might help. I didn't go through it, but the title and description looks promising.

Walkthrough: Performing Bulk Updates to Rows Bound to a GridView Web Server Control
http://msdn.microsoft.com/en-us/library/aa992036(VS.80).aspx

ASKER

I'm still trying to get this to work and understand it. Sorry it is taking so long.

ASKER

Ok I have done the Walkthrought as Instructed but the data I change is not updating in the SQL Database. So were is this going wrong? What am I missing?

Attached is all the code:

ASPX
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>BULK UPDATE PAGE</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        TRANS ID:
        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br />
        <br />
        <br />
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID,AAID"
            DataSourceID="SqlDataSource1">
            <Columns>
                <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True"
                    SortExpression="ID" />
                <asp:BoundField DataField="AAID" HeaderText="AAID" ReadOnly="True" SortExpression="AAID" />
                <asp:TemplateField HeaderText="DOCLOAD_SEC1" SortExpression="DOCLOAD_SEC1">
                    <EditItemTemplate>
                        <asp:TextBox ID="DOCLOAD_SEC1" runat="server" Text='<%# Bind("DOCLOAD_SEC1") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:TextBox ID="Docload_SEC1TextBox" runat="server" Text='<%# Bind("DOCLOAD_SEC1") %>'></asp:TextBox>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="LAST_MODIFY_USER" SortExpression="LAST_MODIFY_USER">
                    <EditItemTemplate>
                        <asp:TextBox ID="LAST_MODIFY_USER" runat="server" Text='<%# Bind("LAST_MODIFY_USER") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        &nbsp;<asp:TextBox ID="LAST_MODIFY_USERTextBox" runat="server" Text='<%# Bind("LAST_MODIFY_USER") %>'></asp:TextBox>
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="DT_LAST_MODIFY" SortExpression="DT_LAST_MODIFY">
                    <EditItemTemplate>
                        <asp:TextBox ID="DT_LAST_MODIFYTextBox" runat="server" Text='<%# Bind("DT_LAST_MODIFY") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        &nbsp;<asp:TextBox ID="DT_LAST_MODIFY" runat="server" Text='<%# Bind("DT_LAST_MODIFY") %>'></asp:TextBox>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:******** %>"
            DeleteCommand="DELETE FROM [AA_DETAIL] WHERE [ID] = @ID AND [AAID] = @AAID" InsertCommand="INSERT INTO [AA_DETAIL] ([AAID], [DOCLOAD_SEC1], [LAST_MODIFY_USER], [DT_LAST_MODIFY]) VALUES (@AAID, @DOCLOAD_SEC1, @LAST_MODIFY_USER, @DT_LAST_MODIFY)"
            SelectCommand="SELECT [ID], [AAID], [DOCLOAD_SEC1], [LAST_MODIFY_USER], [DT_LAST_MODIFY] FROM [AA_DETAIL] WHERE ([AAID] = @AAID)"
            UpdateCommand="UPDATE [AA_DETAIL] SET [DOCLOAD_SEC1] = @DOCLOAD_SEC1, [LAST_MODIFY_USER] = @LAST_MODIFY_USER, [DT_LAST_MODIFY] = @DT_LAST_MODIFY WHERE [ID] = @ID AND [AAID] = @AAID">
            <SelectParameters>
                <asp:ControlParameter ControlID="TextBox1" Name="AAID" PropertyName="Text" Type="Int32" />
            </SelectParameters>
            <DeleteParameters>
                <asp:Parameter Name="ID" Type="Int32" />
                <asp:Parameter Name="AAID" Type="Int32" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="DOCLOAD_SEC1" Type="String" />
                <asp:Parameter Name="LAST_MODIFY_USER" Type="String" />
                <asp:Parameter DbType="Date" Name="DT_LAST_MODIFY" />
                <asp:Parameter Name="ID" Type="Int32" />
                <asp:Parameter Name="AAID" Type="Int32" />
            </UpdateParameters>
            <InsertParameters>
                <asp:Parameter Name="AAID" Type="Int32" />
                <asp:Parameter Name="DOCLOAD_SEC1" Type="String" />
                <asp:Parameter Name="LAST_MODIFY_USER" Type="String" />
                <asp:Parameter DbType="Date" Name="DT_LAST_MODIFY" />
            </InsertParameters>
        </asp:SqlDataSource>
        <br />
        <asp:Button ID="UpdateButton" runat="server" Text="Update" /></div>
    </form>
</body>
</html>
 
ASPX.VB
Partial Class asc_year_end_BulkUpdate
    Inherits System.Web.UI.Page
 
    Private tableCopied As Boolean = False
    Private originalDataTable As System.Data.DataTable
 
    Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
        If e.Row.RowType = DataControlRowType.DataRow Then
            If Not tableCopied Then
                originalDataTable = CType(e.Row.DataItem, System.Data.DataRowView).Row.Table.Copy()
                ViewState("originalValuesDataTable") = originalDataTable
                tableCopied = True
            End If
        End If
    End Sub
 
    Protected Sub UpdateButton_Click(ByVal sender As Object, ByVal e As EventArgs)
        originalDataTable = CType(ViewState("originalValuesDataTable"), System.Data.DataTable)
 
        For Each r As GridViewRow In GridView1.Rows
            If IsRowModified(r) Then GridView1.UpdateRow(r.RowIndex, False)
        Next
 
        ' Rebind the Grid to repopulate the original values table.
        tableCopied = False
        GridView1.DataBind()
 
    End Sub
 
    Protected Function IsRowModified(ByVal r As GridViewRow) As Boolean
        Dim currentID As Integer
        Dim currentDOCLOAD_SEC1 As String
        Dim currentLAST_MODIFY_USER As String
        Dim currentDT_LAST_MODIFY As String
 
        currentID = Convert.ToInt32(GridView1.DataKeys(0).Value)
 
        currentDOCLOAD_SEC1 = CType(r.FindControl("DOCLOAD_SEC1TextBox"), TextBox).Text
        currentLAST_MODIFY_USER = CType(r.FindControl("LAST_MODIFY_USERTextBox"), TextBox).Text
        currentDT_LAST_MODIFY = CType(r.FindControl("DT_LAST_MODIFYTextBox"), TextBox).Text
 
        Dim row As System.Data.DataRow = _
            originalDataTable.Select(String.Format("ID = {0}", currentID))(0)
 
        If Not currentDOCLOAD_SEC1.Equals(row("DOCLOAD_SEC1").ToString()) Then Return True
        If Not currentLAST_MODIFY_USER.Equals(row("LAST_MODIFY_USER").ToString()) Then Return True
        If Not currentDT_LAST_MODIFY.Equals(row("DT_LAST_MODIFY").ToString()) Then Return True
 
        Return False
    End Function
 
End Class

Open in new window

Now comes the fun part--debugging!!

The UpdateButton_Click will need to be checked. Then, the For Each r As GridViiewRow.

This small change will make it easier to place breakpoints:

For Each r As GridViewRow In GridView1.Rows
If IsRowModified(r) Then
GridView1.UpdateRow(r.RowIndex, False)
End If
Next

Put a break-point on this line:

GridView1.UpdateRow(r.RowIndex, False)

ASKER

I'm not sure how to use the point break, but I set the break, ran the app, changed the data and pressed update and nothing changed. In the breakpoint window I get Bulkupdate.aspx.vb, line 29 chararcter 17, Condition (NO CONDITION), Hit Count (break alawys (currently 0).

If you have the break point correct (the first line in the UpdateButton_Click event handler), then you would get a break when the Update button is pressed.

ASKER

As you can see in the attached I have it set as explained.
BreakPoint.doc

Put the break-point on line 24:

originalDataTable = ...

ASKER

Nope, No Change see attached.
BreakPoint.doc

You know what would make this easier--take a screen shot, paste it in something like MS Paint, save it as a .png file, and attach that. Then I could see the image in this question, without opening a Word document.

If your Update button is not raising the Click event, then we certainly have bigger fish to fry...

Where is that control defined in the HTML?

ASKER

LIne 78, see PNG file
Line78.PNG

With VB.NET and ASP.NET, there are three different ways to attach event handlers:

1) In HTML:
<asp:Button ID="UpdateButton" runat="server" Text="Update" OnClick="UpdateButton_Click" />

2) With Handles:

Protected Sub UpdateButton_Click(ByVal sender As Object, ByVal e As EventArgs) Handles UpdateButton.Click

3) With Code

AddHandler UpdateButton.Click, AddressOf UpdateButton_Click

BTW, the next process improvement step would be to insert the HTML from the web page into a code snippet, instead of attaching a .png image in the designer.

ASKER

Ok, so I selected option 2. now the code from the codebehind page now reads:

Btw I set a breakpoint at the Table Copies and it hit that line, the only problem is that there is no table to copy as first the user has to select a transaction id.
Private tableCopied As Boolean = False

    Protected Sub UpdateButton_Click(ByVal sender As Object, ByVal e As EventArgs) Handles UpdateButton.Click
        originalDataTable = CType(ViewState("originalValuesDataTable"), System.Data.DataTable)
 
        For Each r As GridViewRow In GridView1.Rows
            If IsRowModified(r) Then GridView1.UpdateRow(r.RowIndex, False)
        Next
 
        ' Rebind the Grid to repopulate the original values table.
        tableCopied = False
        GridView1.DataBind()
 
    End Sub

Open in new window

"the only problem is that there is no table to copy as first the user has to select a transaction id."

I don't know what that means--what transaction ID?

ASKER

Okay, the screen appears with a textbox to select a transaction that the user wants to modify. After entering the transaction id and clicking submit then the GridView populates. I think that is the issue, because just removed the textbox to enter a transaction id therefore the screen on open calls all transactions from the database and I update the first record and it works just fine.

So know how it that I can call only certain transactions, then after the gridview appears then copy the table and proceed?

You have to remember that I am sitting here, on the outside looking in, and I have no idea what you are talking about...

ASKER

I'll send a picture

The issue is that the way I designed this update was a user was to enter a transaction id to update only a particular record set. The update feature doesn't work because there is no table to copy, because no transactions have been called. See the screen shot of my original and needed way of working.

The second screen shot is the modified version whihc doesn't require a transaction id as all transaction from the database are pulled, therefore a table is copied and become updatable.

Hope you understand.

Thanks

Original-Screen-Calling-A-Transa.bmp
New-Screen-No-Transaction-ID-Cal.bmp

Try to explain the desired processing steps, like this example:

1) Enter a transaction ID

2) Click the <Update> button

3) The page posts back

4) The UpdateButton click event handler is called

...

ASKER

1). User opens the URL
2). A TextBox Appears asking the user to enter the Transaction ID they wish to Update
3). The user presses the Submit Button
4). The GridView is then populated
a). At this point is when the Table Should Be Copied.
5). The user edits, updates the record set as only the Records matching that trans id are displayed.
6). The user presses the Update button.
7). The records are then posted back to the server.

Hope that is clear.

SCREEN-1.bmp
SCREEN-2.bmp
SCREEN-3.bmp

Does the value that you enter into the TextBox relate to all the records in the GridView, or just the record that corresponds to the ID entered?

ASKER

Just the records that correspond to the ID, which happens to be a field called AAID. The second column.

ASKER

Sorry submitted to soon. The records that correspond within the gridview should only be the records that correspond to the record column of AAID

Oooh, the view is waaay too narrow. You just explained that the values to 1-to-many records in the GridView. Before you said, "the only problem is that there is no table to copy as first the user has to select a transaction id.". That sounds like the SqlDataSource hasn't done any work (or has it)...

ASKER

The SqlDataSource hasn't done any work until the user clicks submit. Therefore in the codebhind what happens is when the page is opened it executes the functions

Private tableCopied As Boolean = False
Private originalDataTable As System.Data.DataTable

before there is any table available to copy.

Is there any code to store the DataTable in the ViewState?

ViewState("originalValuesDataTable") = ...

ASKER

Yes:

Private tableCopied As Boolean = False
Private originalDataTable As System.Data.DataTable

Protected Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
If e.Row.RowType = DataControlRowType.DataRow Then
If Not tableCopied Then
originalDataTable = CType(e.Row.DataItem, System.Data.DataRowView).Row.Table.Copy()
ViewState("originalValuesDataTable") = originalDataTable
tableCopied = True
End If
End If
End Sub

Protected Sub UpdateButton_Click(ByVal sender As Object, ByVal e As EventArgs) Handles UpdateButton.Click
originalDataTable = CType(ViewState("originalValuesDataTable"), System.Data.DataTable)

For Each r As GridViewRow In GridView1.Rows
If IsRowModified(r) Then GridView1.UpdateRow(r.RowIndex, False)
Next

' Rebind the Grid to repopulate the original values table.
tableCopied = False
GridView1.DataBind()

End Sub

Protected Function IsRowModified(ByVal r As GridViewRow) As Boolean
Dim currentID As Integer
Dim currentDOCLOAD_SEC1 As String
Dim currentLAST_MODIFY_USER As String

currentID = Convert.ToInt32(GridView1.DataKeys(0).Value)

currentDOCLOAD_SEC1 = CType(r.FindControl("DOCLOAD_SEC1TextBox"), TextBox).Text
currentLAST_MODIFY_USER = CType(r.FindControl("LAST_MODIFY_USERTextBox"), TextBox).Text

Dim row As System.Data.DataRow = _
originalDataTable.Select(String.Format("ID = {0}", currentID))(0)

If Not currentDOCLOAD_SEC1.Equals(row("DOCLOAD_SEC1").ToString()) Then Return True
If Not currentLAST_MODIFY_USER.Equals(row("LAST_MODIFY_USER").ToString()) Then Return True

Return False

End Function

Since the DataTable is created in the GridView1_RowDataBound event handler, and that happens after the SqlDataSource gets data from the source, then you click the <Update> button, then I don't see why you won't have the DataTable created at that point.

ASKER

I believe that the functions
Private tableCopied As Boolean = False
Private originalDataTable As System.Data.DataTable
Should only fire after the GridView has been populated as this is the issue. When I place a breakpoint there it fires as soon as the page is opened, not after the Transaction ID has been entered. Therefore if we move that code that should fix it.

Those lines are just declarations. The first one has an initializer, but it really isn't doing anything. The meat of the process is further down in your code.

ASKER

So shlould I rem them out? What should I do at this point?

Those are needed for the rest of the code to work. They are "module-level variable declarations", that are use further down in the code, and if you remove them, or comment them out, you will get compiler errors (test this if you wish).

ASKER

Okay, so I have provided a lot of information and detail, so where do we go from here? How do we implement a textbox to call the record set and make a copy of the record set for updating?

You have provided a lot of detail, and yet I am still confused. What are you asking for?

ASKER

How is it that I can set this app up so that I can implement the steps that you requested I define for you? I think it's clear given the screen shots of the scenario I have provided you.

The only way this app currently works is if you pull the entire sql table and update all those records. What I want is the ability to call only a selected record set. Not the whole entire table, then proceed with changes and then updates.

I don't know how else to explain this to you.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Thanks for the help!