错误:过程或函数需要未提供的参数 [英] Error: Procedure or function expects parameter which was not supplied

问题描述

当前，我在Visual Studio中的程序将我的Repeater中的数据动态添加到数据库中.

Currently, my program in Visual Studio dynamically adds my data from my Repeater into my database.

现在，我需要添加ID，我的EventId和FormId，这些是我在中继器之外手动收集的.

Now I need to add the ID, my EventId and FormId, which I collected manually outside of the Repeater.

我需要添加以下内容:

 sqlCmd.Parameters.Add("@EventId", SqlDbType.Int).Value = eventId;
 sqlCmd.Parameters.Add("@FormId", SqlDbType.Int).Value = formId;

但是，如何设置我的代码，当我添加以下代码时却出现错误:

However with how my code is setup, it gives an error when I add this code that says:

其他信息:过程或函数'spInsFormRegistrant'期望未提供参数"@EventId".

Additional information: Procedure or function 'spInsFormRegistrant' expects parameter '@EventId', which was not supplied.

工作代码(已注释掉错误代码):

Working code (with error code commented out):

 protected void BtnSubmit_Click(object sender, EventArgs e)
 {
            using (SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["Events2"].ConnectionString))
            {
                sqlConn.Open();

                using (SqlCommand sqlCmd = new SqlCommand())
                {

                   //sqlCmd.Parameters.Add("@EventId", SqlDbType.Int).Value = eventId;
                   //sqlCmd.Parameters.Add("@FormId", SqlDbType.Int).Value = formId;
                    foreach (RepeaterItem rpItem in RepeaterForm.Items)
                    {
                        Label lblDisplayName = rpItem.FindControl("lblDisplayName") as Label;
                        Label lblColumnName = rpItem.FindControl("lblColumnName") as Label;
                        TextBox txtColumnValue = rpItem.FindControl("txtColumnValue") as TextBox;

                        if (txtColumnValue != null)
                        {
                            sqlCmd.Connection = sqlConn;
                            sqlCmd.CommandType = CommandType.StoredProcedure;
                            sqlCmd.CommandText = "spInsFormRegistrant";
                            sqlCmd.Parameters.Clear();

                            sqlCmd.Parameters.Add("@ColumnName", SqlDbType.NVarChar).Value = lblColumnName.Text;
                            sqlCmd.Parameters.Add("@ColumnValue", SqlDbType.NVarChar).Value = txtColumnValue.Text;

                        sqlCmd.ExecuteNonQuery();
                        }
                    }
                }
            }
            PnlNone.Visible = false;
            PnlExist.Visible = false;
            PnlSuccess.Visible = true;
            PnlFail.Visible = false;
        }

因此，我只需要知道在 @EventId 和 @FormId 中添加的位置即可正常运行.每次尝试都行不通.我必须丢失一些小东西，以免产生错误.还是我的存储过程有问题...?

So I just need to know where to add in @EventId and @FormId for this to function correctly. Each time I try it, it does not work. I must be missing something small for this to not produce an error. Or maybe it is an issue with my stored procedure...?

存储过程

ALTER PROCEDURE [dbo].[spInsFormRegistrant]
    -- Add the parameters for the stored procedure here
     @EventId int,
     @FormId int,
     @ColumnName varchar(100),
    @ColumnValue varchar(100)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
     declare @Query nvarchar(4000)
    declare @ParmDefinition nvarchar(500);

    set @Query = 'INSERT into Registrant(DateCreated,EventId,FormId,'+ (@ColumnName) +') values (CURRENT_TIMESTAMP, @EventId, @FormId, @ColumnValue)'
    set @ParmDefinition = N'@ColumnValue varchar(100)'
    exec sp_executesql @Query, @ParmDefinition, @ColumnValue = @ColumnValue

END

推荐答案

您没有为eventID和FormID添加参数，但是您应该尝试使用其他方法.不要每次都创建参数.您可以在进入foreach循环之前创建它们一次，然后在循环内部仅更改它们的值

You are not adding the parameter for eventID and for FormID, but you should try to use a different approach. Do not create everytime the parameters. You could create them just one time before entering the foreach loop and then, inside the loop change only their value

// This part never changes so, set it up just one time before the loop
sqlCmd.Connection = sqlConn;
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "spInsFormRegistrant";
sqlCmd.Parameters.Add("@EventId", SqlDbType.Int).Value = eventId;
sqlCmd.Parameters.Add("@FormId", SqlDbType.Int).Value = formId;

// These twos change inside the loop, so we don't need the value here
sqlCmd.Parameters.Add("@ColumnName", SqlDbType.NVarChar);
sqlCmd.Parameters.Add("@ColumnValue", SqlDbType.NVarChar);


foreach (RepeaterItem rpItem in RepeaterForm.Items)
{
    Label lblDisplayName = rpItem.FindControl("lblDisplayName") as Label;
    Label lblColumnName = rpItem.FindControl("lblColumnName") as Label;
    TextBox txtColumnValue = rpItem.FindControl("txtColumnValue") as TextBox;

    if (txtColumnValue != null)
    {
        sqlCmd.Parameters["@ColumnName"].Value = lblColumnName.Text;
        sqlCmd.Parameters["@ColumnValue"].Value = txtColumnValue.Text;
        sqlCmd.ExecuteNonQuery();
    }
}

当然，您不需要调用 Parameters.Clear

然后，在将参数传递给存储过程中的sp_executesql调用的方式上存在问题.该系统存储过程要求您为查询中使用的每个参数设置数据类型以及这些参数的初始化列表.

Then there is a problem in the way in which you pass the paramenters to the sp_executesql call inside the stored procedure. That system storedprocedure requires that you set the datatype for every parameter used in the query and an initialization list of these parameters.

您应该写

...
-- Insert statements for procedure here
declare @Query nvarchar(4000)
declare @ParmDefinition nvarchar(500);

set @Query = 'INSERT into Registrant(DateCreated,EventId,FormId,'+ 
             (@ColumnName) +') values (CURRENT_TIMESTAMP, @EventId, @FormId, @ColumnValue)'
set @ParmDefinition = N'@ColumnValue varchar(100), @EventID int, @FormID int'
exec sp_executesql @Query, @ParmDefinition, 
                   @ColumnValue = @ColumnValue,
                   @EventID = @EventID,
                   @FormID = @FormID

这篇关于错误:过程或函数需要未提供的参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！