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

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
c++sharpsqldatabasesql-server-2008autogeneratecolumn


Solution 1:[1]

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

Solution 2:[2]

The following code should work, change the sql to accept the parameters

SqlCommand cmd = new SqlCommand("dbo.UsersInsert @UserID out, @UserFirstName, @UserMiddleName, @UserLastName", cnn);

Then change the parameters as follows

var id = new SqlParameter("UserID", System.Data.SqlDbType.Int);
id.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(id);

cmd.Parameters.AddWithValue("UserFirstName", FirstName.Text);
cmd.Parameters.AddWithValue("UserMiddleName", MiddleName.Text);
cmd.Parameters.AddWithValue("UserLastName", LastName.Text);

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Jeroen van Langen
Solution 2 3dd

Related Questions