Mamado Jozif Mamado Jozif - 1 month ago 8
ASP.NET (C#) Question

Procedure expects parameter which was not Supplied error

I have stored procedure in DB that accepted 4 parameters.when i call this procedure from SQLDatasource and pass its parameters through sqldatasource wizard i get the following error


ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure or function 'director_proc' expects parameter '@Department', which was not supplied.


below is code for sqldatasource tags and stored procedure

<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>


stored procedure

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

Answer Source

guys good news is i successfully solved the problem. i was using odbc connection and seems that error occur when passing the parameters through ODBC connection.so i remove the odbc and replace it with regular sql connection and everything goes fine.

thanks all for your help.