A complete Editable Data Grid in ASP.NET 1.1

This is good piece of code generated by the Web Matrix,
It had a bug that no records could be added if the table is empty.
This is the fixed version. I feel this could help many beginners.


The Script Part

   ' TODO: update the ConnectionString and Command values for your application

    Dim ConnectionString As String = "server=(local);database=pubs;trusted_connection=true"
    Dim SelectCommand As String = "SELECT au_id, au_lname, au_fname from Authors"

    Dim isEditing As Boolean = False

    Sub Page_Load(Sender As Object, E As EventArgs)

        If Not Page.IsPostBack Then

            ' Databind the data grid on the first request only
            ' (on postback, bind only in editing, paging and sorting commands)

            BindGrid()

        End If

    End Sub

    ' ---------------------------------------------------------------
    '
    ' DataGrid Commands: Page, Sort, Edit, Update, Cancel, Delete
    '

    Sub DataGrid_ItemCommand(Sender As Object, E As DataGridCommandEventArgs)

        ' this event fires prior to all of the other commands
        ' use it to provide a more graceful transition out of edit mode

        CheckIsEditing(e.CommandName)

    End Sub

    Sub CheckIsEditing(commandName As String)

        If DataGrid1.EditItemIndex <> -1 Then

            ' we are currently editing a row
            If commandName <> "Cancel" And commandName <> "Update" Then

                ' user's edit changes (If any) will not be committed
                Message.Text = "Your changes have not been saved yet.  Please press update to save your changes, or cancel to discard your changes, before selecting another item."
                isEditing = True

            End If

        End If

    End Sub

    Sub DataGrid_Edit(Sender As Object, E As DataGridCommandEventArgs)

        ' turn on editing for the selected row

        If Not isEditing Then

            DataGrid1.EditItemIndex = e.Item.ItemIndex
            BindGrid()

        End If

    End Sub

    Sub DataGrid_Update(Sender As Object, E As DataGridCommandEventArgs)

        ' update the database with the new values

        ' get the edit text boxes
        Dim id As String = CType(e.Item.Cells(2).Controls(0), TextBox).Text
        Dim lname As String = CType(e.Item.Cells(3).Controls(0), TextBox).Text
        Dim fname As String = CType(e.Item.Cells(4).Controls(0), TextBox).Text

        ' TODO: update the Command value for your application
        Dim myConnection As New SqlConnection(ConnectionString)
        Dim UpdateCommand As SqlCommand = new SqlCommand()
        UpdateCommand.Connection = myConnection

        If AddingNew = True Then
            UpdateCommand.CommandText = "INSERT INTO authors(au_id, au_lname, au_fname, contract) VALUES (@au_id, @au_lname, @au_fname, 0)"
        Else
            UpdateCommand.CommandText = "UPDATE authors SET au_lname = @au_lname, au_fname = @au_fname WHERE au_id = @au_id"
        End If

        UpdateCommand.Parameters.Add("@au_id", SqlDbType.VarChar, 11).Value = id
        UpdateCommand.Parameters.Add("@au_lname", SqlDbType.VarChar, 40).Value = lname
        UpdateCommand.Parameters.Add("@au_fname", SqlDbType.VarChar, 20).Value = fname

        ' execute the command
        Try
            myConnection.Open()
            UpdateCommand.ExecuteNonQuery()

        Catch ex as Exception
            Message.Text = ex.ToString()

        Finally
            myConnection.Close()

        End Try

        ' Resort the grid for new records
        If AddingNew = True Then
            DataGrid1.CurrentPageIndex = 0
            AddingNew = false
        End If

        ' rebind the grid
        DataGrid1.EditItemIndex = -1
        BindGrid()

    End Sub

    Sub DataGrid_Cancel(Sender As Object, E As DataGridCommandEventArgs)

        ' cancel editing

        DataGrid1.EditItemIndex = -1
        BindGrid()

        AddingNew = False

    End Sub

    Sub DataGrid_Delete(Sender As Object, E As DataGridCommandEventArgs)

        ' delete the selected row

        If Not isEditing Then

            ' the key value for this row is in the DataKeys collection
            Dim keyValue As String = CStr(DataGrid1.DataKeys(e.Item.ItemIndex))

            ' TODO: update the Command value for your application
            Dim myConnection As New SqlConnection(ConnectionString)
            Dim DeleteCommand As New SqlCommand("DELETE from authors where au_id='" & keyValue & "'", myConnection)

            ' execute the command
            myConnection.Open()
            DeleteCommand.ExecuteNonQuery()
            myConnection.Close()

            ' rebind the grid
            DataGrid1.CurrentPageIndex = 0
            DataGrid1.EditItemIndex = -1
            BindGrid()

        End If

    End Sub

    Sub DataGrid_Page(Sender As Object, E As DataGridPageChangedEventArgs)

        ' display a new page of data

        If Not isEditing Then

            DataGrid1.EditItemIndex = -1
            DataGrid1.CurrentPageIndex = e.NewPageIndex
            BindGrid()

        End If

    End Sub

    Sub AddNew_Click(Sender As Object, E As EventArgs)

        ' add a new row to the end of the data, and set editing mode 'on'

        CheckIsEditing("")

        If Not isEditing = True Then

            ' set the flag so we know to do an insert at Update time
            AddingNew = True

            ' add new row to the end of the dataset after binding

            ' first get the data
            Dim myConnection As New SqlConnection(ConnectionString)
            Dim myCommand As New SqlDataAdapter(SelectCommand, myConnection)

            Dim ds As New DataSet()
            myCommand.Fill(ds)

            ' add a new blank row to the end of the data
            Dim rowValues As Object() = {"", "", ""}
            ds.Tables(0).Rows.Add(rowValues)

            ' figure out the EditItemIndex, last record on last page
            Dim recordCount As Integer = ds.Tables(0).Rows.Count

            If recordCount > 1 Then

                recordCount -= 1
                DataGrid1.CurrentPageIndex = recordCount \ DataGrid1.PageSize
                DataGrid1.EditItemIndex = recordCount Mod DataGrid1.PageSize
            Else
                DataGrid1.EditItemIndex = 0
            End If

            ' databind
            DataGrid1.DataSource = ds
            DataGrid1.DataBind()

        End If


    End Sub

    ' ---------------------------------------------------------------
    '
    ' Helpers Methods:
    '

    ' property to keep track of whether we are adding a new record,
    ' and save it in viewstate between postbacks

    Property AddingNew() As Boolean

        Get
            Dim o As Object = ViewState("AddingNew")
            If o Is Nothing Then
                Return False
            End If
            Return CBool(o)
        End Get

        Set(ByVal Value As Boolean)
            ViewState("AddingNew") = Value
        End Set

    End Property

    Sub BindGrid()

        Dim myConnection As New SqlConnection(ConnectionString)
        Dim myCommand As New SqlDataAdapter(SelectCommand, myConnection)

        Dim ds As New DataSet()
        myCommand.Fill(ds)

        DataGrid1.DataSource = ds
        DataGrid1.DataBind()

    End Sub

The HTML part

<H2>Editable Data Grid </H2>
<HR SIZE=1>
</FORM>
<FORM runat="server"><?xml:namespace prefix = asp /><asp:datagrid id=DataGrid1 runat="server" DataKeyField="au_id" OnItemCommand="DataGrid_ItemCommand" OnEditCommand="DataGrid_Edit" OnUpdateCommand="DataGrid_Update" OnCancelCommand="DataGrid_Cancel" OnDeleteCommand="DataGrid_Delete" AllowPaging="true" PageSize="6" OnPageIndexChanged="DataGrid_Page" ForeColor="Black" BackColor="White" CellPadding="3" GridLines="None" CellSpacing="1" width="80%"><HEADERSTYLE backcolor="#4A3C8C" forecolor="white" font-bold="True"></HEADERSTYLE><PAGERSTYLE backcolor="#C6C3C6" font-size="smaller" mode="NumericPages" horizontalalign="Right"></PAGERSTYLE><ITEMSTYLE backcolor="#DEDFDE"></ITEMSTYLE><FOOTERSTYLE backcolor="#C6C3C6"></FOOTERSTYLE><COLUMNS><asp:EditCommandColumn ItemStyle-Width="10%" ItemStyle-Font-Size="smaller" EditText="Edit" CancelText="Cancel" UpdateText="Update" ButtonType="LinkButton"></asp:EditCommandColumn><asp:ButtonColumn ItemStyle-Width="10%" ItemStyle-Font-Size="smaller" CommandName="Delete" Text="Delete"></asp:ButtonColumn></COLUMNS></asp:datagrid><BR><asp:LinkButton id=LinkButton1 onclick=AddNew_Click runat="server" Text="Add new item" Font-Size="smaller"></asp:LinkButton><BR><BR><asp:Label id=Message runat="server" width="80%" forecolor="red" enableviewstate="false"></asp:Label></FORM>