I am trying to insert data into a SQL Server database by calling a stored procedure, but I am getting the error
Procedure or function 'SHOWuser' expects parameter '@userID', which was not supplied.
My stored procedure is called SHOWuser
. I have checked it thoroughly and no parameters is missing.
My code is:
public void SHOWuser(string userName, string password, string emailAddress, List<int> preferences) { SqlConnection dbcon = new SqlConnection(conn); try { SqlCommand cmd = new SqlCommand(); cmd.Connection = dbcon; cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandText = "SHOWuser"; cmd.Parameters.AddWithValue("@userName", userName); cmd.Parameters.AddWithValue("@password", password); cmd.Parameters.AddWithValue("@emailAddress", emailAddress); dbcon.Open(); int i = Convert.ToInt32(cmd.ExecuteScalar()); cmd.Parameters.Clear(); cmd.CommandText = "tbl_pref"; foreach (int preference in preferences) { cmd.Parameters.Clear(); cmd.Parameters.AddWithValue("@userID", Convert.ToInt32(i)); cmd.Parameters.AddWithValue("@preferenceID", Convert.ToInt32(preference)); cmd.ExecuteNonQuery(); } } catch (Exception) { throw; } finally { dbcon.Close(); }
and the stored procedure is:
ALTER PROCEDURE [dbo].[SHOWuser] ( @userName varchar(50), @password nvarchar(50), @emailAddress nvarchar(50) ) AS BEGIN INSERT INTO tbl_user(userName, password, emailAddress) VALUES (@userName, @password, @emailAddress) SELECT tbl_user.userID, tbl_user.userName, tbl_user.password, tbl_user.emailAddress, STUFF((SELECT ',' + preferenceName FROM tbl_pref_master INNER JOIN tbl_preferences ON tbl_pref_master.preferenceID = tbl_preferences.preferenceID WHERE tbl_preferences.userID = tbl_user.userID FOR XML PATH ('')), 1, 1, ' ' ) AS Preferences FROM tbl_user SELECT SCOPE_IDENTITY(); END
This is the second stored procedure tbl_pref
which is used in the same function:
ALTER PROCEDURE [dbo].[tbl_pref] @userID int, @preferenceID int AS BEGIN INSERT INTO tbl_preferences(userID, preferenceID) VALUES (@userID, @preferenceID) END
The simplest kind of SQL Server stored procedure that you can call is one that contains no parameters and returns a single result set. The Microsoft JDBC Driver for SQL Server provides the SQLServerStatement class, which you can use to call this kind of stored procedure and process the data that it returns.
Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values). Functions can have only input parameters for it whereas Procedures can have input/output parameters . Function takes one input parameter it is mandatory but Stored Procedure may take o to n input parameters..
The main difference between these objects is that function has a return value, and procedure has not. A stored procedures and functions may have input, output, and input/output parameters.
So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.
In my case I received this exception even when all parameter values were correctly supplied but the type of command was not specified :
cmd.CommandType = System.Data.CommandType.StoredProcedure;
This is obviously not the case in the question above, but exception description is not very clear in this case, so I decided to specify that.
I came across this issue yesterday, but none of the solutions here worked exactly, however they did point me in the right direction.
Our application is a workflow tool written in C# and, overly simplified, has several stored procedures on the database, as well as a table of metadata about each parameter used by each stored procedure (name, order, data type, size, etc), allowing us to create as many new stored procedures as we need without having to change the C#.
Analysis of the problem showed that our code was setting all the correct parameters on the SqlCommand
object, however once it was executed, it threw the same error as the OP got.
Further analysis revealed that some parameters had a value of null
. I therefore must draw the conclusion that SqlCommand
objects ignore any SqlParameter
object in their .Parameters
collection with a value of null
.
There are two solutions to this problem that I found.
In our stored procedures, give a default value to each parameter, so from @Parameter int
to @Parameter int = NULL
(or some other default value as required).
In our code that generates the individual SqlParameter
objects, assigning DBNull.Value
instead of null
where the intended value is a SQL NULL
does the trick.
The original coder has moved on and the code was originally written with Solution 1 in mind, and having weighed up the benefits of both, I think I'll stick with Solution 1. It's much easier to specify a default value for a specific stored procedure when writing it, rather than it always being NULL
as defined in the code.
Hope that helps someone.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With