NGWS SDK Documentation  

This is preliminary documentation and subject to change.
To comment on this topic, please send us email at ngwssdk@microsoft.com. Thanks!

Updating Data in a SQL Database

Often it will be desirable to update data fields in an existing database. This section shows how to do this.

In this example, when the page is presented, there is an additional column on the left side of the DataGrid display. It has an "edit" link that, when clicked, causes all the fields in that row to become text boxes showing the existing data. These fields can now be edited to contain the new information. When ready, the user can click the "update" link and the data will be modified in the database.

To update existing data

  1. Import the necessary namespaces.
    <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="System.Data.SQL" %>
    <%@ Import Namespace="System.Text"%>

    Within the <script language="C#" runat="server"> tag, this example implements five functions: Page_Load, MyDataGridEdit, MyDataGridCancel, MyDataGridUpdate, and BindGrid. These functions are described in more detail in the steps that follow.

    <script language="C#" runat="server">
    SQLConnection myConnection;
  2. Implement a Page_Load function that sets up the connection information for a connection to the "pubs" database. It also checks to see if this page is a PostBack, and if not, calls a custom BindGrid function. For a description of BindGrid, see step 4 of Inserting Data Into a SQL Database.
  3. Implement the MyDataGrid_Edit function. This function sets up an index to the row in the DataGrid that is clicked, and then calls BindGrid.
    public void MyDataGrid_Edit(Object sender, DataGridCommandEventArgs E)
        {
            MyDataGrid.EditItemIndex = (int)E.Item.ItemIndex;
            BindGrid();
        }
  4. Implement the MyDataGrid_Cancel function. This function resets the index to the row's previous settings, and then calls BindGrid.
    public void MyDataGrid_Cancel(Object sender, DataGridCommandEventArgs E)
        {
            MyDataGrid.EditItemIndex = -1;
            BindGrid();
        }
  5. Implement the MyDataGrid_Update function. When the "update" link is clicked, this function builds a comma-delimited string of the SQL UPDATE command and fieldname/value pairs (fieldname=value,), connects to the database, updates the row's field information in the database, and then rebinds the DataGrid to show the updated information.
    public void MyDataGrid_Update(Object sender, 
                                      DataGridCommandEventArgs E)
        {
    1. Set up a SQLCommand using parameters and the connection information that was implemented in step 2.
              String updateCmd = "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";
              SQLCommand myCommand = new SQLCommand(updateCmd, myConnection);
              myCommand.Parameters.Add(new SQLParameter("@Id", 
                                                   SQLDataType.VarChar, 11));
              myCommand.Parameters.Add(new SQLParameter("@LName", 
                                                   SQLDataType.VarChar, 40));
              myCommand.Parameters.Add(new SQLParameter("@FName", 
                                                   SQLDataType.VarChar, 20));
              myCommand.Parameters.Add(new SQLParameter("@Phone", 
                                                      SQLDataType.Char, 12));
              myCommand.Parameters.Add(new SQLParameter("@Address", 
                                                   SQLDataType.VarChar, 40));
              myCommand.Parameters.Add(new SQLParameter("@City", 
                                                   SQLDataType.VarChar, 20));
              myCommand.Parameters.Add(new SQLParameter("@State", 
                                                       SQLDataType.Char, 2));
              myCommand.Parameters.Add(new SQLParameter("@Zip", 
                                                       SQLDataType.Char, 5));
              myCommand.Parameters.Add(new SQLParameter("@Contract", 
                                                     SQLDataType.VarChar,1));
              String[] cols = {"@Id","@LName","@FName","@Phone","@Address", 
                              "@City","@State","@Zip","@Contract"};
    2. Initialize the SQLCommand "@ID" parameter to the row ID of the row that must be clicked.
              myCommand.Parameters["@Id"].Value = 
                                  MyDataGrid.DataKeys[(int)E.Item.ItemIndex];
    3. Create an array of column names.
              String[] cols = {"@Id","@LName","@FName","@Phone","@Address", 
                              "@City","@State","@Zip","@Contract"};
    4. Skipping the first, second, and last columns, iterate through the columns, checking for empty values. If an empty value is found, give the user a message box. Also, initialize the SQLCommand parameter values.
              int numCols = E.Item.Cells.Count;
              for (int i=2; i<numCols-1; i++) 
              {
                  String colvalue = 
                                 ((TextBox)E.Item.Cells[i].Controls[0]).Text;
                  if (i<6 && colvalue == "") 
                  {
                      Message.InnerHtml = "ERROR: Null values not allowed for 
                                          Author ID, Name or Phone";
                      Message.Style["color"] = "red";
                      return;
                  }
                  myCommand.Parameters[cols[i-1]].Value = colvalue;
              }
    5. Append the last field, converting true/false values to 0/1.
              if (String.Compare(((TextBox)E.Item.Cells
                             [numCols-1].Controls[0]).Text, "true", true)==0)
                  myCommand.Parameters["@Contract"].Value =  "1"; 
              else
                  myCommand.Parameters["@Contract"].Value =  "0"; 
    6. Connect to the database and update the information.
              myCommand.ActiveConnection.Open();
    7. Test to see if the data was updated and present the appropriate message to the user.
              try 
              {
                  myCommand.Execute(ref rowsAffected);
                  Message.InnerHtml = "<b>Record Updated</b><br>" + 
                                                        updateCmd.ToString();
                  MyDataGrid.EditItemIndex = -1;
              }
              catch (SQLException e)
              {
                  if (e.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";
                  Message.Style["color"] = "red";
              }
    8. Close the connection.
              myCommand.ActiveConnection.Close();
    9. Rebind the DataGrid to show the updated information.
              BindGrid();
          }
  6. Implement a BindGrid function. This function connects to the database and implements the standard "select *" SQL query to get all of the data in "Authors" table of the database. For the details on BindGrid, see step 4 of Inserting Data Into a SQL Database.
  7. In the <body> of the page, display the data.
    <body style="font: 10pt verdana">
      <form runat="server">
        <h3><font face="Verdana">Updating a Row of Data</font></h3>
        <span id="Message" MaintainState="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"
        >
          <property name="Columns">
            <asp:EditCommandColumn EditText="Edit" CancelText="Cancel" 
                               UpdateText="Update" ItemStyle-Wrap="false"/>
          </property>
        </ASP:DataGrid>
      </form>
    </body>

To see this example run, go to the ASP+ Quick Start and run sample DataGrid6.aspx.