Multirow edit in datagrid, only last row is read

Posted on 2006-04-11
Last Modified: 2008-02-01
I am trying to get a multirow editable datagrid working.  When I hit the Update button, the last row's edited values show and are sent to server (SQL update statement gets values) but the other rows are reset and those original values show in the update statements.  Also, Some values are not getting dispalyed.  It is very strange, like one column is "Comment" (varchar) and in the datagrid is always empty though the same query in query analyzer shows the text.  Select Comment + '-' Comment from ... will only show "-" in the grid, not the value of the comment before the -.  

There is no code behind.  The page code is:

<%@ Page Language="VB" Inherits="Connection" Src="Connection.vb" description="Connection page" Debug="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>

<script runat="server">

    Public Conn as Connection
    Public code as String
      Public OpenPeriod as String
      Public ThisMonth as String
      Dim MyConnection As SqlConnection

    sub Page_Load(sender as Object, e as EventArgs)
                        AddHandler DataGrid1.ItemCreated, AddressOf DataGrid1_ItemCreated
             Conn=new Connection
             if code = "" then code = "6069531"
             Conn.Connect("the database server")
             Dim query as String
             query = "select a.projectcode, x.projectname, x.ClientLongName, x.BillingAnalyst, x.contract_curr_sym, a.ETC + 1 ETC, a.ETChours +2 ETChours, '-' + a.Comment + '-' Comment, x.contract_curr_sym + CAST(CAST(b.ETC AS INTEGER) AS VARCHAR) pETC, b.ETChours pETChours, b.Comment pComment from fcg$reports.dbo.tbl_ETC a left join fcg$reports.dbo.tbl_ETC b on a.projectid = b.projectid and a.period -1 = b.period join fcg$reports.dbo.tbl_ProjectExInfo x on x.projectid = a.projectid where x.status = 1 and a.code = '" & code & "' order by a.projectcode"

            Dim UpdateCmd as string
            UpdateCmd = "select OpenPeriod, OpenName from fcg$reports.dbo.tblGlobal"
            Dim DS As DataSet
            Dim MyCommand As SqlCommand = New SqlCommand(UpdateCmd, MyConnection)
            Dim objDR as SqlDataReader
            MyConnection = New SqlConnection(Application("ETWebRpts"))
            objDR = MyCommand.ExecuteReader()
            if then
                  ' response.write("<b> OpenName = " & objDR("OpenName").tostring & "</b>")
                  OpenPeriod = objDR("OpenPeriod").tostring
                  ThisMonth = objDR("OpenName").tostring
            end if

    end sub

    sub Page_Unload(e as EventArgs)
    end sub

' This just adds a row above the header
Private Sub DataGrid1_ItemCreated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) ' Handles DataGrid1.ItemCreated
    If e.Item.ItemType = ListItemType.Header Then
            Dim dgItem As DataGridItem
            Dim dgCell As TableCell
            Dim dgCell2 As TableCell
            dgItem = New DataGridItem(0, 0, ListItemType.Header)
            dgCell = New TableCell()
            dgCell.ColumnSpan =  7
            dgCell2 = New TableCell()
            dgCell2.ColumnSpan =  3
            dgCell2.Text = "Prior Month"
            DataGrid1.Controls(0).Controls.AddAt(0, dgItem)
    End If
End Sub

Private Sub btnUpdate_Click(ByVal sender As System.Object, _
      ByVal e As System.EventArgs) ' Handles btnUpdate.Click
      Dim i As Integer
      Dim dgi As DataGridItem
      Dim projectcode As Label
      Dim ETC As TextBox
      Dim ETCh As TextBox
      Dim comment As TextBox
      Dim UpdateCmd as string
      Dim MyCommand As SqlCommand
      MyConnection = New SqlConnection(Application("ETWebRpts"))

      For i = 0 To DataGrid1.Items.Count - 1
       dgi = DataGrid1.Items(i)
       projectcode = CType(dgi.FindControl("projectcode"), Label)
       ETC = CType(dgi.FindControl("TBETC"), TextBox)
       ETCh = CType(dgi.FindControl("TBETCh"), TextBox)
       comment = CType(dgi.FindControl("TBcomment"), TextBox)

response.write("UPDATE fcg$reports.dbo.tbl_ETC SET ETC = " & ETC.Text & ", ETChours = " & ETCh.Text & ", Comment = '" & comment.Text & "' WHERE projectcode = '" & projectcode.Text & "' AND period = " & OpenPeriod & "<br>")

            UpdateCmd = "UPDATE fcg$reports.dbo.tbl_ETC SET ETC = " & ETC.Text & ", ETChours = " & ETCh.Text & ", Comment = '" & comment.Text & "' WHERE projectcode = '" & projectcode.Text & "' AND period = " & OpenPeriod

            MyCommand = New SqlCommand(UpdateCmd, MyConnection)

End Sub

<head link="#0000FF" vlink="#0000FF">
<title>Update Estimate To Complete</title>
    <center><font style='font-size:14pt;font-family:"Tahoma";font-weight:bold;'>Input Form for Estimate to Complete (ETC) on Fixed Price Projects</font><br><font style='font-size:10.5pt;font-family:"Tahoma";'>
      For Project Manager: <br>
      Estimates are for: Summary Period <%=ThisMonth%> through End of Project(s)</font></center>
        <font face="Tahoma" size="-1">

        <style type="text/css">A {
      CURSOR: hand
            <form id="ThisForm" name="ThisForm" action="exclude.aspx" method="post" runat="server">
                <p align="center">
                    <asp:DataGrid id="DataGrid1" runat="server" ShowFooter="True" AutoGenerateColumns="False" font-size="10">
                        <FooterStyle backcolor="#B2C1F1"></FooterStyle>
                        <HeaderStyle horizontalalign="Center" font-bold="True" backcolor="#B2C1F1"></HeaderStyle>
                        <AlternatingItemStyle backcolor="#DAE4F9"></AlternatingItemStyle>
                                          <asp:TemplateColumn HeaderText="Project #" ItemStyle-Wrap="false">
                                                <ItemTemplate><asp:Label ID="projectcode" Text='<%# DataBinder.Eval(Container.DataItem, "projectcode") %>' runat="server"/>
                            <asp:BoundColumn DataField="projectname" ReadOnly="True" HeaderText="Project Name"></asp:BoundColumn>
                            <asp:BoundColumn DataField="ClientLongName" ReadOnly="True" HeaderText="Client Name"></asp:BoundColumn>
                            <asp:BoundColumn DataField="BillingAnalyst" ReadOnly="True" HeaderText="Billing Analyst"></asp:BoundColumn>
                  <asp:TemplateColumn HeaderText="ETC Fees" ItemStyle-Wrap="false">
                        <ItemTemplate><%# DataBinder.Eval(Container.DataItem, "contract_curr_sym") %><asp:TextBox ID="TBETC" STYLE="text-align:'right'" width=50 Text='<%# DataBinder.Eval(Container.DataItem, "ETC") %>' runat="server"/>
                  <asp:TemplateColumn HeaderText="ETC Hrs">
                              <asp:TextBox ID="TBETCh" STYLE="text-align:'right'" width=50 Text='<%# DataBinder.Eval(Container.DataItem, "ETChours") %>' runat="server"/>
                  <asp:TemplateColumn HeaderText="Comments">
                              <asp:TextBox ID="TBcomment" TextMode="MultiLine" Text='<%# DataBinder.Eval(Container.DataItem, "Comment") %>' runat="server"/>
                            <asp:BoundColumn DataField="pETC" ReadOnly="True" HeaderText="ETC Fees"><ItemStyle HorizontalAlign="Right"></ItemStyle></asp:BoundColumn>
                            <asp:BoundColumn DataField="pETChours" ReadOnly="True" HeaderText="ETC Hrs"><ItemStyle HorizontalAlign="Right"></ItemStyle></asp:BoundColumn>
                            <asp:BoundColumn DataField="pComment" ReadOnly="True" HeaderText="Comments"></asp:BoundColumn>

            <asp:Button id="btnUpdate" onclick="btnUpdate_Click" runat="server" Text="Update"></asp:Button>    

Question by:carlcom
    LVL 15

    Accepted Solution

    you need to put all the code in your page_load sub into a "If not IsPostBack" statement otherwise it will execute each time the page loads - including when you postback (this will effectively undo your edits and re-fill the data form db before it writes the new data to db)

    sub page_load
      if not ispostback then
        'all your page load code

      end if
    end sub

    Author Comment

    Did that, now the update moves the values one row down so the first row is blank.  the last row is shown in the footer.  Same with the update statements, first has no values.  This appears related to the DataGrid1_ItemCreated Sub.
    LVL 10

    Expert Comment

    Hi ,

    Plz see the following link.It may closest solution for you


    Author Comment

    That is a nice article which I already read and even downloaded the code.  Never got the "add x rows" link to do anything.

    Author Comment

    I found that if I don't call the DataGrid1_ItemCreated Sub, I don't have the alignment problem.  

    I still have the problem that some columns don't show what the database has.  It is very strange, like one column is "Comment" (varchar) and in the datagrid is always empty though the same query in query analyzer shows the text.  Select Comment + '-' Comment from ... will only show "-" in the grid, not the value of the comment before the -.  Again, query analyzer with the same query shows thecomment-.

    Author Comment

    OMG, Connection.vb was connecting to a different server.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    AJAX ModalPopupExtender has a required property "TargetControlID" which may seem to be very confusing to new users. It means the server control that will be extended by the ModalPopup, for instance, if when you click a button, a ModalPopup displays,…
    Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now