Too many arguments specified

Question

I'm working on an application which allows one to list movies as well edit, insert and delete them. In the db I have three tables (Movie, Genre and MovieGenre) and this problem is related to adding a genre to a movie (by adding to the table MovieGenre, which keeps tracks of the movies genres).

Below is the relevant code, as well as the thrown exception. I just can't figure out why I get this error message, because I use the right number of arguments as I can see it in the c# code when calling the sproc.

Can somebody see what's the problem here?

Exception: Procedure or function usp_InsertMovieGenre has too many arguments specified.

Exception Details: System.Data.SqlClient.SqlException: Procedure or function usp_InsertMovieGenre has too many arguments specified.

c# code:

public void InsertMovieGenre(MovieGenre movieGenre) {

    using (SqlConnection conn = CreateConnection()) {
        try {
            SqlCommand cmd = new SqlCommand("dbo.usp_InsertMovieGenre", conn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@MovieID", SqlDbType.Int, 4).Value = movieGenre.MovieID;
            cmd.Parameters.Add("@GenreID", SqlDbType.Int, 4).Value = movieGenre.MovieGenreID;

            cmd.Parameters.Add("@MovieGenreID", SqlDbType.Int, 4).Direction = ParameterDirection.Output;

            conn.Open();

            cmd.ExecuteNonQuery();

            movieGenre.MovieID = (int)cmd.Parameters["@MovieGenreID"].Value;
        }
        catch {
        }
    }
}

sproc:

ALTER PROCEDURE usp_InsertMovieGenre
@GenreID varchar(500),
@MovieID int
AS
BEGIN
    INSERT INTO MovieGenre (GenreID, MovieID)
    VALUES (@GenreID, @MovieID);
END
GO

Answer 1

Error becaue of this line

cmd.Parameters.Add("@MovieGenreID", SqlDbType.Int, 4).Direction = ParameterDirection.Output; 

to resolve modify stored procdeurre

sproc:

ALTER PROCEDURE usp_InsertMovieGenre
    @GenreID varchar(500),
    @MovieID int,
    @MovieGenreID int output

    AS
    BEGIN
        INSERT INTO MovieGenre (GenreID, MovieID)
        VALUES (@GenreID, @MovieID);
     SELECT @MovieGenreID = @@identity   
    END
    GO

Answer 2

You have to add this output parameter to your Stored-Procedure:

ALTER PROCEDURE usp_InsertMovieGenre
@GenreID varchar(500),
@MovieID int,
@MovieGenreID int OUTPUT
AS
BEGIN
    INSERT INTO MovieGenre (GenreID, MovieID)
    VALUES (@GenreID, @MovieID);
    SELECT @MovieGenreID = SCOPE_IDENTITY();
END
GO

• Returning Data by Using OUTPUT Parameters
• SCOPE_IDENTITY