Gridview Editing Update to MS SQL database

Question

I am having trouble with OnRowUpdating event. It will not save the new data in the GridView1.EditIndex row. It just reverts back to the old value as soon as you press Update.

The OnRowEditing seems to be functioning correctly.

The same goes for OnRowCancelingEdit it seems to be functioning correctly.

ASPX:

<asp:GridView ID="GridView1" runat="server" AllowSorting="true" AutoPostBack="True" AutoGenerateColumns="True" AutoGenerateEditButton="True" OnRowCancelingEdit="Gridview1_OnRowCancelingEdit" OnRowEditing="Gridview1_OnRowEditing" OnRowUpdating="GridView1_OnRowUpdating" 
    AllowPaging="True" PageSize="50" OnPageIndexChanging="GridView1_PageIndexChanging">
</asp:GridView>

Code behind:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    ViewState("pageIndex") = 1
    ViewState("edit") = -1
    ShowGrid()
End Sub
Sub ShowGrid()
    Dim connStr, cmdStr As String
    connStr = connection string works"
    cmdStr = "SELECT * FROM OrbitDates;"
    Dim ds As New DataSet
    Dim dt As New DataTable()
    Try
        Using conn As New SqlConnection(connStr)
            Using cmd As New SqlCommand(cmdStr, conn)
                conn.Open()
                cmd.ExecuteNonQuery()
                Using da As New SqlDataAdapter(cmd)
                    da.Fill(ds)
                    dt = ds.Tables(0)
                    GridView1.EditIndex = Convert.ToInt32(ViewState("edit"))
                    dt.DefaultView.Sort = ViewState("Sort")
                    GridView1.DataSource = dt.DefaultView
                    GridView1.DataBind()
                    GridView1.PageIndex = Convert.ToInt32(ViewState("pageIndex"))
                End Using
                conn.Close()
                cmd.Dispose()
                conn.Dispose()
            End Using
        End Using
    Catch ex As Exception

    End Try
End Sub
Protected Sub GridView1_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs) Handles GridView1.PageIndexChanging
    GridView1.PageIndex = e.NewPageIndex
    ViewState("pageIndex") = e.NewPageIndex.ToString()
    ShowGrid()
End Sub
Protected Sub GridView1_OnRowEditing(ByVal sender As Object, ByVal e As GridViewEditEventArgs)
    GridView1.EditIndex = e.NewEditIndex
    ViewState("edit") = GridView1.EditIndex
    ShowGrid()
End Sub
Protected Sub GridView1_OnRowCancelingEdit(ByVal sender As Object, ByVal e As GridViewCancelEditEventArgs)
    GridView1.EditIndex = -1
    ViewState("edit") = GridView1.EditIndex
    ShowGrid()
End Sub
Protected Sub GridView1_OnRowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)
    Dim connStr, cmdStr As String
    connStr = "connection string works"
    cmdStr = "UPDATE OrbitDates SET (JD=@JD,Xecl1=@Xecl1,Yecl1=@Yecl1,Zecl1=@Zecl1) WHERE ido=@ido;"
    Try
        Using conn As New SqlConnection(connStr)
            Using cmd As New SqlCommand(cmdStr, conn)
                conn.Open()
                cmd.Parameters.AddWithValue("@ido", GridView1.Rows(e.RowIndex).Cells(0).Text)
                cmd.Parameters.AddWithValue("@JD", GridView1.Rows(e.RowIndex).Cells(1).Text)
                cmd.Parameters.AddWithValue("@Xecl1", GridView1.Rows(e.RowIndex).Cells(2).Text)
                cmd.Parameters.AddWithValue("@Yecl1", GridView1.Rows(e.RowIndex).Cells(3).Text)
                cmd.Parameters.AddWithValue("@Zecl1", GridView1.Rows(e.RowIndex).Cells(4).Text)

                cmd.ExecuteNonQuery()
                conn.Close()
                cmd.Dispose()
                conn.Dispose()
            End Using
        End Using
    Catch ex As Exception
        Throw ex
    End Try
    ViewState("edit") = e.RowIndex
    ShowGrid()
End Sub

Answer 1

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    ViewState("pageIndex") = 1
    ViewState("edit") = -1
    ShowGrid()
End Sub
Sub ShowGrid()
    Dim connStr, cmdStr As String
    connStr = connection string works"
    cmdStr = "SELECT * FROM OrbitDates;"
    Dim ds As New DataSet
    Dim dt As New DataTable()
    Try
        Using conn As New SqlConnection(connStr)
            Using cmd As New SqlCommand(cmdStr, conn)
                conn.Open()
                cmd.ExecuteNonQuery()
                Using da As New SqlDataAdapter(cmd)
                    da.Fill(ds)
                    dt = ds.Tables(0)
                    GridView1.EditIndex = Convert.ToInt32(ViewState("edit"))
                    dt.DefaultView.Sort = ViewState("Sort")
                    GridView1.DataSource = dt.DefaultView
                    GridView1.DataBind()
                    GridView1.PageIndex = Convert.ToInt32(ViewState("pageIndex"))
                End Using
                conn.Close()
                cmd.Dispose()
                conn.Dispose()
            End Using
        End Using
    Catch ex As Exception

    End Try
End Sub
Protected Sub GridView1_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs) Handles GridView1.PageIndexChanging
    GridView1.PageIndex = e.NewPageIndex
    ViewState("pageIndex") = e.NewPageIndex.ToString()
    ShowGrid()
End Sub
Protected Sub GridView1_OnRowEditing(ByVal sender As Object, ByVal e As GridViewEditEventArgs)
    GridView1.EditIndex = e.NewEditIndex
    ViewState("edit") = GridView1.EditIndex
    ShowGrid()
End Sub
Protected Sub GridView1_OnRowCancelingEdit(ByVal sender As Object, ByVal e As GridViewCancelEditEventArgs)
    GridView1.EditIndex = -1
    ViewState("edit") = GridView1.EditIndex
    ShowGrid()
End Sub
Protected Sub GridView1_OnRowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs)
    Dim connStr, cmdStr As String
    connStr = "connection string works"
    cmdStr = "UPDATE OrbitDates SET (JD=@JD,Xecl1=@Xecl1,Yecl1=@Yecl1,Zecl1=@Zecl1) WHERE ido=@ido;"
    Try
        Using conn As New SqlConnection(connStr)
            Using cmd As New SqlCommand(cmdStr, conn)
                conn.Open()
                cmd.Parameters.AddWithValue("@ido", GridView1.Rows(e.RowIndex).Cells(0).Text)
                cmd.Parameters.AddWithValue("@JD", GridView1.Rows(e.RowIndex).Cells(1).Text)
                cmd.Parameters.AddWithValue("@Xecl1", GridView1.Rows(e.RowIndex).Cells(2).Text)
                cmd.Parameters.AddWithValue("@Yecl1", GridView1.Rows(e.RowIndex).Cells(3).Text)
                cmd.Parameters.AddWithValue("@Zecl1", GridView1.Rows(e.RowIndex).Cells(4).Text)

                cmd.ExecuteNonQuery()
                conn.Close()
                cmd.Dispose()
                conn.Dispose()
            End Using
        End Using
    Catch ex As Exception
        Throw ex
    End Try
    ViewState("edit") = e.RowIndex
    ShowGrid()
End Sub

Answer 2

The problem is the GridView is overwritten with the old data before executing GridView1_OnRowUpdating because of this code:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    ViewState("pageIndex") = 1
    ViewState("edit") = -1
    ShowGrid()
End Sub

You need to put If Not IsPostBack inside Page_Load:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    If Not IsPostBack Then
        ViewState("pageIndex") = 1
        ViewState("edit") = -1
        ShowGrid()
    End If
End Sub

Try to understand more about ASP.NET Page Life Cycle.