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 >
<h2>Employees Record</h2>
<button type="button" data-toggle="modal" data-target="#myModal" onclick="clearTextBox();">Add New Employee</button><br /><br />
<table >
<thead>
<tr>
<th>
ID
</th>
<th>
Name
</th>
<th>
Age
</th>
<th>
State
</th>
<th>
Country
</th>
<th>
Action
</th>
</tr>
</thead>
<tbody ></tbody>
</table>
</div>
<div id="myModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
<div >
<div >
<div >
<button type="button" data-dismiss="modal">X</button>
<h4 id="myModalLabel">Add Employee</h4>
</div>
<div >
<form>
<div >
<label for="EmployeeId">ID</label>
<input type="text" id="EmployeeID" placeholder="Id" disabled="disabled" />
</div>
<div >
<label for="EmployeeName">EmployeeName</label>
<input type="text" id="Name" placeholder="EmployeeName" />
</div>
<div >
<label for="Age">Age</label>
<input type="text" id="Age" placeholder="Age" />
</div>
<div >
<label for="EmployeeState">EmployeeState</label>
<input type="text" id="State" placeholder="EmployeeState" />
</div>
<div >
<label for="Country">Country</label>
<input type="text" id="Country" placeholder="Country" />
</div>
</form>
</div>
<div >
<button type="button" id="btnAdd" onclick="return Add();">Add</button>
<button type="button" id="btnUpdate" style="display:none;" onclick="Update();">Update</button>
<button type="button" data-dismiss="modal">Close</button>
</div>
</div>
</div>
</div>
CodePudding user response:
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.)[HttpPost] public JsonResult List() { return Json(empDB.ListAll(), JsonRequestBehavior.AllowGet); }
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. )
public JsonResult Add([FromForm] Employee emp)
3) Is DB select (or update) not possible?
-> Search for the message in the error window and correct the error.
CodePudding user response:
<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>
By updating The scripts i sorted it out!!