Binaryworld - How to perform UPDATE operation with datagrid. ... [ ASP.net -> Datagrid ]

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<html>

<script language="VB" runat="server">

  Dim MyConnection As SqlConnection

  Sub Page_Load(Sender As Object, E As EventArgs)

    MyConnection = New SqlConnection("server=(local);database=pubs;Trusted_Connection=yes")

    If Not (IsPostBack)
      BindGrid()
    End If
  End Sub

  Sub MyDataGrid_Edit(Sender As Object, E As DataGridCommandEventArgs)

    MyDataGrid.EditItemIndex = CInt(E.Item.ItemIndex)
    BindGrid()
  End Sub

  Sub MyDataGrid_Cancel(Sender As Object, E As DataGridCommandEventArgs)

    MyDataGrid.EditItemIndex = -1
    BindGrid()
  End Sub

  Sub MyDataGrid_Update(Sender As Object, E As DataGridCommandEventArgs)

    Dim DS As DataSet
    Dim MyCommand As SqlCommand

    Dim UpdateCmd As String = "UPDATE Authors SET au_id = @Id, au_lname = @LName, au_fname = @FName, phone = " _
       & " @Phone, address = @Address, city = @City, state = @State, zip = @Zip, contract = @Contract where au_id = @Id"

    MyCommand = New SqlCommand(UpdateCmd, MyConnection)

    MyCommand.Parameters.Add(New SqlParameter("@Id", SqlDbType.NVarChar, 11))
    MyCommand.Parameters.Add(New SqlParameter("@LName", SqlDbType.NVarChar, 40))
    MyCommand.Parameters.Add(New SqlParameter("@FName", SqlDbType.NVarChar, 20))
    MyCommand.Parameters.Add(New SqlParameter("@Phone", SqlDbType.NChar, 12))
    MyCommand.Parameters.Add(New SqlParameter("@Address", SqlDbType.NVarChar, 40))
    MyCommand.Parameters.Add(New SqlParameter("@City", SqlDbType.NVarChar, 20))
    MyCommand.Parameters.Add(New SqlParameter("@State", SqlDbType.NChar, 2))
    MyCommand.Parameters.Add(New SqlParameter("@Zip", SqlDbType.NChar, 5))
    MyCommand.Parameters.Add(New SqlParameter("@Contract", SqlDbType.NVarChar,1))

    MyCommand.Parameters("@Id").Value = MyDataGrid.DataKeys(CInt(E.Item.ItemIndex))

    Dim Cols As String() = {"@Id","@LName","@FName","@Phone","@Address","@City","@State","@Zip","@Contract"}

    Dim NumCols As Integer = E.Item.Cells.Count

    Dim I As Integer
    For I=2 To NumCols-2 'skip first, second and last column

      Dim CurrentTextBox As TextBox
      CurrentTextBox = E.Item.Cells(I).Controls(0)
      Dim ColValue As String = CurrentTextBox.Text

      ' Check for null values in required fields
      If I<6 And ColValue = ""

        Message.InnerHtml = "ERROR: Null values not allowed for Author ID, Name or Phone"
        Message.Style("color") = "red"
        Return
      End If

      MyCommand.Parameters(Cols(I-1)).Value = Server.HtmlEncode(ColValue)
    Next

    ' Append last row, converting true/false values to 0/1
    Dim ContractTextBox As TextBox
    ContractTextBox = E.Item.Cells(NumCols-1).Controls(0)
    If ContractTextBox.Text = "true"
          MyCommand.Parameters("@Contract").Value = "1"
    Else
          MyCommand.Parameters("@Contract").Value = "0"
    End If

    MyCommand.Connection.Open()

    Try
      MyCommand.ExecuteNonQuery()
      Message.InnerHtml = "<b>Record Updated</b><br>" & UpdateCmd.ToString()
      MyDataGrid.EditItemIndex = -1
    Catch Exp As SQLException
      If Exp.Number = 2627
        Message.InnerHtml = "ERROR: A record already exists with the same primary key"
      Else
        Message.InnerHtml = "ERROR: Could not update record, please ensure the fields are correctly filled out"
      End If
      Message.Style("color") = "red"
    End Try

    MyCommand.Connection.Close()

    BindGrid()
  End Sub

  Sub MyDataGrid_ItemDataBound(Sender As Object, E As DataGridItemEventArgs)
    If (e.Item.ItemType = ListItemType.EditItem) Then
      Dim i As Integer
      For i = 0 To e.Item.Controls.Count-1
        If (e.Item.Controls(i).Controls(0).GetType().ToString() = "System.Web.UI.WebControls.TextBox") Then
          Dim tb As TextBox
          tb = e.Item.Controls(i).Controls(0)
          tb.Text = Server.HtmlDecode(tb.Text)
        End If
      Next
    End If
  End Sub

  Sub BindGrid()

    Dim DS As DataSet
    Dim MyCommand As SqlDataAdapter
    MyCommand = new SqlDataAdapter("select * from Authors", MyConnection)

    DS = new DataSet()
    MyCommand.Fill(DS, "Authors")

    MyDataGrid.DataSource=DS.Tables("Authors").DefaultView
    MyDataGrid.DataBind()
  End Sub

</script>

<body style="font: 10pt verdana">

<form runat="server">

  <h3><font face="Verdana">Updating a Row of Data</font></h3>

  <span id="Message" EnableViewState="false" style="font: arial 11pt;" runat="server"/><p>

  <ASP:DataGrid id="MyDataGrid" runat="server"
   Width="800"
   BackColor="#ccccff"
   BorderColor="black"
   ShowFooter="false"
   CellPadding=3
   CellSpacing="0"
   Font-Name="Verdana"
   Font-Size="8pt"
   HeaderStyle-BackColor="#aaaadd"
   OnEditCommand="MyDataGrid_Edit"
   OnCancelCommand="MyDataGrid_Cancel"
   OnUpdateCommand="MyDataGrid_Update"
   DataKeyField="au_id"
   OnItemDataBound="MyDataGrid_ItemDataBound"
  >

   <Columns>
    <asp:EditCommandColumn EditText="Edit" CancelText="Cancel" UpdateText="Update" ItemStyle-Wrap="false"/>
   </Columns>

  </ASP:DataGrid>

</form>

</body>
</html>