System.FormatException with SQLParameter RSS

• • Reply
  

  theSpike

  Member

  10 Points

  2 Posts

  System.FormatException with SQLParameter

  Feb 06, 2005 10:46 PM|LINK

  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

• • Reply
  

  ndinakar

  All-Star

  49092 Points

  6868 Posts

  Moderator

  MVP

  Re: System.FormatException with SQLParameter

  Feb 07, 2005 03:32 PM|LINK

  (1) check for nulls and pass in a zero( or any appropriate number) if null. (2) do a typecast to int. convert.toint16(businessID) before you send it to the db. hth
  ***********************
  Dinakar Nethi
  Life is short. Enjoy it.
  ***********************
• • Reply
  

  martiendejon...

  Member

  4 Points

  2 Posts

  Re: System.FormatException with SQLParameter

  May 24, 2012 10:44 AM|LINK

  Is there any way to find out the name of the parameter that is generating the exception?