ASP.NET update the sql data displayed in GridView using Visual Basic language

Why not consider placing ONE save button at the bottom of the GridView, and then allow the user to freely tab around and edit any row (much like in Excel, or even on desktop data grid). Hence, the user can bounce around, tab around and make changes to all rows, and then we have ONE save button.

Above not only presents a common type of UI for editing, but it also dispenses with a convoluted UI and also dispenses with a lot of convoluted code, and trick events for the GridView. Just dump all of the GridView events, and write some clean easy to read code.

Hence, say this markup:

        <style>
            .borderhide input {
                border: none;
                background-color: transparent
            }

            .borderhide textarea {
                border: none;
                background-color: transparent
            }
        </style>


        <asp:GridView ID="GridView1" runat="server"
            AutoGenerateColumns="False" 
            DataKeyNames="ID"
            CssClass="table table-hover borderhide" Width="50%">
            <Columns>
                <asp:TemplateField HeaderText="First"><ItemTemplate>
                    <asp:TextBox ID="txtFirst" runat="server" Text='<%#Eval("FirstName") %>' >
                    </asp:TextBox>
                </ItemTemplate></asp:TemplateField>
                <asp:TemplateField HeaderText="Last"><ItemTemplate>
                    <asp:TextBox ID="txtLast" runat="server" Text='<%#Eval("LastName") %>' >
                    </asp:TextBox>
                </ItemTemplate></asp:TemplateField>
                <asp:TemplateField HeaderText="HotelName"><ItemTemplate>
                    <asp:TextBox ID="txtHotel" runat="server" Text='<%#Eval("HotelName") %>' >
                    </asp:TextBox>
                </ItemTemplate></asp:TemplateField>
                <asp:TemplateField HeaderText="Description">
                    <ItemTemplate>
                        <asp:TextBox ID="txtDescription"
                            runat="server" TextMode="MultiLine"
                            Text='<%# Eval("Description") %>'
                            Height="60px" Width="330px">
                        </asp:TextBox>
                    </ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
        <br />
        <asp:Button ID="cmdSave" runat="server" Text="Save/Done"
            CssClass="btn btn-dark"
            OnClick="cmdSave_Click" />

And our code behind is also rather simple.

Dim rstHotels As New DataTable
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    If Not IsPostBack Then
        LoadData()
        Session("rstHotels") = rstHotels
    Else
        rstHotels = Session("rstHotels")
    End If

End Sub


Sub LoadData()

    Dim strSQL =
        "SELECT * FROM tblHotelsA
        WHERE Active = 1
        ORDER BY HotelName"

    rstHotels = MyRst(strSQL)
    GridView1.DataSource = rstHotels
    GridView1.DataBind()

End Sub

Sub GridToTable()

    ' send grid rows back to persisted table

    For Each gRow As GridViewRow In GridView1.Rows
        If gRow.RowType = DataControlRowType.DataRow Then

            Dim OneRow As DataRow = rstHotels.Rows(gRow.DataItemIndex)
            OneRow("FirstName") = DirectCast(gRow.FindControl("txtFirst"), TextBox).Text
            OneRow("LastName") = DirectCast(gRow.FindControl("txtLast"), TextBox).Text
            OneRow("HotelName") = DirectCast(gRow.FindControl("txtHotel"), TextBox).Text
            OneRow("Description") = DirectCast(gRow.FindControl("txtDescription"), TextBox).Text

        End If
    Next

End Sub

Protected Sub cmdSave_Click(sender As Object, e As EventArgs)

    GridToTable()
    Using conn As New SqlConnection(My.Settings.TEST4)
        Using cmdSQL As New SqlCommand("SELECT * FROM tblHotelsA", conn)

            Dim da As New SqlDataAdapter(cmdSQL)
            Dim daU As New SqlCommandBuilder(da)

            conn.Open()
            da.Update(rstHotels)

        End Using
    End Using

    ' at this point all changes saved to database
    ' we no doubt at this point will navagate to another page.
    'Response.Redirect("Some other page.aspx")

End Sub

So, end result is like this:

So, user can tab around, make changes. And then we have one save button, and we also only have one save operation for sending “all of” the edits back to the database.

Also, since one fast becomes tired of typing code over and over to get some table data, then I have these 2 global helper routines that I placed in a standard code module.

Public Function MyRst(strSQL As String, Optional strCon As String = "") As DataTable
    ' general get any data from SQL

    If strCon = "" Then
        strCon = GetConStr()    ' my defualt connection string
    End If
    Dim rstData As New DataTable
    Using conn As New SqlConnection(strCon)
        Using cmdSQL As New SqlCommand(strSQL, conn)
            conn.Open()
            rstData.Load(cmdSQL.ExecuteReader)
            rstData.TableName = strSQL
        End Using
    End Using
    Return rstData
End Function

Public Function MyRstP(cmdSQL As SqlCommand, Optional strCon As String = "") As DataTable
    ' general get any data from SQL command
    If strCon = "" Then strCon = GetConStr()

    Dim rstData As New DataTable
    Using conn As New SqlConnection(strCon)
        Using (cmdSQL)
            cmdSQL.Connection = conn
            conn.Open()
            rstData.Load(cmdSQL.ExecuteReader)
        End Using
    End Using

    Return rstData

End Function