过程需要未提供错误的参数

本文关键字：错误 参数 过程 | 更新日期: 2023-09-27 18:34:37

我在数据库中存储了接受 4 个参数的过程。当我从 SQLDatasource 调用此过程并通过 sqldatasource 向导传递其参数时，出现以下错误

错误 [42000] [Microsoft][ODBC SQL Server 驱动程序][SQL Server]过程或函数"director_proc"需要参数"@Department"，但未提供。

下面是 SQLdataSource 标记和存储过程的代码

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 
SelectCommand="director_proc" SelectCommandType="StoredProcedure">
 <SelectParameters>
<asp:ControlParameter ControlID="ASPxComboBox1" DbType="String" 
DefaultValue="Enterprise solution" Name="Department" PropertyName="Value" 
Type="String" />
 <asp:ControlParameter ControlID="ASPxComboBox2" DbType="String" 
DefaultValue="Enterprise operations" Name="Section" PropertyName="Value" 
Type="String" />
<asp:ControlParameter ControlID="MonthEdit1" DbType="Int16" DefaultValue="7" 
Name="Month" PropertyName="Month" Type="Decimal" />
<asp:ControlParameter ControlID="ASPxComboBox3" DbType="Int16" 
 DefaultValue="2013" Name="Year" PropertyName="Value" Type="Decimal" />
 </SelectParameters>
</asp:SqlDataSource>

存储过程

    USE [AccessmgmtDB]
GO
/****** Object:  StoredProcedure [dbo].[director_proc]    Script Date: 10/08/2013 14:22:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[director_proc]
    -- Add the parameters for the stored procedure here
@Department nchar(50),
@Section nchar(50),
@Month numeric,
@Year numeric
AS
BEGIN
    if(@Section is null)
    SELECT SSNO ,Full_Name,152.5 [Working Hours],sum(diff) Actual_Hours,MONTH(date) as month,Year(date) Year
         ,(case when 152.5-sum([diff])<0 then 0 else 152.5-sum([diff])end) as [Missing Hours]
         ,(case when 152.5-sum([diff])<0 then 0 else (152.5-sum([diff]))/8.5 end) as [wanted days]
         ,section
         FROM attendance2
         where Department=@Department and MONTH ( date )=@Month and Year(date)=@Year
         group by SSNO,Full_Name,section,MONTH(date),Year(date) order by full_name;
         else
         SELECT SSNO ,Full_Name,152.5 [Working Hours],sum(diff) Actual_Hours,MONTH(date) as month,Year(date) Year
         ,(case when 152.5-sum([diff])<0 then 0 else 152.5-sum([diff])end) as [Missing Hours]
         ,(case when 152.5-sum([diff])<0 then 0 else (152.5-sum([diff]))/8.5 end) as [wanted days]
         ,section
         FROM attendance2
         where Department=@Department and MONTH ( date )=@Month and Year(date)=@Year and section=@Section
         group by SSNO,Full_Name,section,MONTH(date),Year(date) order by full_name;
END

过程需要未提供错误的参数

而不是：

        <asp:ControlParameter ControlID="ASPxComboBox1" DbType="String" DefaultValue="Enterprise solution" Name="Department" PropertyName="Value" Type="String" />

尝试：

        <asp:ControlParameter ControlID="ASPxComboBox1" DbType="String" DefaultValue="Enterprise solution" Name="Department" PropertyName="SelectedValue" Type="String" />

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.controlparameter.propertyname.aspx

我认为

问题是你绑定到DevExpress Combo Box。尝试在 ASPxComboBox 控件上设置AutoPostback="true"。此外，请确保 SqlDataSource 与 DevExpress 控件位于同一命名容器中...他们过去有过这个问题，但我多年来没有使用过它们，所以我不知道这是否仍然是一个问题。

尝试对组合框的控制参数使用 SelectedItem.Value 而不是值。

 <SelectParameters>
      <asp:ControlParameter ControlID="ASPxComboBox1" DbType="String" 
       DefaultValue="Enterprise solution" Name="Department"  
       PropertyName="SelectedItem.Value" 
       Type="String" />
    <asp:ControlParameter ControlID="ASPxComboBox2" DbType="String" 
       DefaultValue="Enterprise operations" Name="Section"     
       PropertyName="SelectedItem.Value" 
       Type="String" />
   <asp:ControlParameter ControlID="MonthEdit1" DbType="Int16" DefaultValue="7" 
       Name="Month" PropertyName="Month" Type="Decimal" />
   <asp:ControlParameter ControlID="ASPxComboBox3" DbType="Int16" 
       DefaultValue="2013" Name="Year" PropertyName="SelectedItem.Value" Type="Decimal" />
    </SelectParameters>

伙计们的好消息是我成功地解决了这个问题。我正在使用 ODBC 连接，似乎在通过 ODBC 传递参数时发生错误 connection.so 我删除了 ODBC 并将其替换为常规 SQL 连接，一切正常。

谢谢大家的帮助。

相关文章：