Click here to Skip to main content
15,894,405 members
Please Sign up or sign in to vote.
3.00/5 (2 votes)
See more:
Hi..
I have an issue. please help to solve my problem

I have an sql function

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


the sql function shows proper result by using the execute command
SQL
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

C#
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;
        
    }


C#
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;
    }
Posted
Comments
[no name] 25-Sep-14 10:35am    
What sort of DateTime would you expect the string "default" to produce?
jinesh sam 25-Sep-14 10:49am    
Select dbo.fnKudishikaAmt('St.George Malankara Catholic Church',29,default)

i used this code to execute sql function in the back end
jinesh sam 25-Sep-14 10:52am    
its normal "datetime"
jinesh sam 25-Sep-14 10:54am    
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".

1 solution

Replace "default" with DBNull.Value (no qoutes!!)
 
Share this answer
 
Comments
jinesh sam 25-Sep-14 11:16am    
Thanks its works.. Cheers...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900