how to pass an optional parameter to sql function from C#

Question

I have an issue. please help to solve my problem

I have a SQL function

function [dbo].[fnKudishikaAmt]
    (@ParishName nvarchar(100), @Hno int, @dateto datetime = Null)
Returns Decimal(15,2)

This function shows proper result by using the execute command

Select dbo.fnKudishikaAmt('St.George Malankara Catholic Church', 29, default)

My requirement is this function should be called from C#

I am getting the error

Conversion failed when converting datetime from character string

Code:

public double kudishikatotal(string ParishName, Int32 HouseNo)
{
    String SQLText = "select ChurchDB.dbo.fnKudishikaAmt(@ParishName, @Hno, @dateto) as fnresult";

    SqlCommand cmd = new SqlCommand(SQLText);
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("@ParishName", ParishName);
    cmd.Parameters.AddWithValue("@Hno", HouseNo);
    cmd.Parameters.AddWithValue("@dateto", "default");

    string rval = GetSingleValue(cmd);
    double kudiamt = 0;

    if (rval != null)
    {
        kudiamt = Convert.ToDouble(rval);
    }

    return kudiamt;
}

private static string GetSingleValue(SqlCommand cmd)
{
    string ConString = connectionstring();
    string returnvalue = "";

    using (SqlConnection con = new SqlConnection(ConString))
    {
        cmd.Connection = con;
        con.Open();
        returnvalue = cmd.ExecuteScalar().ToString();
        con.Close();
    }

    return returnvalue;
}

Don't pass that parameter at all. Don't even add it to the parameters collection — Comfortably Numb
– Comfortably Numb, Commented Sep 25, 2014 at 14:44
Same as stackoverflow.com/questions/8358315/… ... but you should show your function definition. Is that second parameter a nullable DateTime type? — Mashton
– Mashton, Commented Sep 25, 2014 at 14:49
if i didn't include the date parameter i get the error Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Must declare the scalar variable "@dateto". — Jinesh Sam
– Jinesh Sam, Commented Sep 25, 2014 at 14:59

Andrey Korneyev · Accepted Answer · 2014-09-25 14:44:35Z

3

If you've declared default value for parameter in stored procedure - then you can just not pass this parameter from c# code at all, and in this case it will have default value.

In your case exception thrown because it's impossible to convert string "default" to SqlDateTime which is your parameter type.

answered Sep 25, 2014 at 14:44

Andrey Korneyev

26.9k16 gold badges73 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jinesh Sam Over a year ago

if we are using stored procedure we can avoid the default parameter but in my case i am using function if i didn't include the date parameter i get the error Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Must declare the scalar variable "@dateto".

mrsrizan · Accepted Answer · 2014-09-25 14:47:20Z

0

YOu can use if condition while sending the datetime parameter.

if(some condition)
{
 cmd.Parameters.AddWithValue("@dateto", dateTimeValue);
}

Here datetimeValue is the value you want to pass. So you will be passing dateTimeValue only if required. The error is due to the string "default" you passed.

answered Sep 25, 2014 at 14:47

mrsrizan

3111 silver badge9 bronze badges

Collectives™ on Stack Overflow

how to pass an optional parameter to sql function from C#

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related