How to update record into the database?

Introduction

ADO.NET is a mechanism to communicate with the database. It allows us to work in connected (database connection is alive while the operation is performed) as well as disconnected architecture (database connection is closed and operation can be performed on the data).

Get hundreds of ASP.NET Tips and Tricks and ASP.NET Online training here.

In this scenario, we are going to learn how to update record into the database. To do this, first we will list the records, every record will have a hyperlink with the its Id as querystring that redirects on the same page. In the Page_Load, we shall retrieve the id and fetch that record from database and populate into the TextBoxes and DropDown to update.

Below is my aspx page (aspx.page) to selected record and update record into the database.

ASPX PAGE

<asp:Label ID="lblMessage" runat="server" ForeColor="Green" />
        <h5>Create, Read, Update, Delete operation</h5>
        <div>
            <table>
               <tr><td>First name: </td><td><asp:TextBox ID="txtFirstName"
runat="server" /> </td></tr>
                <tr><td>Last name: </td><td><asp:TextBox ID="txtLastName"
runat="server" /> </td></tr>
                <tr><td>Age: </td><td><asp:TextBox ID="txtAge" runat="server" />
</td></tr>
                <tr><td>Active: </td><td>
                    <asp:DropDownList ID="dropActive" runat="server">
                        <asp:ListItem Text="Yes" Value="True" />
                        <asp:ListItem Text="No" Value="False" />
                    </asp:DropDownList>
               </td></tr>
               <tr><td>&nbsp;</td><td><asp:Button ID="btnSubmit" runat="server"
Text="Submit" OnClick="SubmitData" /></td></tr>
            </table>
        </div>
 
<asp:Label ID="lblData" runat="server" ForeColor="Green" />

In the above code snippet, we have a html table with TextBoxes, DropDownList and a Button. On click of button, we are calling SubmitData method.

CODE BEHIND

string _connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
 
 
protected void Page_Load(object sender, EventArgs e)
    {
       if (!IsPostBack)
       {
           // if coming for edit mode, populate the data into the textboxes
if (!string.IsNullOrWhiteSpace(Request.QueryString["AutoId"]))
            {
                int id = int.Parse(Request.QueryString["AutoId"]);
                if (!id.Equals(0))
                {
                    PopulateTheDataInEditMode(id);
                }
             }
             FetchRecords();
    }
}
 
   /// <summary>
/// Populates the data in edit mode.
/// </summary>
/// <param name="id">The id.</param>
private void PopulateTheDataInEditMode(int id)
{
 
    DataTable table = new DataTable();
 
   // get the connection
   using (SqlConnection conn = new SqlConnection(_connStr))
   {
     // write the sql statement to execute
     string sql = "SELECT AutoId, FirstName, LastName, Age, Active FROM
PersonalDetail WHERE AutoId = @AutoId";
   // instantiate the command object to fire
   using (SqlCommand cmd = new SqlCommand(sql, conn))
   {
 
    cmd.Parameters.AddWithValue("@AutoId", id);
    // get the adapter object and attach the command object to it
    using (SqlDataAdapter ad = new SqlDataAdapter(cmd))
    {
        // fire Fill method to fetch the data and fill into DataTable
        ad.Fill(table);
     }
   }
 }
 DataRow row = table.Rows[0];
 txtFirstName.Text = row["FirstName"].ToString();
 txtLastName.Text = row["LastName"].ToString();
 txtAge.Text = row["Age"].ToString();
 dropActive.SelectedValue = row["Active"].ToString();
 btnSubmit.Text = "Update";
 
 // save the id into the ViewState so that it can be used while updating
 ViewState["AutoId"] = id;
}
 
/// <summary>
/// Fetches the records.
/// </summary>
private void FetchRecords()
{
    DataTable table = new DataTable();
 
// get the connection
using (SqlConnection conn = new SqlConnection(_connStr))
{
   // write the sql statement to execute
   string sql = "SELECT AutoId, FirstName, LastName, Age, Active FROM
PersonalDetail Order By AutoID ASC";
            // instantiate the command object to fire
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
               // get the adapter object and attach the command object to it
               using (SqlDataAdapter ad = new SqlDataAdapter(cmd))
               {
                  // fire Fill method to fetch the data and fill into DataTable
                  ad.Fill(table);
               }
               // DataAdapter doesn't need open connection, it takes care of opening and
closing the database connection
            }
       }
 
      // in case of concatenating multiple string we should use StringBuilder
      StringBuilder strb = new StringBuilder();
 
     // loop through the rows of the table
     foreach (DataRow row in table.Rows)
     {
         strb.Append("<p><a href=\"?AutoId=" + row["AutoId"] + "\">Edit</a> | "+
row["AutoId"] + " > " + row["FirstName"] + " > " + row["LastName"] + " > " + row["Age"] +
" > " + row["Active"] + "</p>");
        }
        lblData.Text = strb.ToString();
     }
 
 
/// <summary>
     /// Submits the data.
     /// </summary>
     /// <param name="sender">The sender.</param>
     /// <param name="e">The <see cref="System.EventArgs"/> instance containing the event
data.</param>
    protected void SubmitData(object sender, EventArgs e)
{
 
      if (ViewState["AutoId"] != null)
      {
         UpdateRecord(int.Parse(ViewState["AutoId"].ToString()));
         return;
      }
     //…. REST ALL CODES ARE DELETED FOR CLARITY PURPOSE
}
 
 
 
/// <summary>
    /// This method code is almost similar to the above method code and the above code
could have been used
   /// to update the reocrds by placing couple of conditions however
   /// I have separated it out from the Insert method to make it easily understandable
   /// Updates the record.
   /// </summary>
   /// <param name="id">The id.</param>
   private void UpdateRecord(int id)
   {
     int returnValue = 0;
 
     // get the connection
     using (SqlConnection conn = new SqlConnection(_connStr))
     {
        // write the sql statement to execute
        string sql = "UPDATE PersonalDetail SET FirstName = @FirstName, LastName =
@LastName, " +
           "Age = @Age, Active = @Active WHERE AutoId = @AutoId";
 
       // instantiate the command object to fire
       using (SqlCommand cmd = new SqlCommand(sql, conn))
       {
          // attach the parameter to pass, if no parameter is in the sql no need to
attach
SqlParameter[] prms = new SqlParameter[5];
prms[0] = new SqlParameter("@FirstName", SqlDbType.VarChar, 50);
prms[0].Value = txtFirstName.Text.Trim();
prms[1] = new SqlParameter("@LastName", SqlDbType.VarChar, 50);
prms[1].Value = txtLastName.Text.Trim();
prms[2] = new SqlParameter("@Age", SqlDbType.Int);
prms[2].Value = int.Parse(txtAge.Text.Trim());
prms[3] = new SqlParameter("@Active", SqlDbType.Bit);
prms[3].Value = bool.Parse(dropActive.SelectedValue);
prms[4] = new SqlParameter("@AutoId", SqlDbType.Int);
prms[4].Value = id;
 
           cmd.Parameters.AddRange(prms);
           conn.Open();
           returnValue = cmd.ExecuteNonQuery();
           conn.Close();
       }
 }
 
 if (!returnValue.Equals(0))
 {
    lblMessage.Text = " Records updated successfully !";
    ViewState["AutoId"] = null;
 
    // refresh the data listing
    this.FetchRecords();
 }
}

In the above code snippet, the aspx page is same as we have in the previous article (Insert records). In the
Page_Load event, we have checked for the AutoId querystring value if it has some value, we have assumed that the request is coming to edit the record so we need to pre-populate the data for that AutoId (using PopulateTheDataInEditMode method) into the textboxes and the dropdownlist (Look at the Output section below and notice that now we have Edit link coming in the list of records, this edit link redirects the user on the same page with AutoId querystring value).

In the PopulateTheDataInEditMode method, we have executed the SQL select statement by filtering the records where AutoId is the querystring value, then populated the TextBoxes and the DropDownList with that record data. Also we have changed the Text of the button to “Update”. We have saved the id (AutoId querystring value) into the ViewState so that we can use it while Updating.

(ViewState is a way to store temporary data on the page that can be retrieved in the post back).

On click of the button, we are checking for the ViewState value, if it is not null the postback must be for the Update so we have called the UpdateRecord method by passing the value of the ViewState.

In the UpdateRecord method, we have used Update sql statement to update the record (this update method code is almost similar to “Insert” apart from the SQL statement and one more AutoId parameter).

OUTPUT

Hope you were able to understand the approach used in this article to update record into SQL Server database. If you have any question, do let me know by responding to this article.

Thanks for reading !