locked
EF error when returning from Stored Procedure in EF 6.1.2 RRS feed

  • Question

  • User-1188570427 posted

    When I am calling a stored procedure I am seeing this error:

    Additional information: The data reader is incompatible with the specified
    '.sp_MySp_Result'. A member of the type, 'Column1', does not have a
    corresponding column in the data reader with the same name.

    This stored procedure is doing everything with the updates and all, but fails when doing the return.  I am just doing updates and not returning any records or a value or anything (that I know of). I have tried to recreate the stored procedure in EF 2 or 3 times and it keeps bring in a random column 'Column1' in the complex type property.  Why is it doing that?

    I am passing in 2 true parameters and 1 output parameter, but every time 'column1' is being generated as a complex type.

    If the stored procedure ONLY does updates and returns a value if successful, how can I see if something else is coming back?

    Friday, April 14, 2017 3:30 AM

All replies

  • User753101303 posted

    Hi,

    Could it be an issue in how you are calling this stored procedure from your C# code? Showing how you call your SP might perhaps help.

    Not directly related but you could perhaps use the returned value as a "done" or "row not found" signal if this is the purpose of your explicit return value (an update returns the number of affected rows, you are using ExecuteSqlCommand ?).

    Or start with a SP that just does the update and absolutely nothing at all (check perhaps also if you have maybe  a trigger that could mess with that ???)

    Ah and you have SET NOCOUNT ON inside your SP ?

    Friday, April 14, 2017 9:16 AM
  • User-1188570427 posted

    Here is how I am calling my stored procedure:

                var outputParm = new ObjectParameter("myOutputErrorMain", typeof(string));
    
                Context.sp_Delete(codeToDelete, lastUpdatedById, outputParm).FirstOrDefault();
    
                if (outputParm.Value.ToString() != "true")
                {
                    return false;
                }

    Friday, April 14, 2017 11:18 AM
  • User753101303 posted

    This is database first and generated for you? What is returned by sp_Delete? It seems it does return a list rather than an int and it seems that when this code is generated for you it does find that your SP actually returns a result set ???

    Are you 100% sure your SP doesn't return any result set ???

    Friday, April 14, 2017 11:22 AM
  • User-1188570427 posted

    This is database first and generated for you? What is returned by sp_Delete? It seems it does return a list rather than an int and it seems that when this code is generated for you it does find that your SP actually returns a result set ???

    Are you 100% sure your SP doesn't return any result set ???

    YES!  it is returning a result set!  I am calling a another stored procedure within it and it is returning that information as well, even though I don't need that information.

    What is the best way to handle this?

    Friday, April 14, 2017 12:44 PM
  • User-1188570427 posted

    Basically something like this:

    http://stackoverflow.com/questions/571670/how-do-i-suppress-the-results-from-a-stored-procedure-from-within-a-stored-proce/571684#571684

    but how do I kill the result set from the inner stored procedure

    Friday, April 14, 2017 1:30 PM
  • User753101303 posted

    From the inner or from the outer procedure ?

    From the outer procedure you could try http://stackoverflow.com/questions/14622940/insert-into-table-exec-query-with-sql-server-2000 (ie you'll insert the resultset into a table variable so that it is not returned any more to the client side).

    Else you could change the inner procedure to take an additional @ReturnRows BIT=1 parameter so that you can skip returning a resultset if 0 is used.

    Friday, April 14, 2017 1:58 PM
  • User-1188570427 posted

    I just need to know if the outer return true or false or had errors. I don't care what the value(s) from the inner stored procedure are.

    Friday, April 14, 2017 5:27 PM
  • User-271186128 posted

    Hi tvb2727,

    If possible, I suggest you could post the related code in the Stored Procedure, it might be easier for us to find the real reason.

    Besides, I suggest you could refer to the following links to use Stored procedure:

    http://www.entityframeworktutorial.net/stored-procedure-in-entity-framework.aspx

    Best regards,
    Dillion

    Monday, April 17, 2017 7:01 AM