How to update record into the database?

Sheonarayan
Posted by in ADO.NET category on for Intermediate level | Points: 250 | Views : 6718 red flag

In this article, we shall learn how to update record into the SQL Server 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 ! 

Page copy protected against web site content infringement by Copyscape

About the Author

Sheonarayan
Full Name: Sheo Narayan
Member Level: HonoraryPlatinum
Member Status: Administrator
Member Since: 7/8/2008 6:32:14 PM
Country: India
Regards, Sheo Narayan http://www.dotnetfunda.com
http://www.snarayan.com
Ex-Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001. Connect me on http://www.facebook.com/sheo.narayan | https://twitter.com/sheonarayan | http://www.linkedin.com/in/sheonarayan

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)