过程指定了太多参数 [英] Procedure has too many arguments specified

问题描述

在我的项目上工作，在使用存储过程将数据插入sql server时面临错误，但错误弹出说System.Data.SqlClient.SqlException（0x80131904）：过程或函数uspSetSubscriptions指定的参数太多
 

 创建  Proc  uspSetSubscriptions 
  @ packageType   varchar （< span class =code-digit> 25 ）， @ UserID   int ， @ ConstID   int  
  AS  
  BEGIN  
  DECLARE   @ PackageId   int ， @ notific   int ， @ articles   int ， @MaxVideos   int ， @ mxevent   int ， @ mxallias   int  
  SELECT   @ notific  = MaxNotification  FROM  TblPackages  WHERE  Category =  @ packageType  
  SELECT   @articles  = MaxVideos  FROM  TblPackages  WHERE 类别=  @ packageType  
  SELECT   @ MaxVideos  = MaxArticles  FROM  TblPackages  WHERE 类别=  @ packageType  
  SELECT   @ mxevent  = MaxEvent  FROM  TblPackages  WHERE 类别=  @ packageType  
  SELECT   @ mxallias  = MaxAllias  FROM  TblPackages  WHERE  Category =  @ packageType  
  SELECT   @ PackageId  = PackageID  FROM  TblPackages  WHERE  Category =  @ packageType  
  INSERT   INTO  Tb lSubscription（UserId，PackageId，ConstID，StartDate，FinishDate，MaxNotification，MaxArticles，MaxVideos，MaxEvent，MaxAllias，IsActive，IsPaid）
  VALUES （ @ UserID ， @ PackageId ， @ ConstID ，GetDate（） ，GETDATE（）+ 30， @ notific ， @ articles ， @MaxVideos ， @ mxevent ， @ mxallias ， 0 ， 0 ） SELECT   @ @IDENTITY  
  SELECT   SCOPE_IDENTITY （）  -   订阅ID  
  END  

  protected   void  BtnSubscribe_Click（ object  sender，EventArgs e）
 {
 Response.Write（AppUserID）;  //   13  
 Response.Write（Convert.ToString（ViewState [  packtype]））;  //  基本 
 Response.Write（DDConstituency.SelectedValue）;  //   1  
 
  string  PackgType = Convert.ToString（ViewState [  packtype]）; 
 
 尝试 
 {
 db1.sqlcmd =  new  SqlCommand（  uspSetSubscriptions）; 
 使用（SqlDataAdapter sda =  new  SqlDataAdapter（））
 {
 db1.sqlcmd.CommandType = CommandType.StoredProcedure; 
 db1.sqlcmd.Parameters.AddWithValue（  @ UserID，AppUserID）; 
 db1.sqlcmd.Parameters.AddWithValue（  @ packageType，Convert.ToString（ViewState） [  packtype]））; 
 db1.sqlcmd.Parameters.AddWithValue（  @ ConstID，DDConstituency.SelectedValue）; 
 db1.sqlcmd.Connection = db1.sqlcon; 
 db1.sqlcon.Open（）; 
 Subscripid = Convert.ToInt32（db1.sqlcmd.ExecuteScalar（））; 
 ViewState [  Subscripid] = Subscripid; 
} 
} 
  catch （Exception ex）{Response.Write（ex）; } 
  finally  {db1.sqlcon.Close（）; } 
} 

解决方案

为什么你有 SqlDataAdapter ？并且您没有将 ConstID 转换为整数值。你最好使用块 SqlConnection 和 SqlCommand 对象。不要共享连接，在需要时创建新连接，并在完成后将其丢弃。

 使用（SqlConnection con =  new  SqlConnection（yourconnnectionString））
 使用（SqlCommand cmd =  new  SqlCommand（  uspSetSubscriptions，con））
 {
 cmd.CommandType = CommandType.StoredProcedure; 
 cmd.Parameters.AddWithValue（  @ UserID，Convert.ToInt32（AppUserID）） ; 
 cmd.Parameters.AddWithValue（  @ packageType，Convert.ToString（ViewState [< span class =code-string>  packtype]））; 
 cmd.Parameters.AddWithValue（  @ ConstID，Convert.ToInt32（DDConstituency.SelectedValue ））; 
 con.Open（）; 
 Subscripid = Convert.ToInt32（cmd.ExecuteScalar（））; 
} 

2注释：

1）为什么要使用多个变量，如果你可以使用简单的 INSERT INTO + SELECT 。

2）代码落后！





AD 1）

试试用以下内容替换SP的主体：

  INSERT   INTO  TblSubscription（UserId，PackageId，ConstID，StartDate，FinishDate，MaxNotification，MaxArticles，MaxVideos，MaxEvent，MaxAllias，IsActive，IsPaid）
  SELECT  < span class =code-sdkkeyword> @ UserID   AS  UserID，PackageID， @ ConstID   AS  ConstID，GetDate（） AS  StartDate，GETDATE（）+ 30  AS  FinishDate，MaxNotification，MaxVideos，MaxArticles，MaxEvent，MaxAllias，PackageID， 0   AS  IsActive， 0   AS  IsPaid 
  F ROM  TblPackages 
  WHERE  Category =  @ packageType  
 
   -   改用SELECT ...  
 返回  @@ IDENTITY （）  -   或@@ SCOPE_IDENTITY  

广告2）

一般情况下 - 更新数据库，你需要使用 ExecuteNonQuery [ ^ ]方法。



在您的情况下，使用输出参数调用SP。有关详细信息，请参阅：

修改存储过程数据 [ ^ ]

检索身份或自动编号值 [ ^ ]

如何调用SQL Server存储过程在ASP.NET中使用Visual Basic .NET [ ^ ]

如何：执行返回行的存储过程 [ ^ ]

如何：执行返回单个值的存储过程 [ ^ ]

如何：执行不返回任何值的存储过程 [ ^ ]

Working on my Project, facing the error while inserting data into sql server using store procedure, but the error popups saying that System.Data.SqlClient.SqlException (0x80131904): Procedure or function uspSetSubscriptions has too many arguments specified

Create Proc uspSetSubscriptions
@packageType varchar(25),@UserID int,@ConstID int
AS
BEGIN
DECLARE @PackageId int,@notific int, @articles int, @MaxVideos int,@mxevent int,@mxallias int
SELECT @notific = MaxNotification FROM TblPackages WHERE Category = @packageType
SELECT @articles = MaxVideos FROM TblPackages WHERE Category = @packageType
SELECT @MaxVideos = MaxArticles FROM TblPackages WHERE Category = @packageType
SELECT @mxevent = MaxEvent FROM TblPackages WHERE Category = @packageType
SELECT @mxallias = MaxAllias FROM TblPackages WHERE Category = @packageType
SELECT @PackageId = PackageID FROM TblPackages WHERE Category = @packageType 
INSERT INTO TblSubscription (UserId,PackageId,ConstID,StartDate,FinishDate,MaxNotification,MaxArticles,MaxVideos,MaxEvent,MaxAllias,IsActive,IsPaid)
VALUES(@UserID,@PackageId,@ConstID,GetDate(),GETDATE()+30,@notific,@articles,@MaxVideos,@mxevent,@mxallias,0,0) SELECT @@IDENTITY
SELECT SCOPE_IDENTITY() -- Subscription id 
END

protected void BtnSubscribe_Click(object sender, EventArgs e)
    {
        Response.Write(AppUserID); //13
        Response.Write(Convert.ToString(ViewState["packtype"])); //Basic
        Response.Write(DDConstituency.SelectedValue); //1
        
        string PackgType = Convert.ToString(ViewState["packtype"]);

        try
        {
            db1.sqlcmd = new SqlCommand("uspSetSubscriptions");
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                db1.sqlcmd.CommandType = CommandType.StoredProcedure;
                db1.sqlcmd.Parameters.AddWithValue("@UserID", AppUserID);
                db1.sqlcmd.Parameters.AddWithValue("@packageType",Convert.ToString( ViewState["packtype"]));
                db1.sqlcmd.Parameters.AddWithValue("@ConstID", DDConstituency.SelectedValue);
                db1.sqlcmd.Connection = db1.sqlcon;
                db1.sqlcon.Open();
                Subscripid = Convert.ToInt32(db1.sqlcmd.ExecuteScalar());
                ViewState["Subscripid"] = Subscripid;
            }
        }
        catch (Exception ex) { Response.Write(ex); }
        finally { db1.sqlcon.Close(); }
    }

解决方案

why you have SqlDataAdapter? and you are not converting ConstID to integer value. you better have using blocks for SqlConnection and SqlCommand objects. don't share the connection, create new one when needed and dispose it when you done with it.

using (SqlConnection con = new SqlConnection(yourconnnectionString))
using(SqlCommand cmd =new SqlCommand("uspSetSubscriptions", con))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@UserID", Convert.ToInt32(AppUserID));
    cmd.Parameters.AddWithValue("@packageType",Convert.ToString( ViewState["packtype"]));
    cmd.Parameters.AddWithValue("@ConstID", Convert.ToInt32(DDConstituency.SelectedValue));
    con.Open();
    Subscripid = Convert.ToInt32(cmd.ExecuteScalar());
}

2 notes:
1) Why to use several variables, if you can use simple INSERT INTO + SELECT.
2) Code behind !


AD 1)
Try to replace the body of SP with this:

INSERT INTO TblSubscription (UserId, PackageId, ConstID, StartDate, FinishDate, MaxNotification, MaxArticles, MaxVideos, MaxEvent, MaxAllias, IsActive, IsPaid)
SELECT @UserID AS UserID, PackageID, @ConstID AS ConstID,GetDate() AS StartDate, GETDATE()+30 AS FinishDate, MaxNotification, MaxVideos, MaxArticles, MaxEvent, MaxAllias, PackageID, 0 AS IsActive, 0 AS IsPaid
FROM TblPackages
WHERE Category = @packageType

--use this instead SELECT ...
RETURN @@IDENTITY() --or @@SCOPE_IDENTITY

Ad 2)
In general - to update database, you need to use ExecuteNonQuery[^] method.

In your case, call SP with output parameter. For further information, please see:
Modifying Data with Stored Procedures[^]
Retrieving Identity or Autonumber Values[^]
How to call SQL Server stored procedures in ASP.NET by using Visual Basic .NET[^]
How to: Execute a Stored Procedure that Returns Rows[^]
How to: Execute a Stored Procedure that Returns a Single Value[^]
How to: Execute a Stored Procedure that Returns No Value[^]

这篇关于过程指定了太多参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！