MS SQL function not returning value to C# [duplicate]

Question

I've got the following MS SQL function:

CREATE FUNCTION [dbo].[fn_NumApplications] ()
RETURNS int
AS
BEGIN
DECLARE @numRecords int = 0
SELECT @numRecords = COUNT(A.id) FROM Applications A
RETURN @numRecords
END

and the following C# code to call the function:

using (SqlConnection conn = new SqlConnection(mydbconnstring))
{
    using (SqlCommand cmd = new SqlCommand("dbo.fn_numApplications", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        //execute
        conn.Open();
        string retVal = cmd.ExecuteScalar().ToString();
        conn.Close();
    }
}

When the function gets to cmd.ExecuteScalar(), I am getting a NullReferenceException.

I have tried calling the function with, and without "dbo.". I have also tried calling it using cmd.ExecuteReader() without success.

What's going wrong here?

Not only do you need the parenthesis and the SELECT keyword, but if you have a case sensitive collation this will throw an exception because the name of the function does not have a capital N in your c# code. Of course I would suggest making this into an inline table valued function instead. — Sean Lange
– Sean Lange, Commented Jul 8, 2016 at 20:05
You're missing the SELECT statement in front of the dbo.MyFunction. You should run the code in SSMS first......to learn the syntax — granadaCoder
– granadaCoder, Commented Jul 8, 2016 at 20:26

Navid K · Accepted Answer · 2016-07-08 20:29:17Z

0

Try this:

SqlCommand cmd = new SqlCommand("Select dbo.fn_numApplications()", conn)

answered Jul 8, 2016 at 20:29

Navid K

901 silver badge7 bronze badges

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

Collectives™ on Stack Overflow

MS SQL function not returning value to C# [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related