Update/Delete in GridView using JQuery without page post back
In this article, I am going to use JQuery with json data and web method to update/delete in grid view without sending page to the server and without putting grid view in edit mode. I am going to show the update operation, but one can easily use it for delete operation with little changes.
The grid view is a very powerful control to present tabular view of data. It is equipped with all the necessary features of server side data processing. But sometimes we need to edit small data (in the form of record) to the database very frequently and we do not want the page to have a round trip to the server. Though we can achieve this (partial post back) using "Ajax", but then the end user need to make some "extra" clicks for putting grid view in "edit mode" and as a developer we need to write some additional block of codes and handle some grid view events along with a little performance hit.
In the below lines, I am going to show how we can update/delete the grid view's row using JQuery with the use of json data. This way the page needs not to be sent on server at all.
I this example, I used the "Products" table of "Northwind" database. If you want to run the below code snippet, you need "Northwind" database installed in your SQL SERVER database.
Now let us directly jump to the codes. I explained all the necessary details in the code itself.
The code below is built on .NET framework 4.0 and successfully tested on Internet Explorer 9 and Firefox.
Below is my grid view:
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="ProductID" HeaderText="ProductID" />
<asp:BoundField DataField="ProductName" HeaderText="Product Name" />
<asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice" />
<asp:TemplateField HeaderText="Units In Stock">
<ItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Eval("UnitsInStock") %>'
CssClass="stock">
</asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<a href="#" id='<%# Eval("ProductID") %>' class="updatebutton">
<img border="0" src="Images/update.png" alt="Delete" />
</a>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
You can have any numbers of "BoundField" and "TemplateField". The important thing is the "CssClass" of the textbox, which we are going to use in our JQuery function. Also I am updating one column, for that textbox is used so that user can edit the value inside textbox. You can update any number of columns by putting the column in "TemplateField" and using textbox.
Below is the JQuery function:
<script src="Scripts/jquery-1.4.1.js" type="text/javascript" > </script >
<script src="Scripts/jquery-1.4.1.min.js" type="text/javascript" > </script>
<script language="javascript" type="text/javascript">
$(document).ready(function () {
$(".updatebutton").click(function () {
var row = $(this).closest("tr");
//Getting the ID of the record and the data from textbox to update
var pID = $(this).attr("id");
var update_data = $(".stock", row).val(); //stock is the CssClass name of the textbox.
// If you are updating more than one column, retrieve the value of all the
//textboxes similarly and then add them in single variable
//(see below)
update_data = pID + "," + update_data; //adding both the value in single variable
//with ',' separator
// Confirming the operation from the user
if (confirm("Do you want to update this record?")) {
$.ajax({
type: "POST",
//UpdateRecordInGridViewUsingJQuery.aspx is the page name and UpdateProduct
// is the server side web method which actually does the updation
url: "UpdateRecordInGridViewUsingJQuery.aspx/UpdateProduct",
//Passing the record id and data to be updated which is in the variable record_id
data: "{'args': '" + update_data + "'}",
contentType: "application/json; charset=utf-8",
dataType: "json",
//Giving message to user on successful updation
success: function () {
alert("Record successfully updated!!!");
//location.reload();
}
});
}
return false;
});
});
</script>
In the above function, I am calling a web method named "UpdateProduct", which actually updates the record to the database.
Let us look at this web method:
[System.Web.Services.WebMethod]
public static void UpdateProduct(string args)
{
string[] data = args.Trim().Split(',');
try
{
SqlConnection conn;
SqlCommand cmd;
conn = new SqlConnection();
conn.ConnectionString = ConfigurationManager.ConnectionStrings["NWConnectionString"].ConnectionString;
cmd = new SqlCommand();
cmd.CommandText = "UPDATE Products SET UnitsInStock = @unitsinstock WHERE ProductID = @productid";
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
cmd.Parameters.AddWithValue("@unitsinstock", data[1]);
cmd.Parameters.AddWithValue("@productid", Convert.ToInt32(data[0]));
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
throw ex;
}
}
And at last below is my data bind method which bind the grid view:
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
LoadData();
}
}
protected void LoadData()
{
SqlConnection conn;
SqlCommand cmd;
SqlDataAdapter sda;
DataTable dt;
conn = new SqlConnection();
conn.ConnectionString = ConfigurationManager.ConnectionStrings["NWConnectionString"].ConnectionString;
cmd = new SqlCommand();
cmd.CommandText = "SELECT top 5 ProductID, ProductName, UnitPrice, UnitsInStock FROM Products";
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
dt = new DataTable();
sda = new SqlDataAdapter();
sda.SelectCommand = cmd;
sda.Fill(dt);
GridView1.DataSource = dt.DefaultView;
GridView1.DataBind();
sda.Dispose();
cmd.Dispose();
conn.Dispose();
}
Hope this article is helpful.
My other articles:
All the best…!
hi,
I'm new to jquery/json. I got a task to insert update and delete the grid(gridview or jqgrid)and update in the sql using jquery /json.
Note: while inserting the row i want to display the row below the grid and insert the value to sql and want to update delete in sql using this grid....
can any one help ...
Thanks in advance,