EntityFramework Procedure or function '' expects parameter '', which was not supplied

Question

I apologise for asking just a basic question, however I cannot find the cause of this error.

I am using Entity Framework to execute a Stored Procedure, and I am passing in four parameters, however the SQL Database seems to reject them. Can anyone point me in the right direction?

My code:

ObjectResult<SearchDirectoryItem> resultList = container.ExecuteStoreQuery<SearchDirectoryItem>("SearchDirectoryEntries",
            new SqlParameter("@DirectoryId", search.DirectoryId),
            new SqlParameter("@Latitude", point.Latitude),
            new SqlParameter("@Longitude", point.Longitude),
            new SqlParameter("@Range", search.RangeMiles));

Which produces the error:

Procedure or function 'SearchDirectoryEntries' expects parameter '@DirectoryId', which was not supplied.

The SQL generated is:

exec sp_executesql N'SearchDirectoryEntries',N'@DirectoryId int,@Latitude decimal(7,5),@Longitude decimal(6,5),@Range int',@DirectoryId=3,@Latitude=53.36993,@Longitude=-2.37013,@Range=10

The stored procedures is:

ALTER PROCEDURE [dbo].[SearchDirectoryEntries]
@DirectoryId int,
@Latitude decimal(18, 6),
@Longitude decimal(18, 6),
@Range int

Many Thanks.

Can you post the declaration of your SQL stored procedure? Up to where the parameters are defined? — Andy Shellam
ALTER PROCEDURE [dbo].[SearchDirectoryEntries] @DirectoryId int, @Latitude decimal(18, 6), @Longitude decimal(18, 6), @Range int Cheers — James
What are the definitions of the search and point? i.e. Is search.DirectoryId an int? — ChrisF
None of the values are null, they are all passed to SQL: exec sp_executesql N'SearchDirectoryEntries',N'@DirectoryId int,@Latitude decimal(7,5),@Longitude decimal(6,5),@Range int',@DirectoryId=3,@Latitude=53.36993,@Longitude=-2.37013,@Range=10 — James

Kamarey Kamarey · Accepted Answer · 2011-06-10T10:25:06

The commandText param in your query is incorrect. It should be a call to a stored procedure with parameters instead of just stored procedure name:

ObjectResult<SearchDirectoryItem> resultList = container.ExecuteStoreQuery<SearchDirectoryItem>(
    "Exec SearchDirectoryEntries @DirectoryId, @Latitude, @Longitude, @Range",
     new SqlParameter("DirectoryId", search.DirectoryId),
     new SqlParameter("Latitude", point.Latitude),
     new SqlParameter("Longitude", point.Longitude),
     new SqlParameter("Range", search.RangeMiles));

Also don't forget to remove '@' from SqlParameter constructor.

EntityFramework Procedure or function '' expects parameter '', which was not supplied

1 Answers