The data reader has more than one field error while calling a procedure that returns an integer
Database.SqlQuery<T>()
expects some kind of result set (e.g. SELECT
). Under the hood, it uses DbCommand.ExecuteReader()
, and when T
is scalar, it expects the result set to have exactly one field -- but if the result set has more than one field, or if there are no fields, it throws the exception that you encountered.
The return value can be retrieved by passing a DbParameter
to Database.SqlQuery<T>()
and setting Direction = ParameterDirection.ReturnValue
as seen in these examples:
- How can I get return value of stored procedure when using SqlDataReader?
- http://blogs.msdn.com/b/diego/archive/2012/01/10/how-to-execute-stored-procedures-sqlquery-in-the-dbcontext-api.aspx
FYI, if you just want the return value, but you don't want a result set, use ExecuteSqlCommand
with a DbParameter
.
Comments
-
Jyrkka almost 2 years
I was trying to get status code of a stored procedure execution when encountered this error:
An exception of type 'System.Data.Entity.Core.EntityCommandExecutionException' occurred in EntityFramework.SqlServer.dll but was not handled in user code
Additional information: The data reader has more than one field. Multiple fields are not valid for EDM primitive or enumeration types.
I have rewritten the procedure to do absolutely nothing but to return an integer value, this is how it looks now:
ALTER Procedure [dbo].[test] ( @i int, @a nvarchar(max), @b nvarchar(max), @c nvarchar(max), @d nvarchar(max) ) As Begin SET NOCOUNT ON return 1 End
But I still get the same error at run-time when reaching the procedure call at line:
this.Database.SqlQuery<int>("test @i, @a, @b, @c, @d", p_i, p_a, p_b, p_c, p_d).FirstOrDefault();
Is there anyway to figure out what these fields are, and where they are coming from? And how should I get the returned value?
I've tried to specify a tuple of two strings as the
T
just to look into these values, but with no success...Updates:
Select 1
instead ofreturn 1
makes the function usable, the only question remains what are these mysterious fields that are returned to the data reader? -
Scharly Ochoa over 8 yearswhen are you getting that error on compile or runtime ? i think it should work. Also try "exec [dbo].[test] {0}, {1}, {2}, {3}, {4}"
-
Jyrkka over 8 yearsHave tried also the second one, the error remained the same, I'm getting it at runtime.
-
Jyrkka over 8 yearsWell, now the exception is different
Error converting data type nvarchar to int.
-
Ramesh Babu over 8 yearsthe statement is right. so check the datatype of the parameters within the stored procedure and the values you are passing to them.
-
Jyrkka over 8 yearsI can run the query from the SSMS, thus both the procedure and the values are usable. The only thing I can think of is that you need to put quotes around {1-4}, since @a, @b, @c and @d are nvarchars...
-
Jyrkka over 8 yearsThank you for the explanation about the behind the scenes stuff, but I didn't get the point, the procedure returns exactly one value, I'm not sure that it can be thought of as a field though, but the message says that the reader has more than one field...
-
jjj over 8 years@Jyrkka: What I mean to say is that when using
SqlQuery
, it counts fields in the result set, soSELECT x, y, FROM table
would have two fields. WhenT
is a scalar, it expects one field, but if there are no fields, it looks like it gives the same error as when there are more than one.