Updating Data in a SQL Database

Updating Data in a SQL Database

.NET Framework 1.1

The following code example demonstrates how to use a Web application to update data fields in an existing database.

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<script language="VB" runat="server">
   Dim myConnection As SqlConnection
   '  Create a connection to the "pubs" SQL database located on the 
   ' local computer. 
   Sub Page_Load(Src As Object, E As EventArgs) 
      myConnection = New SqlConnection("server=localhost;" _ 
         & "database=pubs;Trusted_Connection=Yes")
      ' Determine whether this page is a postback. If it is not a
      ' postback, call BindGrid.
      If Not IsPostBack Then 
         BindGrid()
      End If
   End Sub

   ' Create an index to the DataGrid row that is clicked and 
   ' call BindGrid.
   Sub MyDataGrid_Edit(sender As Object, E As DataGridCommandEventArgs)
      MyDataGrid.EditItemIndex = CInt(E.Item.ItemIndex)
      BindGrid()
   End Sub

   ' Cancel resets the index to the row's previous settings.
   Sub MyDataGrid_Cancel(sender As Object, E As DataGridCommandEventArgs)
      MyDataGrid.EditItemIndex = -1
      BindGrid()
   End Sub

   ' When the  Update link is clicked, build a SQL UPDATE command,    
   ' connect to the database, update the row's information in the 
   ' database, and rebind the DataGrid to show the updated information.
   Public Sub MyDataGrid_Update(sender As Object, _
      E As DataGridCommandEventArgs)
      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;"
      Dim  myCommand As SqlCommand = New SqlCommand(updateCmd, _
         myConnection)
      myCommand.Parameters.Add(New SqlParameter("@Id", SqlDbType.VarChar))
      myCommand.Parameters.Add(New SqlParameter("@LName", _
         SqlDbType.VarChar))
      myCommand.Parameters.Add(New SqlParameter("@FName", _
         SqlDbType.VarChar))
      myCommand.Parameters.Add(New SqlParameter("@Phone", _
         SqlDbType.VarChar))
      myCommand.Parameters.Add(New SqlParameter("@Address", _
         SqlDbType.VarChar))
      myCommand.Parameters.Add(New SqlParameter("@City", _
         SqlDbType.VarChar))
      myCommand.Parameters.Add(New SqlParameter("@State", _
         SqlDbType.VarChar))
      myCommand.Parameters.Add(New SqlParameter("@Zip", _
         SqlDbType.VarChar))
      myCommand.Parameters.Add(New SqlParameter("@Contract", _
         SqlDbType.VarChar))
      
      ' Initialize the SqlCommand "@ID" parameter to the ID of the row 
      ' that must be clicked.
      myCommand.Parameters("@Id").Value = _
         MyDataGrid.DataKeys(CInt(E.Item.ItemIndex))
      ' Create an array of column names.
      Dim cols() As String = {"@Id","@LName","@FName","@Phone", _
         "@Address", "@City","@State","@Zip","@Contract"}
      ' Skipping the first, second, and last columns, iterate through the 
      ' columns, checking for empty values. If an empty value is found, 
      '  display a message box. Also initialize the SqlCommand 
      ' parameter values.
      Dim numCols As Integer = E.Item.Cells.Count
      Dim i As Integer
      Dim colvalue As String
      Dim txtBox As Textbox
      For i = 2 To numCols-1
         txtBox = E.Item.Cells(i).Controls(0)
         colvalue = txtBox.Text
         If (i<6 And colvalue = "") 
            Message.InnerHtml = "ERROR: Null values not allowed for " _
               & "Author ID, Name or Phone"
            Message.Style("color") = "red"
            Exit Sub
         End If
         myCommand.Parameters(cols(i-1)).Value = colvalue
      Next i
      ' Append the last field, converting true/false values to 0/1.
      txtBox=E.Item.Cells(numCols-1).Controls(0)
      If (String.Compare(txtBox.Text, "true", true) = 0) Then
         myCommand.Parameters("@Contract").Value =  "1"
      Else
         myCommand.Parameters("@Contract").Value =  "0"
         ' Connect to the database and update the information.
         myCommand.Connection.Open()
         ' Test  whether the data was updated, and display the 
         ' appropriate message to the user.
         Try 
            myCommand.ExecuteNonQuery()
            Message.InnerHtml = "<b>Record Updated.</b><br>"
            MyDataGrid.EditItemIndex = -1
         Catch ex As SqlException
            If ex.Number = 2627 Then
               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"
            End If
         End Try

         ' Close the connection.
         myCommand.Connection.Close()
         ' Rebind the DataGrid to show the updated information.
         BindGrid()
      End If
   End Sub

   ' The BindGrid procedure connects to the database and implements
   ' a SQL SELECT query to get all the data in the "Authors" table.
   Public Sub BindGrid() 
      Dim myConnection As SqlConnection = _
         New SqlConnection("server=localhost;" _
         & "database=pubs;Trusted_Connection=Yes")
      Dim myCommand As SqlDataAdapter = New SqlDataAdapter("SELECT *" _
         & " FROM authors", myConnection)
      Dim ds As DataSet= New DataSet()
      myCommand.Fill(ds)
      MyDataGrid.DataSource = ds
      MyDataGrid.DataBind()
   End Sub
</script>

<%--  Display the data in the body of the page. --%>
<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"
      >
      <Columns>
         <ASP:EditCommandColumn EditText="Edit" CancelText="Cancel" 
            UpdateText="Update"/>
      </Columns>
   </ASP:DataGrid>
</form>
</body>
</html>

[C#]
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Text"%>
<html>
<script language="C#" runat="server">
   SqlConnection myConnection;

   protected void Page_Load(Object Src, EventArgs E) 
   {
      //  Create a connection to the "pubs" SQL database located on the
      // local computer.
      myConnection = new SqlConnection ("server=localhost;" +
         "database=pubs;Trusted_Connection=Yes");
      // Determine whether the page is a postback. If it is not a 
      // postback, call BindGrid.
      if (!IsPostBack) BindGrid();
   }
   // Create an index to the DataGrid row that is clicked and 
   // call BindGrid.
   public void MyDataGrid_Edit(Object sender, DataGridCommandEventArgs E)
   {
      MyDataGrid.EditItemIndex = (int)E.Item.ItemIndex;
      BindGrid();
   }

   // Cancel resets the index to the row's previous settings.
   public void MyDataGrid_Cancel(Object sender, 
      DataGridCommandEventArgs E) 
   {
      MyDataGrid.EditItemIndex = -1;
      BindGrid();
   }
   // When the  Update link is clicked, build a SQL UPDATE command,    
   // connect to the database, update the row's information in the 
   // database, and rebind the DataGrid to show the updated information.
   public void MyDataGrid_Update(Object sender, 
      DataGridCommandEventArgs E) 
   {
      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",
         SqlDbType.VarChar));
      myCommand.Parameters.Add(new SqlParameter("@LName", 
         SqlDbType.VarChar));
      myCommand.Parameters.Add(new SqlParameter("@FName", 
         SqlDbType.VarChar));
      myCommand.Parameters.Add(new SqlParameter("@Phone", 
         SqlDbType.VarChar));
      myCommand.Parameters.Add(new SqlParameter("@Address", 
         SqlDbType.VarChar));
      myCommand.Parameters.Add(new SqlParameter("@City", 
         SqlDbType.VarChar));
      myCommand.Parameters.Add(new SqlParameter("@State", 
         SqlDbType.VarChar));
      myCommand.Parameters.Add(new SqlParameter("@Zip", 
         SqlDbType.VarChar));
      myCommand.Parameters.Add(new SqlParameter("@Contract", 
         SqlDbType.VarChar));
      // Initialize the SqlCommand "@Id" parameter to the ID of the
      // row that must be clicked.
      myCommand.Parameters["@Id"].Value = 
         MyDataGrid.DataKeys[(int)E.Item.ItemIndex];
      // Create an array of column names.
      String[] cols = {"@Id","@LName","@FName","@Phone",
         "@Address", "@City","@State","@Zip","@Contract"};
      // Iterate through the columns, checking for empty values. 
      // Skip the first, second, and last columns. If an empty value 
      // is found,  display an error 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;
      }
      // 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"; 
         // Connect to the database and update the information.
         myCommand.Connection.Open();
         // Test  whether the data was updated and  display the 
         //appropriate message to the user.
         try 
         {
            myCommand.ExecuteNonQuery();
            Message.InnerHtml = "<b>Record Updated.</b><br>";
            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";
         }
      // Close the connection.
      myCommand.Connection.Close();
      // Show the updated information.
      BindGrid();
   }

   // The BindGrid procedure connects to the database and implements
   // a SQL SELECT query to get all data in the "Authors" table.
   public void BindGrid() 
   {
      SqlConnection myConnection = new SqlConnection(
         "server=localhost;database=pubs;Trusted_Connection=Yes");
      SqlDataAdapter myCommand = new SqlDataAdapter(
         "SELECT * FROM authors", myConnection);
      DataSet ds = new DataSet();
      myCommand.Fill(ds);
      MyDataGrid.DataSource=ds;
      MyDataGrid.DataBind();
   }
</script>

<%--  Display the data in the body of the page. --%>
<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"
      >
      <Columns>
         <ASP:EditCommandColumn EditText="Edit" CancelText="Cancel" 
            UpdateText="Update"/>
      </Columns>
   </ASP:DataGrid>
</form>
</body>
</html>

See Also

Accessing Data with ASP.NET | Accessing Data with ADO.NET | System.Web.UI.WebControls Namespace | DataGrid Class

Show:
© 2016 Microsoft