System.FormatException With SQLParameter

Below I have pasted the error message, hidden form fields, function code and even the stored procedure code. I'd be most grateful if anyone can show me the error of my ways!





Thanks,


theSpike





I'm calling a stored procedure and besides several varchar fields, I'm passing two integer values (employeeID and businessID). These values come from hidden fields on a form and it's the businessID that throws the format error. I call the same stored procedure for both insert and update (the SP evaluates employeeID to decide btwn insert/update) and the insert works perfectly, but the update throws the format exception error. The only difference is that the employeeID is 0 (zero) for an insert and > 0 when it's updating. I strikes me as particularly bizzare that the line that throws the exception is the businessID, not the employeeID because the businessID is the same value for both insert and update. It's the employeeID that is subject to change.








The error:


Exception Details: System.FormatException: Input string was not in a correct format.





Source Error:





Line 147: //businessID


Line 148: SqlParameter businessID = new SqlParameter("@businessID",SqlDbType.Int,4);


Line 149: businessID.Value = Convert.ToInt32(hdnBusinessID.Value);


Line 150: businessID.Direction = ParameterDirection.Input;


Line 151: theCommand.Parameters.Add(businessID);








I'm calling the SAME function for the insert and update, the same hiddent form fields exist


<input name="hdnBusinessID" id="hdnBusinessID" type="hidden" value="1" />


<input name="hdnEmployeeID" id="hdnEmployeeID" type="hidden" value="7" />


Here's the function that get's called:





void btnAddEmployee_Click(object sender, EventArgs e) {


int intNewEmployeeID;


string connectionstring = ConfigurationSettings.AppSettings["ConnectionString"];


SqlCommand theCommand = new SqlCommand();


theCommand.CommandText = "AMS_EmployeeSave";


theCommand.Connection = new SqlConnection(connectionstring);


theCommand.CommandType = CommandType.StoredProcedure;





// Add parameters to SqlCommand


//employeeID


SqlParameter empID = new SqlParameter("@employeeID",SqlDbType.Int,4);


empID.Value = Convert.ToInt32(hdnEmployeeID.Value);


empID.Direction = ParameterDirection.Input;


theCommand.Parameters.Add(empID);





//businessID


SqlParameter businessID = new SqlParameter("@businessID",SqlDbType.Int,4);


businessID.Value = Convert.ToInt32(hdnBusinessID.Value);


businessID.Direction = ParameterDirection.Input;


theCommand.Parameters.Add(businessID);





//title


SqlParameter title = new SqlParameter("@title",SqlDbType.Char,5);


title.Value = txtTitle.Text.ToString();


title.Direction = ParameterDirection.Input;


theCommand.Parameters.Add(title);





//firstname


SqlParameter firstName = new SqlParameter("@firstName",SqlDbType.VarChar,25);


firstName.Value = txtFirstName.Text.ToString();


firstName.Direction = ParameterDirection.Input;


theCommand.Parameters.Add(firstName);





//middleName


SqlParameter middleName = new SqlParameter("@middleName",SqlDbType.VarChar,15);


middleName.Value = txtMiddleName.Text.ToString();


middleName.Direction = ParameterDirection.Input;


theCommand.Parameters.Add(middleName);





//LastName


SqlParameter lastName = new SqlParameter("@lastName",SqlDbType.VarChar,25);


lastName.Value = txtLastName.Text.ToString();


lastName.Direction = ParameterDirection.Input;


theCommand.Parameters.Add(lastName);





//suffix


SqlParameter suffix = new SqlParameter("@suffix",SqlDbType.Char,5);


suffix.Value = txtSuffix.Text.ToString();


suffix.Direction = ParameterDirection.Input;


theCommand.Parameters.Add(suffix);





//email


SqlParameter email = new SqlParameter("@email",SqlDbType.VarChar,80);


email.Value = txtEmail.Text.ToString();


email.Direction = ParameterDirection.Input;


theCommand.Parameters.Add(email);





//password


SqlParameter password = new SqlParameter("@password",SqlDbType.Char,20);


password.Value = txtPassword.Text.ToString();


password.Direction = ParameterDirection.Input;


theCommand.Parameters.Add(password);





//newEmployeeID output parameter


SqlParameter newEmployeeID = new SqlParameter("@newEmployeeID",SqlDbType.Int,4);


newEmployeeID.Direction = ParameterDirection.Output;


theCommand.Parameters.Add(newEmployeeID);





theCommand.Connection.Open();





theCommand.ExecuteNonQuery();





intNewEmployeeID = Convert.ToInt16(theCommand.Parameters["@newEmployeeID"].Value);





theCommand.Connection.Close();





if(intNewEmployeeID == -1){


lblSaveResult.Text = "There was a problem saving the employee information";


}


else if(hdnEmployeeID.Value == "0"){


lblSaveResult.Text = "New employee successfully added";


}


else{


lblSaveResult.Text = "Employee successfully updated";


}





lblSaveResult.Visible = true;





}











STORED PROCEDURE


CREATE PROCEDURE [dbo].[xxxx]


(@employeeID int,


@businessID int,


@title char(5),


@firstName varchar(25),


@middleName varchar(15),


@lastName varchar(25),


@suffix char(5),


@email varchar(80),


@password char(20),


@newEmployeeID int output)





AS





declare @alreadyExists int





-- passed in ID = 0 means they're adding a new employee so do insert


if @employeeID = 0


begin


select @alreadyExists = count(*) from dbo.AMS_employee where emp_email = @email


if @alreadyExists = 0


begin


insert into AMS_employee (emp_businessID, emp_title, emp_firstName, emp_middleName, emp_lastName, emp_suffix, emp_email, emp_password)


values (@businessID, @title, @firstName, @middleName, @lastName, @suffix, @email, @password)





select @newEmployeeID = @@identity


end





else -- there is already an employee with that email address


select @newEmployeeID = -1


end





-- passed in ID > 0 means they're updating existing employee so do update


else if @employeeID > 0


begin


update AMS_employee set


emp_title = @title,


emp_firstName = @firstName,


emp_middleName = @middleName,


emp_lastName = @lastName,


emp_suffix = @suffix,


emp_email = @email


where emp_ID = @employeeID





select @newEmployeeID = @employeeID





-- only update the password if it one was provided


if len(rtrim(@password)) > 0


begin


update AMS_employee set emp_password = @password where emp_ID = @employeeID


end





end


GO