Procedure or function expects parameter which was not supplied, when parameter is auto-generated

user983983 picture user983983 · Sep 27, 2013 · Viewed 9.7k times · Source

I'm trying to insert data in my db. this is the code im using

SqlCommand cmd = new SqlCommand("dbo.UsersInsert", cnn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@UserID", SqlDbType.Int);
cmd.Parameters.AddWithValue("@UserFirstName", FirstName.Text);
cmd.Parameters.AddWithValue("@UserMiddleName", MiddleName.Text);
cmd.Parameters.AddWithValue("@UserLastName", LastName.Text);

etc...

It is giving me error

Procedure or function 'UsersInsert' expects parameter '@UserID', which was not supplied.

on cmd.ExecuteNonQuery(); line.

Here, My UserID is auto-generated in my Database. How do I resolve this?

Stored Procedure

 @UserID INT OUTPUT,   
 @UserFirstName VARCHAR (50),  
 @UserMiddleName VARCHAR (50),  
 @UserLastName VARCHAR (50), etc... 

AS  
SET NOCOUNT ON  
DECLARE @ReturnValue int  
BEGIN  

 SELECT @ReturnValue = 0  

 INSERT [Users]  
 ( 
            [UserFirstName]
           ,[UserMiddleName]
           ,[UserLastName] etc... 
 values (

         @UserFirstName,  
                 @UserMiddleName,  
                 @UserLastName,  etc.. )
IF (@@Error <> 0) GOTO ERROR_HANDLER

 GOTO OnExit  
END  

Answer

Jeroen van Langen picture Jeroen van Langen · Sep 27, 2013

You should make it an ParameterDirection.Output

SqlParameter userIdParam = new SqlParameter("@UserID", SqlDbType.Int);
userIdParam.Direction = ParameterDirection.Output;
cmd.Parameters.Add(userIdParam);

This way you don't have to supply a value