I’m Practising CRUD in AJAX and JQuery I Sorted out How to add and get data from Database using ADO but Couldn’t able to update the datas in the crud ned some help!!
This my HomeController.cs
this where every function for the program will be written here
using CRUDAjax.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace CRUDAjax.Controllers
{
public class HomeController : Controller
{
EmployeeDB empDB = new EmployeeDB();
// GET: Home
public ActionResult Index()
{
return View();
}
public JsonResult List()
{
return Json(empDB.ListAll(), JsonRequestBehavior.AllowGet);
}
public JsonResult Add(Employee emp)
{
return Json(empDB.Add(emp), JsonRequestBehavior.AllowGet);
}
public JsonResult GetbyID(int ID)
{
var Employee = empDB.ListAll().Find(x => x.EmployeeID.Equals(ID));
return Json(Employee, JsonRequestBehavior.AllowGet);
}
public JsonResult Update(Employee emp)
{
return Json(empDB.Update(emp), JsonRequestBehavior.AllowGet);
}
public JsonResult Delete(int ID)
{
return Json(empDB.Delete(ID), JsonRequestBehavior.AllowGet);
}
}
}
Employee.cs Model
this is the model for the employee details
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace CRUDAjax.Models {
public class Employee { public int EmployeeID { get; set; }
public string Name { get; set; }
public int Age { get; set; }
public string State { get; set; }
public string Country { get; set; }
}
}
}
EmployeeDB.cs Model
Here the Database Connection Will be Given and here i used ADO method(Connection String)
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
> namespace CRUDAjax.Models {
public class EmployeeDB {
string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
>
> //Return list of all Employees
> public List<Employee> ListAll()
> {
> List<Employee> lst = new List<Employee>();
> using (SqlConnection con = new SqlConnection(cs))
> {
> con.Open();
> SqlCommand com = new SqlCommand("SelectEmployee", con);
> com.CommandType = CommandType.StoredProcedure;
> SqlDataReader rdr = com.ExecuteReader();
> while (rdr.Read())
> {
> lst.Add(new Employee
> {
> EmployeeID = Convert.ToInt32(rdr["EmployeeId"]),
> Name = rdr["Name"].ToString(),
> Age = Convert.ToInt32(rdr["Age"]),
> State = rdr["State"].ToString(),
> Country = rdr["Country"].ToString(),
> });
> }
> return lst;
> }
> }
>
> //Method for Adding an Employee
> public int Add(Employee emp)
> {
> int i;
> using (SqlConnection con = new SqlConnection(cs))
> {
> con.Open();
> SqlCommand com = new SqlCommand("InsertUpdateEmployee", con);
> com.CommandType = CommandType.StoredProcedure;
> com.Parameters.AddWithValue("@Id", emp.EmployeeID);
> com.Parameters.AddWithValue("@Name", emp.Name);
> com.Parameters.AddWithValue("@Age", emp.Age);
> com.Parameters.AddWithValue("@State", emp.State);
> com.Parameters.AddWithValue("@Country", emp.Country);
> com.Parameters.AddWithValue("@Action", "Insert");
> i = com.ExecuteNonQuery();
> }
> return i;
> }
>
> //Method for Updating Employee record
> public int Update(Employee emp)
> {
> int i;
> using (SqlConnection con = new SqlConnection(cs))
> {
> con.Open();
> SqlCommand com = new SqlCommand("InsertUpdateEmployee", con);
> com.CommandType = CommandType.StoredProcedure;
> com.Parameters.AddWithValue("@Id", emp.EmployeeID);
> com.Parameters.AddWithValue("@Name", emp.Name);
> com.Parameters.AddWithValue("@Age", emp.Age);
> com.Parameters.AddWithValue("@State", emp.State);
> com.Parameters.AddWithValue("@Country", emp.Country);
> com.Parameters.AddWithValue("@Action", "Update");
> i = com.ExecuteNonQuery();
> }
> return i;
> }
>
> //Method for Deleting an Employee
> public int Delete(int ID)
> {
> int i;
> using (SqlConnection con = new SqlConnection(cs))
> {
> con.Open();
> SqlCommand com = new SqlCommand("DeleteEmployee", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.AddWithValue("@Id", ID);
i = com.ExecuteNonQuery();
}
return i;
> }
> }
>
> }
employee.js (ajax and jquery functions)
This the javascript where the Ajax Functions of the CRUD where will be written
`//Load Data in Table when documents is ready
> $(document).ready(function () {
> loadData();
> });
>
> //Load Data function
> function loadData() {
> $.ajax({
> url: "/Home/List",
> type: "GET",
> contentType: "application/json;charset=utf-8",
> dataType: "json",
> success: function (result) {
> var html = '';
> $.each(result, function (key, item) {
> html += '<tr>';
> html += '<td>' + item.EmployeeID + '</td>';
> html += '<td>' + item.Name + '</td>';
> html += '<td>' + item.Age + '</td>';
> html += '<td>' + item.State + '</td>';
> html += '<td>' + item.Country + '</td>';
> html += '<td><a href="#">Edit</a> | <a href="#">Delete</a></td>';
> html += '</tr>';
> });
> $('.tbody').html(html);
> },
> error: function (errormessage) {
> alert(errormessage.responseText);
> }
> });
> }
>
> //Add Data Function
> function Add() {
>
> var empObj = {
> EmployeeID: $('#EmployeeID').val(),
> Name: $('#Name').val(),
> Age: $('#Age').val(),
> State: $('#State').val(),
> Country: $('#Country').val()
> };
> $.ajax({
> url: "/Home/Add",
> data: JSON.stringify(empObj),
> type: "POST",
> contentType: "application/json;charset=utf-8",
> dataType: "json",
> success: function (result) {
> loadData();
> $('#myModal').modal('hide');
> },
> error: function (errormessage) {
> alert(errormessage.responseText);
> }
> });
>
> }
>
> //Function for getting the Data Based upon Employee IDfunction getbyID(EmpID) {$('#Name').css('border-color', 'lightgrey');$('#Age').css('border-color', 'lightgrey');$('#State').css('border-color', 'lightgrey');$('#Country').css('border-color', 'lightgrey');$.ajax({url: "/Home/getbyID/" + EmpID,type: "GET",contentType: "application/json;charset=UTF-8",dataType: "json",success: function (result) {$('#EmployeeID').val(result.EmployeeID);$('#Name').val(result.Name);$('#Age').val(result.Age);$('#State').val(result.State);$('#Country').val(result.Country);
>
> $('#myModal').modal('show');
> $('#btnUpdate').show();
> $('#btnAdd').hide();
> },
> error: function (errormessage) {
> alert(errormessage.responseText);
> }
> });
> return false;
>
> }
>
> //function for updating employee's record
> function Update() {
>
> var empObj = {
> EmployeeID: $('#EmployeeID').val(),
> Name: $('#Name').val(),
> Age: $('#Age').val(),
> State: $('#State').val(),
> Country: $('#Country').val(),
> };
> $.ajax({
> url: "/Home/Update",
> data: JSON.stringify(empObj),
> type: "POST",
> contentType: "application/json;charset=utf-8",
> dataType: "json",
> success: function (result) {
> loadData();
> $('#myModal').modal('hide');
> $('#EmployeeID').val("");
> $('#Name').val("");
> $('#Age').val("");
> $('#State').val("");
> $('#Country').val("");
>
> },
> error: function (errormessage) {
> alert(errormessage.responseText);
> }
> });
>
> }
> //function for deleting employee's recordfunction Delete(ID) {var ans = confirm("Are you sure you want to delete this Record?");if (ans) {$.ajax({url: "/Home/Delete/" + ID,type: "POST",contentType: "application/json;charset=UTF-8",dataType: "json",success: function (result) {loadData();},error: function (errormessage) {alert(errormessage.responseText);}});}}
>
> //Function for clearing the textboxesfunction clearTextBox() {$('#EmployeeID').val("");$('#Name').val("");$('#Age').val("");$('#State').val("");$('#Country').val("");$('#btnUpdate').hide();$('#btnAdd').show();$('#Name').css('border-color', 'lightgrey');$('#Age').css('border-color', 'lightgrey');$('#State').css('border-color', 'lightgrey');$('#Country').css('border-color', 'lightgrey');}//Valdidation using jqueryfunction validate() {var isValid = true;if ($('#Name').val().trim() == "") {$('#Name').css('border-color', 'Red');isValid = false;}else {$('#Name').css('border-color', 'lightgrey');}if ($('#Age').val().trim() == "") {$('#Age').css('border-color', 'Red');isValid = false;}else {$('#Age').css('border-color', 'lightgrey');}if ($('#State').val().trim() == "") {$('#State').css('border-color', 'Red');isValid = false;}else {$('#State').css('border-color', 'lightgrey');}if ($('#Country').val().trim() == "") {$('#Country').css('border-color', 'Red');isValid = false;}else {$('#Country').css('border-color', 'lightgrey');}return isValid;}`
index.cshtml
@{
ViewBag.Title = "Index";
}
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.1/css/bootstrap.min.css">
@*<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>*@
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.16.0/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
<script src="~/Scripts/employee.js"></script>
<div class="container">
<h2>Employees Record</h2>
<button type="button" class="btn btn-primary" data-toggle="modal" data-target="#myModal" onclick="clearTextBox();">Add New Employee</button><br /><br />
<table class="table table-bordered table-hover">
<thead>
<tr>
<th>
ID
</th>
<th>
Name
</th>
<th>
Age
</th>
<th>
State
</th>
<th>
Country
</th>
<th>
Action
</th>
</tr>
</thead>
<tbody class="tbody"></tbody>
</table>
</div>
<div class="modal fade" id="myModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal">X</button>
<h4 class="modal-title" id="myModalLabel">Add Employee</h4>
</div>
<div class="modal-body">
<form>
<div class="form-group">
<label for="EmployeeId">ID</label>
<input type="text" class="form-control" id="EmployeeID" placeholder="Id" disabled="disabled" />
</div>
<div class="form-group">
<label for="EmployeeName">EmployeeName</label>
<input type="text" class="form-control" id="Name" placeholder="EmployeeName" />
</div>
<div class="form-group">
<label for="Age">Age</label>
<input type="text" class="form-control" id="Age" placeholder="Age" />
</div>
<div class="form-group">
<label for="EmployeeState">EmployeeState</label>
<input type="text" class="form-control" id="State" placeholder="EmployeeState" />
</div>
<div class="form-group">
<label for="Country">Country</label>
<input type="text" class="form-control" id="Country" placeholder="Country" />
</div>
</form>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-primary" id="btnAdd" onclick="return Add();">Add</button>
<button type="button" class="btn btn-primary" id="btnUpdate" style="display:none;" onclick="Update();">Update</button>
<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
</div>
</div>
</div>
</div>
2
Answers
By updating The scripts i sorted it out!!
You should use breakpoints to diagnose which of the following is the problem:
Is the API not called? (Error code?)
-> Specify ‘Http Method’ in the controller.
-> e.g.)
2) Does the API call work, but no parameters come in?
-> Use another tool(e.g. chrome developer tools) to check if parameters are being passed.
-> If that doesn’t work, specify the type of the parameter.
e.g. )
3) Is DB select (or update) not possible?
-> Search for the message in the error window and correct the error.