Stored Procedure Out Put Parameter

See more:

C#3.0

C#

ASP.NET

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();

            //return rowsAffected;
        }
        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
            {
                //int rowsAffected = 
                    personalDal.SavePersonalDetails(personalObj);
                //if (rowsAffected > 0)
                //{

                    ResetPersonalDetailsForm();
                    lblPDSuccess.Visible = true;
                    lblPDSuccess.Text = personalObj.EmployeeError;
                //}
                //else
                //{
                //    divPDError.Visible = true;
                //    lblPDError.Text = personalObj.EmployeeError;
                //}
            }
            catch (Exception sqlex)
            {
                divPDError.Visible = true;
                lblPDError.Text = sqlex.Message;
            }          
        }