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, Aug 17 '13 at 12:36

rene · Answer 1 · 2013-08-17T18:35:50.303

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

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 — Shaghayegh, Aug 17 '13 at 13:10
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, Aug 17 '13 at 13:15
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` — rene, Aug 17 '13 at 13:25
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, Aug 17 '13 at 16:28
It done bye this code : SqlCommand Cmd = new SqlCommand("Select dbo.dore1(@First, @Second)", cnn); — Shaghayegh, Aug 17 '13 at 16:41

score 1 · Answer 2 · answered Aug 17 '13 at 12:37

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 '13 at 12:37

Gats

3,452
19
20

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 – Shaghayegh Aug 17 '13 at 13:07
Yes, but it is RETURNING a value which is different. Execute scalar expects SELECT with a single row/single column result. – Gats Aug 19 '13 at 02:17

score 1 · Answer 3 · answered Aug 17 '13 at 12:39

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 '13 at 12:39

Blank Overtone

23
6

it works with Char in SQL (our date is diffrent from Gerogrian date) when I run my Function there – Shaghayegh Aug 17 '13 at 13:04
my parameter is not DATE . it is char and it works with char in sql – Shaghayegh Aug 17 '13 at 13:06

score 0 · Answer 4 · answered Aug 17 '13 at 21:20

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 '13 at 21:20

Shaghayegh

1
1

SQL function returns Wrong in c#

4 Answers4