InvalidCastException

[Quan69]

Here is method to execute stored procedure:

public short AddMember(JD.Library.Entities.AdultMember member)
{  
    try
    {
         short rv = 0;
         // Set SqlConnection to library database
         using (SqlConnection connection = new SqlConnection("Data Source=.\\SQLEXPRESS;
                    Initial Catalog=library;Integrated Security=True"))
         {
              connection.Open();

              // Set up SqlCommand to execute stored procedure
              using (SqlCommand command = new SqlCommand("dbo.AddAdult"))
              {                        
                  command.CommandType = CommandType.StoredProcedure;
                  command.Connection = connection;

                  // Setup variable to get return value (member ID)
                  SqlParameter retVal = new SqlParameter();
                  retVal.ParameterName = "@return_value";
                  retVal.SqlDbType = SqlDbType.SmallInt;
                  retVal.Direction = ParameterDirection.ReturnValue;
                  command.Parameters.Add(retVal);

                  command.Parameters.AddWithValue("@firstname", member.FirstName);
                  command.Parameters.AddWithValue("@middleinitial", member.MiddleInitial);
                  command.Parameters.AddWithValue("@lastname", member.LastName);
                  command.Parameters.AddWithValue("@street", member.Street);
                  command.Parameters.AddWithValue("@city", member.City);
                  command.Parameters.AddWithValue("@state", member.State);
                  command.Parameters.AddWithValue("@zip", member.ZipCode);
                  command.Parameters.AddWithValue("@phone_no", member.PhoneNumber);                        
                  int numrows = command.ExecuteNonQuery();
                  rv = (short)retVal.Value;     // GET ERROR HERE!!!
              }
              connection.Close();
          }
          return rv;
      }
      catch (SqlException ex)
      {
           // This is where you would raise your own custom exception like a LibraryException
           throw new Exception("Database error.", ex);
      }            
}  



AND HERE IS MY STORED PROCEDURE:

USE [library]				
GO

SET ANSI_NULLS ON
GO
-- QUOTED_IDENTIFIER: Specifies setting for usage of double quotation marks
SET QUOTED_IDENTIFIER ON
GO

IF ( OBJECT_ID('dbo.AddAdult') IS NOT NULL )    
   DROP PROCEDURE dbo.AddAdult;					
GO 

CREATE PROC [dbo].[AddAdult]					
	(
	@firstname VARCHAR(15) = NULL,
	@middleinitial CHAR(1) = NULL,
	@lastname VARCHAR(15) = NULL,
	@street VARCHAR(15) = NULL,
	@city VARCHAR(15) = NULL,
	@state CHAR(2) = NULL,
	@zip CHAR(10) = NULL,
	@phone_no CHAR(13) = NULL
	)       
AS

-- Test for NULLS and unwanted values
IF @firstname IS NULL
	BEGIN
		RAISERROR('First Name Can NOT be empty', 10, 1);
		RETURN -10
	END
IF @lastname IS NULL
	BEGIN
		RAISERROR('Last Name Can NOT be empty', 10, 1);
		RETURN -10
	END
IF @street IS NULL
	BEGIN
		RAISERROR('Street Can NOT be empty', 10, 1);
		RETURN -10
	END
IF @city IS NULL
	BEGIN
		RAISERROR('City can NOT be empty', 10, 1);
		RETURN -10;
	END	
IF @state IS NULL
	BEGIN
		RAISERROR('State Can NOT be empty', 10, 1);
		RETURN -10
	END	
IF @zip IS NULL
	BEGIN
		RAISERROR('Postal code can NOT be empty', 10, 1);
		RETURN -10;
	END

DECLARE @expr_date datetime, @member_no smallint;
SET @expr_date = DATEADD(yy, 1, GetDate());

BEGIN
	-- Start transaction
	BEGIN TRANSACTION
		SET NOCOUNT ON 

		-- First, insert into member table to get memberID
		INSERT INTO member (firstname, middleinitial, lastname)
		VALUES (@firstname, @middleinitial, @lastname);
	
		-- Commit Rollback if an error
		IF @@error <> 0
		BEGIN
			ROLLBACK TRANSACTION
			RETURN -@@error;
		END
				
		SET @member_no = scope_identity();	

		INSERT INTO adult (member_no, street, city, state, zip, phone_no, expr_date)
		VALUES (@member_no, @street, @city, @state, @zip, @phone_no, @expr_date);

		-- Commit Rollback if an error
		IF @@error <> 0
		BEGIN
			ROLLBACK TRANSACTION
			RETURN -@@error;
		END

	-- If this point is reached, safe to commit transaction
	COMMIT TRANSACTION
	RETURN @member_no
END
GO

Thanks for the help

[hensa22]

Quan

your parameter Out is SqlDbType.SmallInt.

I think your stored procedure attempted return a value range out of SmallInt acepted .