SQL function returns Wrong in c#

Question

I have a function in SQL which returns right answer when execute in SQL Managment this is my Function

ALTER FUNCTION [dbo].[Dore1] 
    (@First NVARCHAR(50), @second NVARCHAR(50))
RETURNS INT
AS
BEGIN
    DECLARE @Tedad INT

    SET @tedad = (SELECT COUNT(sh_gharardad)
                  FROM OrgGharardad
                  WHERE OrgGhAghdDate BETWEEN @First
                  AND @second)

    RETURN @Tedad
END

but when I use it in my c# program, it returns 0 (zero) value in this Code. where did I write wrong ?

int dore1, dore2;
        ConnectDb Contact = new ConnectDb();
        Contact.connect();
        SqlCommand Cmd = new SqlCommand("dore1", cnn);
        SqlCommand Cmd2 = new SqlCommand("dore2", cnn);
        Cmd.CommandType = CommandType.StoredProcedure;
        Cmd2.CommandType = CommandType.StoredProcedure;
        cnn.Open();

        Cmd.Parameters.AddWithValue("@First", txt_1_aghaz.Text);
        Cmd.Parameters.AddWithValue("@Second", txt_1_payan.Text);

        dore1=Convert.ToInt32( Cmd.ExecuteScalar());



        Cmd2.Parameters.AddWithValue("@First2", txt_2_aghaz.Text);
        Cmd2.Parameters.AddWithValue("@second2", txt_2_payan.Text);

        dore2= Convert.ToInt32( Cmd2.ExecuteScalar());

        CreateChart(dore1, dore2);
        cnn.Close();

Check that the values coming from your text-boxes are passed correctly and are convertible to datetime data type. e.g. 2013-08-17 11:40:59.0 — Farzan
– Farzan, Commented Aug 17, 2013 at 12:36

rene · Accepted Answer · 2013-08-17 18:35:50Z

2

As you are calling a function you have to cater for its return value.

c# code for dore1

    int dore1;
    ConnectDb Contact = new ConnectDb();
    Contact.connect();
    SqlCommand Cmd = new SqlCommand("dore1", cnn);
    Cmd.CommandType = CommandType.StoredProcedure;
    cnn.Open();

    Cmd.Parameters.AddWithValue("@First", txt_1_aghaz.Text);
    Cmd.Parameters.AddWithValue("@Second", txt_1_payan.Text);

    // setup out parm
    var outParm =  Cmd.Parameters.Add("@Tedad");
    outParm.Direction = ParameterDirection.ReturnValue;

    Cmd.ExecuteScalar();
    // this is safe as long as select count() returns a number
    dore1 = (Int32) outParm.Value;

T-SQL Function

You have a specific date format, the one that resembles the closest is the japan format yyyy/mm/dd which is code 111. However 1392 is somewhat low as a year. Are those values in your table? The sql datetime format doesn't handle dates prior to 1753. If no dates are stored you can remove the convert statement and revert to your original tsql function.

ALTER FUNCTION [dbo].[Dore1] 
    (@First NVARCHAR(50), @second NVARCHAR(50))
RETURNS INT
AS
BEGIN
    DECLARE @Tedad INT

    SET @Tedad = (SELECT COUNT(sh_gharardad)
                  FROM OrgGharardad
                  WHERE OrgGhAghdDate 
                      BETWEEN CONVERT(datetime, @First , 111)
                      AND CONVERT(datetime, @second, 111))

    RETURN @Tedad
END

edited Aug 17, 2013 at 18:35

answered Aug 17, 2013 at 12:36

rene

42.6k78 gold badges122 silver badges166 bronze badges

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

5 Comments

Shaghayegh Over a year ago

My Function is Correct and runs in SQL Managment. I think there is a mistake in c# . and I tested ur Suggestion but it didn't work

rene Over a year ago

I'm not claiming that your function is not working in SMSS, I'm stating that your stored proc doesn't work with the c#.

rene Over a year ago

Can you show how you invoke dore1 from ssms and can you tell what values you have in txt_1_aghaz.Text and txt_1_payan.Text

Shaghayegh Over a year ago

1392/01/09 and 1392/02/30 there is my value . and when I write Select @tedad my Function will not create and shows error of SELECT

Shaghayegh Over a year ago

It done bye this code : SqlCommand Cmd = new SqlCommand("Select dbo.dore1(@First, @Second)", cnn);

Gats · Accepted Answer · 2013-08-17 12:37:40Z

1

ExecuteScalar is expecting a 1 column, 1 row result set which is not what a function returns. What you are actually getting in the scalar result is the COUNT value of the affected rows.

Check out this about the differences between procs and functions http://www.codeproject.com/Tips/286539/Difference-between-stored-procedure-and-function

And you need to do something to SELECT the result of the function for execute scalar to work.

answered Aug 17, 2013 at 12:37

Gats

3,46221 silver badges21 bronze badges

2 Comments

Shaghayegh Over a year ago

you mean in my function ? My Function works in SQL Managment and returns right value . but in this line dore1=Convert.ToInt32( Cmd.ExecuteScalar()); instead of returning value 2 (for Example) it returns 0

Gats Over a year ago

Yes, but it is RETURNING a value which is different. Execute scalar expects SELECT with a single row/single column result.

Blank Overtone · Accepted Answer · 2013-08-17 12:39:59Z

1

Since you are working with dates. Make sure you convert them before sending them to SQL.

Convert.ToDateTime(txt_1_aghaz.Text.ToString())

and make sure you are receiving Dates in your SQL statement instead of nvarchar(50).

answered Aug 17, 2013 at 12:39

Blank Overtone

236 bronze badges

2 Comments

Shaghayegh Over a year ago

it works with Char in SQL (our date is diffrent from Gerogrian date) when I run my Function there

Shaghayegh Over a year ago

my parameter is not DATE . it is char and it works with char in sql

Shaghayegh · Accepted Answer · 2013-08-17 21:20:24Z

0

it Done by this change : SqlCommand Cmd = new SqlCommand("Select dbo.dore1(@First, @Second)", cnn);

we cant call a function Directly and it is not Stored Procedure

DONE

answered Aug 17, 2013 at 21:20

Shaghayegh

11 bronze badge

Collectives™ on Stack Overflow

SQL function returns Wrong in c#

4 Answers 4

5 Comments

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related