Guys this is pulling my air out. I dont what i am doing wrong with this. Please i have a stored procedure and a 3 tier app layer that work with it. I will post my store procedure first, followed by the the DAL Layer code. The error i am getting is [The Procedure ('ProcedureName') expects parameter @Error which is not supplied]
Alter Procedure Proc_Create_EmployeePersonalDetails
(
@FirstName nvarchar(256),
@Surname nvarchar(256),
@Middlename nvarchar(256),
@MaritalStatus nvarchar(20),
@Gender nvarchar(20),
@DateOfBirth datetime,
@CountryOfBirth nvarchar(256),
@CityOfBirth nvarchar(256),
@BloodType nvarchar(256),
@Nationality nvarchar(256),
@AddressHomeCountry nvarchar(300),
@AddressWorkingCountry nvarchar(300),
@HomePhoneNumber nvarchar(256),
@WorkPhoneNumber nvarchar(256),
@PersoanlEmailAddress nvarchar(256),
@WorkEmailAddress nvarchar(256),
@PhotoPath nvarchar(300),
@CreatedDate datetime,
@Error varchar(300) OUTPUT
)
As
Begin
Declare @TempPersonalEmail nvarchar(256)
SELECT @TempPersonalEmail = PersonalEmailAddress FROM FSA_EmployeePersonalDetails Where PersonalEmailAddress = @PersoanlEmailAddress
Begin
IF (@TempPersonalEmail IS NOT NULL)
Set @Error = 'The specified personal email address already exist'
Return @Error
End
Declare @TempWorkEmail nvarchar(256)
SELECT @TempWorkEmail = WorkEmailAddress FROM FSA_EmployeePersonalDetails Where WorkEmailAddress = @WorkEmailAddress
Begin
IF (@TempWorkEmail IS NOT NULL)
Set @Error = 'The specified work email address already exist'
Return @Error
End
Insert Into FSA_EmployeePersonalDetails
(
FirstName, Surname, Middlename, MaritalStatus, Gender, DateOfBirth, CountryOfBirth, CityOfBirth, BloodType,
Nationality, AddressHomeCountry, AddressWorkingCountry, HomePhoneNumber, WorkPhoneNumber, PersonalEmailAddress,
WorkEmailAddress, PhotoPath, CreatedDate
)
Values
(
@FirstName, @Surname, @Middlename, @MaritalStatus, @Gender, @DateOfBirth, @CountryOfBirth,
@CityOfBirth, @BloodType, @Nationality, @AddressHomeCountry, @AddressWorkingCountry, @HomePhoneNumber, @WorkPhoneNumber,
@PersoanlEmailAddress, @WorkEmailAddress, @PhotoPath, @CreatedDate
)
Declare @ErrorCounter int
Select @ErrorCounter = @@Error
IF(@ErrorCounter > 0)
Begin
Set @Error = 'An internal error occured, the employee information can not be created'
Return @Error
End
ELSE
Begin
Set @Error = 'The employee information you provided has been successfuly submitted...'
Return @Error
End
End
Here is the C# Code that Works with it (The DAL Layer)
public void SavePersonalDetails(PersonalDetailsObject employeePersonalDetail)
{
SqlConnection con = new SqlConnection(FSAHRMSConnectionString.GetFSAHRMSConnectionString());
try
{
con.Open();
SqlCommand cmd = new SqlCommand("Proc_Create_EmployeePersonalDetails", con);
cmd.CommandType = CommandType.StoredProcedure;
#region
cmd.Parameters.AddWithValue("@FirstName", employeePersonalDetail.FirstName);
cmd.Parameters.AddWithValue("@Surname", employeePersonalDetail.SurName);
cmd.Parameters.AddWithValue("@Middlename", employeePersonalDetail.MiddleName);
cmd.Parameters.AddWithValue("@MaritalStatus", employeePersonalDetail.MaritalStatus);
cmd.Parameters.AddWithValue("@Gender", employeePersonalDetail.Gender);
cmd.Parameters.AddWithValue("@DateOfBirth", employeePersonalDetail.DateOfBirth);
cmd.Parameters.AddWithValue("@CountryOfBirth", employeePersonalDetail.CountryOfBirth);
cmd.Parameters.AddWithValue("@CityOfBirth", employeePersonalDetail.City);
cmd.Parameters.AddWithValue("@BloodType", employeePersonalDetail.BloodType);
cmd.Parameters.AddWithValue("@Nationality", employeePersonalDetail.Nationality);
cmd.Parameters.AddWithValue("@AddressHomeCountry", employeePersonalDetail.HomeCountryAddress);
cmd.Parameters.AddWithValue("@AddressWorkingCountry", employeePersonalDetail.WorkingCountryAddress);
cmd.Parameters.AddWithValue("@HomePhoneNumber", employeePersonalDetail.HomePhone);
cmd.Parameters.AddWithValue("@WorkPhoneNumber", employeePersonalDetail.WorkPhone);
cmd.Parameters.AddWithValue("@PersoanlEmailAddress", employeePersonalDetail.PersonalEmail);
cmd.Parameters.AddWithValue("@WorkEmailAddress", employeePersonalDetail.WorkEmail);
cmd.Parameters.AddWithValue("@PhotoPath", employeePersonalDetail.PhotoPath);
cmd.Parameters.AddWithValue("@CreatedDate", employeePersonalDetail.CreatedDate);
cmd.Parameters.Add("@Error", SqlDbType.VarChar, 300);
#endregion
cmd.Parameters["@Error"].Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();
employeePersonalDetail.EmployeeError = cmd.Parameters["@Error"].Value.ToString();
}
finally
{
if (con.State == ConnectionState.Open) { con.Close(); }
}
}
And here is the UI code behind file that interact with the DAL
if (!Page.IsValid)
{
return;
}
else
{
PersonalDetailsObject personalObj = new PersonalDetailsObject();
PersonalDetailsDAL personalDal = new PersonalDetailsDAL();
#region Set The Personal Details Object Properties
personalObj.FirstName = txtPdFirstName.Text;
personalObj.SurName = txtPdSurname.Text;
personalObj.MiddleName = txtPdMiddleName.Text;
personalObj.MaritalStatus = rdPdMaritalStatus.SelectedItem.Text;
personalObj.Gender = rdPdGender.SelectedItem.Text;
personalObj.DateOfBirth = DateTime.Parse(txtPdDOB.Text);
personalObj.CountryOfBirth = ddpCountryOfBirth.SelectedItem.Text;
personalObj.City = txtPdCity.Text;
personalObj.BloodType = ddpPdBloodType.SelectedItem.Text;
personalObj.Nationality = ddpNationality.SelectedItem.Text;
personalObj.HomeCountryAddress = txtPdHomeCountryAddress.Text;
personalObj.WorkingCountryAddress = txtPdWorkingCountryAddress.Text;
personalObj.HomePhone = txtPdHomePhone.Text;
personalObj.WorkPhone = txtPdWorkingPhone.Text;
personalObj.PersonalEmail = txtPdPersonalEmailAddress.Text;
personalObj.WorkEmail = txtPdWorkEmailAddress.Text;
personalObj.PhotoPath = (pdPhoto.PostedFile.FileName.Length > 0) ? pdPhoto.PostedFile.FileName : "Not Available";
personalObj.CreatedDate = DateTime.Now;
#endregion
try
{
personalDal.SavePersonalDetails(personalObj);
ResetPersonalDetailsForm();
lblPDSuccess.Visible = true;
lblPDSuccess.Text = personalObj.EmployeeError;
}
catch (Exception sqlex)
{
divPDError.Visible = true;
lblPDError.Text = sqlex.Message;
}
}