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

Question

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

i think you shouldn't add as a parameter – Guru Stron Sep 27 '13 at 10:57 — Guru Stron, Sep 27 '13 at 10:57
look at this http://stackoverflow.com/a/10614457/2501279 – Guru Stron Sep 27 '13 at 11:01 — Guru Stron, Sep 27 '13 at 11:01

score 1 · Answer 1 · answered Sep 27 '13 at 10:59

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

answered Sep 27 '13 at 10:59

Jeroen van Langen

21,446
3
42
57

I tried what you said, now its giving me, "Error converting data type nvarchar to int." this error. – user983983 Sep 27 '13 at 11:36
This means that your storedprocedure had defined the UserID parameter as nvarchar. Or you should change the storedprocedure to int, or you should change the SqlParameter dbType to nvarchar. – Jeroen van Langen Sep 27 '13 at 11:44
Storedprocedure has defined UserID as int. and I converting the parameter to nvarchar, then it gives me error "Error converting data type int to nvarchar.". This is getting very frustrating. – user983983 Sep 27 '13 at 12:00
Where do you assign the @UserID in the storedproc? It will be DBNull i guess. A DBNull cannot be converted to an int. Try setting at the end of the StoredProc `SET @UserID = SCOPE_IDENTITY()` – Jeroen van Langen Sep 27 '13 at 12:25
Tried. No avail. :( BTW Anyone knows how to edit a function in MS SQL server? – user983983 Sep 27 '13 at 12:55
Nothing in your current code gives any clue why it would be returning that error. I suggest you post a new question with _all_ of the stored proc code and this new error, referring to this question. To edit a stored proc in SQL Server, you need to script it out as modify, make changes then run the script to change it. There are many explanations if you google it. Like this one https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/modify-a-stored-procedure?view=sql-server-ver16 – Nick.Mc Aug 29 '22 at 09:12

score 0 · Answer 2 · answered Jun 19 '14 at 04:22

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);

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

2 Answers2