ASP.Net GridView Update Command using VB.Net

In the previous tutorial we discussed about ASP.Net GridView events for Edit and cancel commands using VB.net code. RowEditing event sets the GridView mode to edit view. RowCancelingEdit event sets the GridView mode back to the read only view. bindGridView() function has been created in the sample VB.Net code to bind the data with GridView Control. This data binding function is used in both RowEditing and RowCancelingEdit events after assigning the value to the EditIndex property of GridView control. Now in this tutorial we will learn how to use the FindControl function of GridView Control to search the TextBox server controls placed inside the TemplateField and pass its value to the SQL update command to update data in the database. FindControl function accepts the string value as ID of the server control that you want to find inside the GridView TemplateField.

Example

txtCategoryName = CType(GridView1.Rows(e.RowIndex).Cells(0).FindControl("txtCategoryName"), 
                        TextBox)

Further to access the properties associated to the control accessed by using FindControl function you have to type cast the control to its WebControls providing class.

Example

CType(GridView1.Rows(e.RowIndex).Cells(0).FindControl("txtCategoryName"), 
    TextBox)

Above VB.Net code will convert the control to Textbox WebControls class object. Control Type casting will allow you to access the Text property of the textbox control searched using FindControl function that you can pass to the SQL Update query as a new value modified by the user. RowUpdating Update command event of ASP.Net GridView Control can be used to perform data update action.

VB.Net Code for ASP.Net GridView Update Command Event

Protected Sub GridView1_RowUpdating(ByVal sender As Object,
                                    ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs)
    Dim connStr As String = ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString

    ' object created for SqlConnection Class.
    Dim mySQLconnection As New SqlConnection(connStr)

    ' if condition that can be used to check the sql connection
    ' whether it is already open or not.
    If mySQLconnection.State = ConnectionState.Closed Then mySQLconnection.Open()

    Dim mySqlUpdate As New SqlCommand("update categories set categoryName = @categoryName,description = @description where categoryId = @categoryId",
                                        mySQLconnection)

    ' Textbox objects
    Dim txtCategoryName As TextBox
    Dim txtDescription As TextBox

    ' FindControl function used to get the reference to textbox controls
    ' placed inside the EditItemTemplate of GridView control
    txtCategoryName = CType(GridView1.Rows(e.RowIndex).Cells(0).FindControl("txtCategoryName"), TextBox)
    txtDescription = CType(GridView1.Rows(e.RowIndex).Cells(0).FindControl("txtDescription"), TextBox)

    ' parameters passed to the SQL Update Command
    mySqlUpdate.Parameters.Add("@categoryName", SqlDbType.VarChar).Value = txtCategoryName.Text
    mySqlUpdate.Parameters.Add("@description", SqlDbType.VarChar).Value = txtDescription.Text
    mySqlUpdate.Parameters.Add("@categoryId", SqlDbType.Int).Value = Convert.ToInt32(GridView1.DataKeys(e.RowIndex)("categoryId").ToString())

    mySqlUpdate.ExecuteNonQuery()


    ' if condition that can be used to check the sql connection
    ' if it is open then close it.
    If mySQLconnection.State = ConnectionState.Open Then mySQLconnection.Close()

    GridView1.EditIndex = -1
    bindGridViewCategories()
End Sub

Continue to the next tutorial: ASP.Net Select GridView Row using VB.Net to learn how to change the appearance of selected row of the GridView control.