Dynamically update record in database using ASP.Net GridView in VB.Net

Hi samokbowie,

Assuming that your first column for each table consists of Primary Key, so that you can able to update the record based on the Primary Key.

Refer below code.

HTML

<asp:ListBox ID="lstTables" runat="server" Height="237px" DataSourceID="dsTable" DataTextField="name" DataValueField="name" AutoPostBack="True"></asp:ListBox>
<asp:SqlDataSource ID="dsTable" runat="server" ConnectionString="<%$ ConnectionStrings:constr %>" ProviderName="<%$ ConnectionStrings:constr.ProviderName %>" 
    SelectCommand="SELECT name FROM sys.sysobjects WHERE (xtype = 'U') ORDER BY name"></asp:SqlDataSource>
<div>
    <asp:GridView ID="GV1" runat="server" AllowPaging="True" AllowSorting="True" 
        AutoGenerateDeleteButton="True" AutoGenerateEditButton="True" PageSize="25">
        <PagerSettings FirstPageText="First" LastPageText="Last" Mode="NumericFirstLast" />
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server"></asp:SqlDataSource>
</div>

Code

Imports System.Data
Imports System.Data.SqlClient

Partial Class _Default
    Inherits System.Web.UI.Page
    Dim con As SqlConnection = New SqlConnection
    Dim cmd As SqlCommand

    Private constr As String = ConfigurationManager.ConnectionStrings("constr").ToString()
    Private Const ASCENDING As String = " ASC"
    Private Const DESCENDING As String = " DESC"

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
        If Not IsPostBack Then
            GetData()
        End If
    End Sub

    Private Sub GetData()
        Dim table As DataTable = New DataTable()
        Dim tabname = lstTables.SelectedValue
        Using conn As SqlConnection = New SqlConnection(constr)
            Dim sql As String = "SELECT * FROM " + tabname
            Using cmd As SqlCommand = New SqlCommand(sql, conn)
                Using ad As SqlDataAdapter = New SqlDataAdapter(cmd)
                    ad.Fill(table)
                End Using
            End Using
        End Using

        GV1.DataSource = table
        GV1.DataBind()
    End Sub

    Protected Sub lstTables_SelectedIndexChanged(sender As Object, e As EventArgs) Handles lstTables.SelectedIndexChanged
        GetData()
    End Sub

    Protected Sub lstTables_TextChanged(sender As Object, e As EventArgs) Handles lstTables.TextChanged
        GetData()
    End Sub

    Protected Sub GV1_RowEditing(sender As Object, e As GridViewEditEventArgs) Handles GV1.RowEditing
        ' Response.Write("UPDATING....")
        GV1.EditIndex = e.NewEditIndex
        GetData()
    End Sub

    Protected Sub GV1_RowUpdating(sender As Object, e As GridViewUpdateEventArgs) Handles GV1.RowUpdating
        Dim row As GridViewRow = GV1.Rows(e.RowIndex)
        Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using con As SqlConnection = New SqlConnection(constr)
            Dim sql As String = "UPDATE " + lstTables.SelectedValue + " SET "
            For i = 2 To row.Cells.Count - 1
                Dim columnName As String = CType(GV1.HeaderRow.Cells(i).Controls(0), LinkButton).Text
                Dim columnValue As String = CType(row.Cells(i).Controls(0), TextBox).Text
                sql += String.Format("{0} = '{1}', ", columnName, columnValue)
            Next
            sql = sql.Substring(0, sql.LastIndexOf(", "))
            sql += String.Format(" WHERE {0} = '{1}'", CType(GV1.HeaderRow.Cells(1).Controls(0), LinkButton).Text, CType(row.Cells(1).Controls(0), TextBox).Text)
            Using cmd As SqlCommand = New SqlCommand(sql, con)
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
            End Using
        End Using

        GV1.EditIndex = -1
        GetData()
    End Sub

    Protected Sub GV1_Sorting(sender As Object, e As GridViewSortEventArgs) Handles GV1.Sorting
        Dim sortExpression As String = e.SortExpression

        If GridViewSortDirection = SortDirection.Ascending Then
            GridViewSortDirection = SortDirection.Descending
            SortGridView(sortExpression, DESCENDING)
        Else
            GridViewSortDirection = SortDirection.Ascending
            SortGridView(sortExpression, ASCENDING)
        End If
    End Sub

    Protected Sub GV1_RowDataBound(sender As Object, e As GridViewRowEventArgs) Handles GV1.RowDataBound

    End Sub

    Protected Sub GV1_PageIndexChanging(sender As Object, e As GridViewPageEventArgs) Handles GV1.PageIndexChanging
        GV1.PageIndex = e.NewPageIndex
        GV1.DataBind()
    End Sub

    Protected Sub GV1_RowCancelingEdit(sender As Object, e As GridViewCancelEditEventArgs) Handles GV1.RowCancelingEdit
        GV1.EditIndex = -1
        GV1.PageIndex = e.RowIndex()
        GetData()
    End Sub
End Class

Screenshot