In my project I am inserting an employee and as my employee is inserted my save button HTML turns to update and on the back-end I am using the same insert function for updating the employee which I just inserted, my insert functionality is working perfectly but as I attempt to update the same record it inserts a new record in database instead of updating the data against the inserted ID, how do I update the existing or currently inserted user against their respective IDs.
I do not know why my update is not working and why I am not able to update right after inserting the user, every time I try to update the user I end up inserting the user again, and how should I restrict my application from inserting similar data again and again, the insert and update button are the same, as I mentioned above that on inserting the user I am changing the inner HTML of my button from save to update and using that same button to update
Here is my complete code, please let me know If I am making any mistakes at any part of my code, all the help I get from you guys is highly appreciated
My stored procedure code:
ALTER PROCEDURE [dbo].[InsEmpOfficialDetails]
(@EmpID int = NULL,
@UserName varchar(500) = NULL,
@pass varchar(500) = NULL,
@OfficialEmailAddress varchar(500) = NULL,
@Department varchar(500) = NULL,
@RoleID int = NULL,
@Role varchar(500) = NULL,
@IsAdmin bit = NULL,
@Designation varchar(500) = NULL,
@ReportToID int = NULL,
@ReportTo varchar(500) = NULL,
@JoiningDate datetime = NULL,
@IsPermanent bit = NULL,
@DateofPermanancy datetime = NULL,
@IsActive bit = NULL,
@HiredbyReference bit = NULL,
@HiredbyReferenceName varchar(500) = NULL,
@BasicSalary int = NULL,
@CurrentPicURL nvarchar(max) = NULL
-- @CreatedBy int,
-- @CreatedOn datetime,
-- @UpdatedOn datetime,
-- @UpdatedBy int
)
AS
BEGIN
IF EXISTS (SELECT 1 FROM Employee
WHERE UserName = @UserName
AND pass = @pass
AND OfficialEmailAddress = @OfficialEmailAddress
AND Department = @Department
AND RoleID = @RoleID
AND Role = @Role
AND IsAdmin = @IsAdmin
AND Designation = @Designation
AND ReportToID = @ReportToID
AND ReportTo = @ReportTo
AND JoiningDate = @JoiningDate
AND IsPermanent = @IsPermanent
AND DateofPermanancy = @DateofPermanancy
AND IsActive = @IsActive
AND HiredbyReference = @HiredbyReference
AND HiredbyReferenceName = HiredbyReferenceName
AND BasicSalary = @BasicSalary
AND CurrentPicURL = @CurrentPicURL)
BEGIN
UPDATE Employee
SET UserName = @UserName,
pass = @pass,
OfficialEmailAddress = @OfficialEmailAddress,
Department = @Department,
RoleID = @RoleID,
Role = @Role,
IsAdmin = @IsAdmin,
Designation = @Designation,
ReportToID = @ReportToID,
ReportTo = @ReportTo,
JoiningDate = @JoiningDate,
IsPermanent = @IsPermanent,
DateofPermanancy = @DateofPermanancy,
IsActive = @IsActive,
HiredbyReference = @HiredbyReference,
HiredbyReferenceName = HiredbyReferenceName,
BasicSalary = @BasicSalary,
CurrentPicURL = @CurrentPicURL
WHERE EmpID = @EmpID
END
ELSE
BEGIN
SET NOCOUNT ON;
INSERT INTO Employee(UserName, pass,
OfficialEmailAddress,Department,
RoleID, Role, IsAdmin, Designation,
ReportToID, ReportTo, JoiningDate,
IsPermanent, DateofPermanancy, IsActive,
HiredbyReference, HiredbyReferenceName,
BasicSalary, CurrentPicURL)
VALUES (@UserName, @pass, @OfficialEmailAddress, @Department,
@RoleID, @Role, @IsAdmin, @Designation,
@ReportToID, @ReportTo, @JoiningDate,
@IsPermanent, @DateofPermanancy, @IsActive,
@HiredbyReference, @HiredbyReferenceName,
@BasicSalary, @CurrentPicURL)
SELECT SCOPE_IDENTITY();
END
END
In my HTML on top of my input fields I am storing the currently inserted user ID in a hidden field like this
<input type="hidden" class="HiddenID" />
I do not know how to use this hidden field ID for updating the User right after inserting, because as I mentioned my insert and update functions both lies on same button
My ajax code:
$('.empOfficialDetails').click(function (ev) {
ev.preventDefault();
var data = new Object();
data.UserName = $('#username').val();
data.UPassword = $('#userpass').val();
data.OfficialEmailAddress = $('#officialemail').val();
data.Departments = $('#departments :selected').text();
data.Designation = $('#designation :selected').text();
data.RoleID = $('#role').val();
data.Role = $('#role :selected').text();
data.ReportToID = $('#reportToID').val();
data.ReportTo = $('#reportTo :selected').text();
data.JoiningDate = $('#joindate').val();
data.IsAdmin = $('#isAdmin :selected').val() ? 1 : 0;
data.IsActive = $('#isActive :selected').val() ? 1 : 0;
data.IsPermanent = $('#isPermanent :selected').val() ? 1 : 0;
data.DateofPermanancy = $('#permanantdate').val();
data.HiredbyReference = $('#hiredbyRef :selected').val() ? 1 : 0;
data.HiredbyReferenceName = $('#refePersonName').val();
data.BasicSalary = $('#basicSalary').val();
data.CurrentPicURL = $('.picture').val();
//data.CurrentPicURL = $('.picture')[0].files;
if (data.UserName && data.UPassword && data.OfficialEmailAddress && data.Departments && data.Designation && data.Role && data.IsAdmin && data.IsPermanent) {
$.ajax({
url: 'http://localhost:1089/api/Employee/EmpOfficialDetails',
type: "POST",
dataType: 'json',
contentType: "application/json",
data: JSON.stringify(data),
enctype: 'multipart/form-data',
beforeSend: function () {
$("#dvRoomsLoader").show();
},
complete: function () {
$("#dvRoomsLoader").hide();
},
success: function (data) {
var ID = parseInt(data);
if (ID > 0) {
//var id = data;
$(".HiddenID").val(data);
//var id = $(".HiddenID").val();
$('#official').css('display', 'block');
$('#official').html("Employees Official details added successfully...!");
$('#official').fadeOut(25000);
$("#dvRoomsLoader").show();
$('.empOfficialDetails').html("Update <i class='fa fa-angle-right rotate-icon'></i>");
}
else {
$('#official').find("alert alert-success").addClass("alert alert-danger").remove("alert alert-success");
}
},
error: function (ex) {
alert("There was an error while submitting employee data");
alert('Error' ex.responseXML);
alert('Error' ex.responseText);
alert('Error' ex.responseJSON);
alert('Error' ex.readyState);
alert('Error' ex.statusText);
}
});
}
return false;
});
my controller code:
public int Emp_OfficialDetails(Employee emp)
{
//SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["AmanraHRMS"].ConnectionString);
var con = DB.getDatabaseConnection();
SqlCommand com = new SqlCommand("InsEmpOfficialDetails", con);
com.CommandType = CommandType.StoredProcedure;
#region Employee Official Details Insert Code block
com.Parameters.AddWithValue("@UserName", emp.UserName);
com.Parameters.AddWithValue("@pass", emp.UPassword);
com.Parameters.AddWithValue("@OfficialEmailAddress", emp.OfficialEmailAddress);
com.Parameters.AddWithValue("@Department", emp.Departments);
com.Parameters.AddWithValue("@Role", emp.Role);
com.Parameters.AddWithValue("@IsAdmin", Convert.ToBoolean(emp.IsAdmin));
com.Parameters.AddWithValue("@Designation", emp.Designation);
com.Parameters.AddWithValue("@ReportToID", emp.ReportToID);
com.Parameters.AddWithValue("@ReportTo", emp.ReportTo);
com.Parameters.AddWithValue("@JoiningDate", Convert.ToDateTime(emp.JoiningDate));
com.Parameters.AddWithValue("@IsPermanent", Convert.ToBoolean(emp.IsPermanent));
com.Parameters.AddWithValue("@DateofPermanancy", Convert.ToDateTime(emp.DateofPermanancy));
com.Parameters.AddWithValue("@IsActive", Convert.ToBoolean(emp.IsActive));
com.Parameters.AddWithValue("@HiredbyReference", Convert.ToBoolean(emp.HiredbyReference));
com.Parameters.AddWithValue("@HiredbyReferenceName", emp.HiredbyReferenceName);
com.Parameters.AddWithValue("@BasicSalary", emp.BasicSalary);
com.Parameters.AddWithValue("@CurrentPicURL", emp.CurrentPicURL);
#endregion
//var file = emp.CurrentPicURL;
//EmployeeImage(file);
var ID = com.ExecuteScalar();
com.Clone();
return Convert.ToInt32(ID);
}
//Ajax call hit this method from AddEmployee page
[Route("api/Employee/EmpOfficialDetails")]
[HttpPost]
public int? EmpOfficialDetails(Employee emp)
{
IHttpActionResult ret;
try
{
var id = Emp_OfficialDetails(emp);
return id;
}
catch (Exception ex)
{
ret = InternalServerError(ex);
}
return null;
}
all help is highly appreciated, please let me know where I am wrong happy coding
CodePudding user response:
fix your hidden field
<input type="hidden" asp-for ="EmpID" id="empId" class="HiddenID" value="@Model.EmpID />
fix your stored procedure. for exist would be enough EmpID, too many parameters can give the wrong result
BEGIN
IF EXISTS (SELECT 1 FROM Employee
WHERE EmpID = @EmpID)
BEGIN
UPDATE Employee
SET UserName = @UserName,
.....
and the most important add EmpId to ajax
var data = new Object();
data.EmpID = $('#empId').val();
and action command
com.Parameters.AddWithValue("@EmpID", emp.EmpID);