Question 85732 in ASP.NET Question posted in
The official documentation can be found here.

Procedure expects a parameter which "was not supplied" – Asp.net

I hate asking for help (in general) but also on here because most of my issues are probably easily solvable, but I’m new-ish to programming and cannot get past this bug.

I’m developing a C# application that takes user input from a .NET form and passes it to a database. On the click of a button, a method is called that generates and inserts a random ID into a column in the DB, stores the newly generated ID into a hidden field, and then calls the stored procedure to populate the rest of the data from what was typed in the webform fields.

Most of this was working fine until I started to store the ID in the hidden field, and now things have gone downhill. Now, I’m getting the error: "System.Data.SqlClient.SqlException: Procedure or function ‘User_Add’ expects parameter ‘@UID’, which was not supplied."

I have tried rearranging the code to position the method that generates the ID first, then calling the stored procedure, and even using a stored procedure to insert the ID into the table. Regardless of whatever rearrangements I make, I still get this error. I’m not sure what else I should be doing. Does anyone have suggestions? Below is some of my code; I’ve replaced the actual names of variables and whatnot with placeholders and eliminated several of the parameters for simplicity’s sake:

        public void UserDataPassage(string cnxnString)
        {
            using (SqlConnection cnxn = new SqlConnection(cnxnString))
            {
                cnxn.Open();

                SqlCommand cmd = new SqlCommand("[dbo].[Users_Add]", cnxn);

                // Create and prepare an SQL statement
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter item1Param = new SqlParameter("@item1", SqlDbType.NVarChar, 50);
                SqlParameter item2Param = new SqlParameter("@item2", SqlDbType.NVarChar, 50);
                
                // Check to see if values are null; pass to DB as a null value if true
                try
                {
                    if (item1Param == null)
                    {
                        item1Param.Value = DBNull.Value;
                    }
                    if (item2Param == null)
                    {
                        item2Param.Value = DBNull.Value;
                    }
                }
                catch (InvalidCastException)
                {

                }

                // Equate the parameters to the ASP IDs created in Default.aspx
                item1Param.Value = box_box1name.Text;
                item2Param.Value = box_box2name.Text;                   

                // Add values to the RBuilder database.
                cmd.Parameters.Add(item1Param).ToString();
                cmd.Parameters.Add(item2Param).ToString();

                // Call Prepare after setting the commandtext parameters
                cmd.Prepare();

                // Execute the query (does not return any values);
                cmd.ExecuteNonQuery();                
            }
        }

And my stored procedure is as follows:

USE [TestDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[User_Add]
    
    @item1 NVARCHAR(50),
    @item2 NVARCHAR(50),
    @userID INT OUT

AS

BEGIN

SET NOCOUNT ON;

    INSERT INTO [Table1] (item1, item2)
    VALUES (@item1, @item2)

SET  @userID= SCOPE_IDENTITY()


END
Tags:

2

Answers


  1. 0

    Add the output parameter. Your stored procedure requires 3 parameters. You need to supply the Parameter even if you won’t use it.

    SqlParameter userId = new SqlParameter("@userID", SqlDbType.Int);
OutputParam.Direction = ParameterDirection.Output;
cmd.Parameters.Add(OutputParam);
    Login or Signup to reply.
  2. 0

    You need to supply the @UID parameter as an output parameter

    SqlParameter uidParam = new SqlParameter("@UID", SqlDbType.Int) {Direction = ParameterDirection.Output};
cmd.Parameters.Add(uidParam);

    The ToString seems superfluous, and cmd.Prepare() is also unnecessary.

    Furthermore, your check for null makes no sense as you are checking the actual parameter object, which is never null. You can instead do this

    cmd.Parameters.Add("@item1", SqlDbType.NVarChar, 50).Value = box_box1name.Text ?? (object) DBNull.Value;
cmd.Parameters.Add("@item2", SqlDbType.NVarChar, 50).Value = box_box2name.Text ?? (object) DBNull.Value;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search