user2920046 user2920046 -4 years ago 143
SQL Question

Stored procedure or function expects parameter which is not supplied

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


Please help, thanks in advance...

Answer Source

Your stored procedure waits 5 parameters to input

@userID int, 
@userName varchar(50), 
@password nvarchar(50), 
@emailAddress nvarchar(50), 
@preferenceName varchar(20) 

So you should add all 5 parameters to this SP call:

    cmd.CommandText = "SHOWuser";
    cmd.Parameters.AddWithValue("@userID",userID);
    cmd.Parameters.AddWithValue("@userName", userName);
    cmd.Parameters.AddWithValue("@password", password);
    cmd.Parameters.AddWithValue("@emailAddress", emailAddress);
    cmd.Parameters.AddWithValue("@preferenceName", preferences);
    dbcon.Open();

PS: It's not clear what these parameter are for. You don't use these parameters in your SP body so your SP should looks like:

ALTER PROCEDURE [dbo].[SHOWuser] AS BEGIN ..... END
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download