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

Who is Participating?
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
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.
Hi ,

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.