Update Gridview Row based Database table record using VB.Net in ASP.Net

Hi jamess,

I have created sample refer the below code.

HTML

<div>
    <asp:GridView ID="GView1" runat="server" AutoGenerateColumns="false" OnRowDataBound="RowDataBound">
        <Columns>
            <asp:BoundField DataField="PoNo" HeaderText="Po No." />
            <asp:BoundField DataField="QTYOrdered" HeaderText="Ordered" />
            <asp:BoundField DataField="QTYDelivered" HeaderText="Delivered" />
        </Columns>
    </asp:GridView>
    <br />
    <asp:Button ID="Button1" Text="Submit" runat="server" OnClick="buttonSubmit_Click" />
</div>

Code

Protected Sub Page_Load(sender As Object, e As EventArgs)
    If Not Me.IsPostBack Then
            ' Here I have fetched all the reords for PoNo = "PO#-0014"
        PopulateGrid("PO#-0014")
    End If
End Sub

Private Sub PopulateGrid(poNo As String)
    Dim constr As String = ConfigurationManager.ConnectionStrings("ConnString").ConnectionString
    Using con As New SqlConnection(constr)
        Using cmd As New SqlCommand("SELECT PoNo,ItemName,QTYOrdered,QTYDelivered FROM tbl2 WHERE PoNo = @PoNo", con)
            cmd.Parameters.AddWithValue("@PoNo", poNo)
            con.Open()
            Dim da As New SqlDataAdapter(cmd)
            Dim dt As New DataTable()
            da.Fill(dt)
            GView1.DataSource = dt
            GView1.DataBind()
        End Using
    End Using
End Sub

Protected Sub buttonSubmit_Click(sender As Object, e As EventArgs)
    Dim constr As String = ConfigurationManager.ConnectionStrings("ConnString").ConnectionString
    Using con As New SqlConnection(constr)
        For Each row As GridViewRow In GView1.Rows
            Dim status As String = String.Empty
            Dim rowIndex As Integer = row.RowIndex + 1
            Dim poNo As String = row.Cells(0).Text.Trim()
            Dim qtyordered As String = row.Cells(1).Text.Trim()
            Dim qtydelivered As String = row.Cells(2).Text.Trim()
            If Not String.IsNullOrEmpty(qtydelivered) AndAlso Not String.IsNullOrEmpty(qtydelivered) Then
                If qtyordered = qtydelivered Then

                    status = "Fully Delivered"
                ElseIf qtyordered <> qtydelivered Then
                    status = "Partially Delivered"
                End If
                Using cmd As New SqlCommand("UPDATE T SET T.[Status] = @Status FROM" & vbCr & vbLf & "(" & vbCr & vbLf & vbTab & " SELECT ROW_NUMBER() OVER (ORDER BY(SELECT 0)) AS Id,[Status]" & vbCr & vbLf & vbTab & " FROM tbl2 WHERE PoNo = @PoNo" & vbCr & vbLf & ") as T" & vbCr & vbLf & " WHERE T.Id = @RowIndex", con)
                    con.Open()
                    cmd.CommandType = CommandType.Text
                    cmd.Parameters.AddWithValue("@Status", status)
                    cmd.Parameters.AddWithValue("@RowIndex", rowIndex)
                    cmd.Parameters.AddWithValue("@PoNo", poNo)
                    cmd.ExecuteNonQuery()
                    con.Close()
                End Using
            End If
        Next
    End Using
    Response.Redirect(Request.Url.AbsoluteUri)
End Sub

Protected Sub RowDataBound(sender As Object, e As GridViewRowEventArgs)
    If e.Row.RowType = DataControlRowType.DataRow Then
        Dim qtyordered As String = e.Row.Cells(1).Text.Trim()
        Dim qtydelivered As String = e.Row.Cells(2).Text.Trim()
        If qtyordered = qtydelivered Then
            e.Row.BackColor = Color.Yellow
        End If
    End If
End Sub

SQL query for update the record

UPDATE T SET T.[Status] = @Status FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY(SELECT 0)) AS Id,[Status]
    FROM tbl2 WHERE PoNo = @PoNo
) as T
WHERE T.Id = @RowIndex

Screenshot

DbRecord Before Update:

DbRecord After Submit Button click: