Need Help To solve error in code

[MJV57]

I get the following error: (Thanks)
Procedure or function GetBusinessDays has too many arguements specified

The code I use is as follows:

private void processCompleteDateTimePicker_ValueChanged(object sender, EventArgs e)
{
DateTime startdate;
DateTime enddate;
startdate = DateTime.Parse(processStartDateTimePicker.Text);
enddate = DateTime.Parse(processCompleteDateTimePicker.Text);


SqlConnection conn = new SqlConnection("Data Source=server1;Initial Catalog=ProjCoorTools;Integrated Security=True");
SqlCommand cmd = new SqlCommand("GetBusinessDays", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@StartDate", startdate);
cmd.Parameters.AddWithValue("@EndDate", enddate);
cmd.Parameters.AddWithValue("@BusinessDays", SqlDbType.Decimal).Direction = ParameterDirection.Output;

try
{
conn.Open();
cmd.ExecuteNonQuery();
proctimeTB.Text = (Convert.ToString(cmd.Parameters["@BusinessDays"].Value.ToString()));

}
catch (SqlException err)
{
MessageBox.Show(err.Message);
}
finally
{
if (conn.State == ConnectionState.Open)
conn.Close();
conn.Dispose();
}
}


The Function in sql is as follows:

USE [ProjCoorTools]
GO
/****** Object: UserDefinedFunction [dbo].[GetBusinessDays] Script Date: 12/11/2009 14:49:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[GetBusinessDays]
(
@StartDate datetime,
@EndDate datetime
)
returns int
as
begin

declare @DaysBetween int
declare @BusinessDays int
declare @Cnt int
declare @EvalDate datetime

select @DaysBetween = 0
select @BusinessDays = 0
select @Cnt=0

select @DaysBetween = datediff(Day,@StartDate,@endDate) + 1

while @Cnt < @DaysBetween
begin

select @EvalDate = @StartDate + @Cnt

if (dbo.IsDateAHoliday(@EvalDate) = 0)
BEGIN
if ((datepart(dw,@EvalDate) <> 1) and (datepart(dw,@EvalDate) <> 7))
BEGIN
select @BusinessDays = @BusinessDays + 1
END
END

select @Cnt = @Cnt + 1
end

return @BusinessDays
end

 

[jmeckley]

I don't think you can call a function directly like this. I could be wrong as I don't mess with procs anymore. if this does work you need to call ExecuteScalar

var cmd = new SqlCommand("GetBusinessDays", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@StartDate", startdate);
cmd.Parameters.AddWithValue("@EndDate", enddate);
var result = cmd.ExecuteScalar();
proctimeTB.Text = Convert.ToInt32(result);

if it doesn't then you need to create a proc to call the function.
btw. output parameters look like this

create proc foo
declare @in int,
declare @out varchar output
as 
select @out = cast(@in as varchar);
end

there are also alot of problems with your usage of ado.net objects. hard coded connection strings, instantiation (micro connection management), when to use try/catch/finally. check out my FAQ on one method to solve this problem. a link can be found in my signature.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[Kalisto]

cmd.Parameters.AddWithValue("@BusinessDays", SqlDbType.Decimal).Direction = ParameterDirection.Output;

I could be wrong, but dont you need to declare @BusinessDays as an Output Parameter in the sql ?