Multirow edit in datagrid, only last row is read

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
             code=Request.QueryString("co")
             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"
             conn.BindGrid(DataGrid1,query)
    response.write(query)
            updatesession()
            

            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"))
            MyCommand.Connection.Open()
            objDR = MyCommand.ExecuteReader()
            if objDR.read 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)
        Conn.Disconnect()
    end sub

' http://www.extremeexperts.com/Net/Articles/ExtendingDataGrid.aspx
' 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
            dgItem.Cells.Add(dgCell)
            dgCell2 = New TableCell()
            dgCell2.ColumnSpan =  3
            dgItem.Cells.Add(dgCell2)
            dgCell2.Text = "Prior Month"
            DataGrid1.Controls(0).Controls.AddAt(0, dgItem)
    End If
End Sub

' http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vstechart/html/vbtchTopQuestionsAboutASPNETDataGridServerControl.asp
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)
            MyCommand.Connection.Open()
            MyCommand.ExecuteNonQuery()
            MyCommand.Connection.Close()


   Next
End Sub


</script>
<html>
<head link="#0000FF" vlink="#0000FF">
<title>Update Estimate To Complete</title>
</head>
<body>
    <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
}
BODY {
      SCROLLBAR-BASE-COLOR: #d7f8ca
}
</style>
        <center>
            <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>
                        <Columns>
                                          <asp:TemplateColumn HeaderText="Project #" ItemStyle-Wrap="false">
                                                <ItemTemplate><asp:Label ID="projectcode" Text='<%# DataBinder.Eval(Container.DataItem, "projectcode") %>' runat="server"/>
                                                </ItemTemplate>
                                          </asp:TemplateColumn>
      
                            <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"/>
                        </ItemTemplate>
                  </asp:TemplateColumn>
                  <asp:TemplateColumn HeaderText="ETC Hrs">
                        <ItemTemplate>
                              <asp:TextBox ID="TBETCh" STYLE="text-align:'right'" width=50 Text='<%# DataBinder.Eval(Container.DataItem, "ETChours") %>' runat="server"/>
                        </ItemTemplate>
                  </asp:TemplateColumn>
                  <asp:TemplateColumn HeaderText="Comments">
                        <ItemTemplate>
                              <asp:TextBox ID="TBcomment" TextMode="MultiLine" Text='<%# DataBinder.Eval(Container.DataItem, "Comment") %>' runat="server"/>
                        </ItemTemplate>
                  </asp:TemplateColumn>
                            <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>

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

            </form>
        </center>
        </font>
    </div>
</body>
</html>
carlcomAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
carlcomAuthor Commented:
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.
SystemExpertCommented:
Hi ,

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

http://123aspx.com/redir.aspx?res=30946

Thanks
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

carlcomAuthor Commented:
That is a nice article which I already read and even downloaded the code.  Never got the "add x rows" link to do anything.
carlcomAuthor Commented:
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-.
carlcomAuthor Commented:
OMG, Connection.vb was connecting to a different server.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.