Introduction
Here's part-2 of the series and it shows CRUD operations using Ajax or without loading the page and binding the data in HTML Table using Ajax with edit and delete operations.
Description
Before moving to the article I will suggest you to visit my first article to save data in the database and bind in HTML table using Ajax. Here's the link:
After the first article we completed till the following:
![Eployee detail]()
![Admin Employee table]()
Step 1: (For Edit Option).
The Edit button has the following code:
<input type="button" class="btn btn-primary editButton" data-id="<%=TableData.Rows[data]["EmpId"] %>" data-toggle="modal" data-target="#myModal" name="submitButton" id="btnEdit" value="Edit" /></td>
data-id is bind with EmpId so that on clicking that particular row button, respective data from the database of EmpID will be the Popup.
Here I have used PopUp edit of BootStrap for displaying it in a better way.
HTML Code for PopUp Edit is as in the following code snippet:
<div class="modal fade bs-example-modal-lg" tabindex="-1" role="dialog" aria-labelledby="myLargeModalLabel" id="myModal">
<div class="modal-dialog" role="document">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button>
<h2 class="modal-title" id="myModalLabel">Modal title</h2>
</div>
<div class="modal-body">
<div class="panel-body">
<div class="form-group col-lg-4">
<label>First Name</label>
<input type="text" name="FirstName" id="FirstName1" class="form-control" placeholder="Ajay" required="" />
</div>
<div class="form-group col-lg-4">
<label>Middle Name</label>
<input type="text" name="MiddleName" id="MiddleName1" class="form-control" placeholder="Kumar" required="" />
</div>
<div class="form-group col-lg-4 ">
<label>Surname</label>
<input type="text" name="Surname" id="Surname1" class="form-control" placeholder="Gupta" required="" />
</div>
<div class="form-group col-lg-4">
<label>Gender</label>
<label class="radio-inline">
<input type="radio" checked="" value="Male" id="Male1" name="Gender" />
Male
</label>
<label class="radio-inline">
<input type="radio" value="Female" id="Female1" name="Gender" />
Female.
</label>
</div>
<div class="clearfix"></div>
<div class="form-group col-lg-6">
<label>Email ID</label>
<input type="email" readonly="readonly" name="EmailId" id="EmailId1" class="form-control" placeholder="[email protected]" required="" />
</div>
<div class="form-group col-lg-6">
<label>Date of Birth </label>
<input type="date" name="Dob" id="Dob1" min="1920-01-02" class="form-control datepicker" required="" />
</div>
<div class="form-group col-lg-6">
<label>MaritalStatus</label>
<select name="MaritalStatus" id="MaritalStatus1" class="form-control" required="">
<option value="" disabled="disabled">-- Select -- </option>
<option value="Single">Single</option>
<option value="Married">Married</option>
</select>
</div>
<div class="form-group col-lg-6">
<label>Hobbies</label>
<input type="text" name="Hobbies" id="Hobbies1" class="form-control" placeholder="Football, Cricket etc." />
</div>
<div class="form-group col-lg-6">
<label>Home Telephone</label>
<input type="text" name="TelephoneNo" id="TelephoneNo1" class="form-control" placeholder="1234567890" />
</div>
<div class="form-group col-lg-6">
<label>Mobile</label>
<input type="tel" name="MobileNo" id="MobileNo1" class="form-control" required="" placeholder="0987654321" />
</div>
<div class="form-group col-lg-12">
<label>Residential Address</label>
<textarea rows="2" name="ResidentialAddress" id="ResidentialAddress1" class="form-control" required=""></textarea>
</div>
<div class="form-group col-lg-6 ">
<label>Pin Code</label>
<input type="text" name="PinCode" id="PinCode1" class="form-control" placeholder="999999" />
</div>
<div class="form-group col-lg-6">
<label>State</label>
<select name="State" id="State1" class="form-control" required="">
<option value="" disabled="disabled">-- Select -- </option>
<option value="Maharashtra">Maharastra</option>
<option value="Bihar">Bihar</option>
<option value="Delhi">UP</option>
<option value="Odisha">Odisha</option>
<option value="Odisha">AP</option>
</select>
</div>
<div class="form-group col-lg-6">
<label>Nationality</label>
<input type="text" name="title" id="Nationality1" class="form-control" placeholder="Indian" required="" />
</div>
<div class="form-group col-lg-6">
<label>Date of Joining</label>
<input type="date" name="Doj" id="Doj1" class="form-control datepicker" required="" />
</div>
</div>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
<button type="button" id="btnUpdate" class="btn btn-primary" edit-id="" data-dismiss="modal">Save changes</button>
</div>
</div>
</div>
</div>
</div>
My scripting code for edit is as follows:
$(document).on("click", ".editButton", function() {
$('#myModal').focus();
var id = $(this).attr("data-id");
console.log(id);
$("#btnUpdate").attr("edit-id", id); // Here I am Passing the ID of that edit id to update that specific data
//alert(id); //getting the row id
$.ajax({
type: "Post",
contentType: "application/json; charset=utf-8",
url: "Default.aspx/EditData", // Default.aspx is the Page and EditData() is my WebMethod
data: '{eid: ' + id + '}', // Showing the Details of Record of Specific ID
dataType: "json",
success: function(data) {
var empDetails = $.parseJSON(data.d);
$.each(empDetails, function(index, value) {
//console.log(v.Fname);
$("#FirstName1").val(value.Fname);
$("#MiddleName1").val(value.Mname);
$("#Surname1").val(value.Lname);
$("#EmailId1").val(value.EMail);
$("#Dob1").val(value.DOB);
$("#MaritalStatus1").val(value.MaritalStatus);
$("#Hobbies1").val(value.Hobbies);
$("#TelephoneNo1").val(value.Telephone);
$("#MobileNo1").val(value.Mobile);
$("#ResidentialAddress1").val(value.Address);
$("#PinCode1").val(value.PinCode);
$("#State1").val(value.State);
$("#Nationality1").val(value.Nationality);
$("#Doj1").val(value.DOJ);
});
},
error: function() {
alert("Error while retrieving data of :" + id);
}
});
});
WebMethod for Edit is the following:
[WebMethod]
public static string EditData(int eid) //Show the edit clicked data in the popup window
{
string jsondata;
//var details = new List<Employee>();
using (var con = new SqlConnection(Constr))
{
var query = "select * from TblUser where EmpId='" + eid + "' order by EmpId desc";
using (var cmd = new SqlCommand(query, con))
{
using (var sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
TableData.Clear();
sda.Fill(TableData);
jsondata = JsonConvert.SerializeObject(TableData);
}
}
}
return jsondata;
}
After that it will look like the following:
![]()
Step 2: (For Update Records).
After updating records we can go for saving the changes. The code for updating the script is shown below:
$("#btnUpdate").click(function() {
var id = $(this).attr("edit-id");
var user = {};
user.FName = $("#FirstName1").val();
user.LName = $("#Surname1").val();
user.MName = $("#MiddleName1").val();
user.Gender = $("#Male1").val();
user.Email = $("#EmailId1").val();
user.Dob = $("#Dob1").val();
user.MaritalStatus = $("#MaritalStatus1").val();
user.Hobbies = $("#Hobbies1").val();
user.HomeMobile = $("#TelephoneNo1").val();
user.OfficeMobile = $("#MobileNo1").val();
user.Address = $("#ResidentialAddress1").val();
user.Pincode = $("#PinCode1").val();
user.State = $("#State1").val();
user.Nationality = $("#Nationality1").val();
user.Doj = $("#Doj1").val();
user.ModifiedDateTime = new Date();
$.ajax({
type: "Post",
contentType: "application/json; charset=utf-8",
url: "Default.aspx/UpdateData",
data: '{objEmployee: ' + JSON.stringify(user) + ', eid : ' + id + '}', // Id came from edit button
dataType: "json",
success: function(data) {
if (confirm("Are you sure you want to change !") == true) {
alert("Data Updated successfully");
} else {
alert("You have canceled the changes");
}
getDetails(); // To Show the updated data after updation
},
error: function(data) {
alert("Error while Updating data of :" + id);
}
});
});
For Delete We have to change the Ajax getDetails() Method as follows
function getDetails() {
$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
url: "Default.aspx/GetData",
data: {},
dataType: "json",
success: function(data) {
$('#dataTables-example tbody').remove();
//console.log(data.d);
for (var i = 0; i < data.d.length; i++) {
$("#dataTables-example").append(
"<tr><td>" + data.d[i].FName + "</td><td>" + data.d[i].Email + "</td>" +
"<td>" + data.d[i].HomeMobile + "</td>" + "<td>" + data.d[i].OfficeMobile + "</td>" +
"<td>" + data.d[i].Doj + "</td>" + "<td>" + data.d[i].Dob + "</td>" +
"<td>" + "<input type='button' class='btn btn-primary editButton' data-id='" + data.d[i].EmpId + "' data-toggle='modal' data-target='#myModal' name='submitButton' id='btnEdit' value='Edit' />" + "</td>" +
"<td><input type='button' class='btn btn-primary deleteButton' data-id='" + data.d[i].EmpId + "' name='submitButton' id='btnDelete' value='Delete'/> </td></tr>");
}
},
error: function() {
alert("Error while Showing update data");
}
});
}
See my Ajax data. Here I am passing all the details of Employee object in JSON format along with the Id to update specific record.
On success, I am calling getDetails() to show updated data in the HTML table.
My WebMethod for updating the records is the following:
[WebMethod]
public static void UpdateData(Employee objEmployee, int eid) //Update data in database
{
using (var con = new SqlConnection(Constr))
{
var query = "update TblUser set Hobbies='" + objEmployee.Hobbies + "',Fname='" + objEmployee.FName +
"',Lname='" + objEmployee.LName + "',Mname='" + objEmployee.MName + "'," +
"Dob='" + objEmployee.Dob + "',MaritalStatus='" + objEmployee.MaritalStatus + "',Address='" +
objEmployee.Address + "', Telephone='" + objEmployee.OfficeMobile + "'," +
"Mobile='" + objEmployee.HomeMobile + "',Pincode='" + objEmployee.Pincode + "',State='" +
objEmployee.State + "',Nationality='" + objEmployee.Nationality + "', " +
"Doj='" + objEmployee.Doj + "'where EmpId='" + eid + "'";
con.Open();
var cmd = new SqlCommand(query, con);
cmd.ExecuteNonQuery();
con.Close();
}
}
Step 3: (For Delete Records).
Here's the delete button html:
<input type="button" class="btn btn-primary deleteButton" data-id="<%=TableData.Rows[data]["EmpId"] %>" name="submitButton" id="btnDelete" value="Delete" />
data-id -From this I am taking EmployeeID to delete specific records. Also I have added a class deleteButton to identify all the delete clicks with that class:
$(document).on("click", ".deleteButton", function() {
var id = $(this).attr("data-id");
$.ajax({
type: "Post",
contentType: "application/json; charset=utf-8",
url: "Default.aspx/Remove", // Remove is the Webmethod in Default.aspx Page
data: '{eid: ' + id + '}',
dataType: "json",
success: function() {
if (confirm("Are you sure you want to delete !") == true) {
alert("Data Deleted successfully");
} else {
alert("You have canceled the changes");
}
//alert("Data Updated successfully");
getDetails();
},
error: function(data) {
alert("Error while Updating data of :" + id);
}
});
}
My WebMethod for Delete record.
[WebMethod]
public static void Remove(int eid)
{
using (var con = new SqlConnection(Constr))
{
var query = "delete from dbo.TblUser where EmpId='" + eid + "'";
con.Open();
var cmd = new SqlCommand(query, con);
cmd.ExecuteNonQuery();
con.Close();
}
}
After this we can delete particular record and also see that after Ajax I am calling getDetails(); to show latest data.
Also for inserting multiple rows in the database from HTML table using Ajax and jQuery please visit here.
You can download the entire application attached above that includes both Part-I and Part-2.
Hope that helps and thanks for reading.