The data reader is incompatible with the specified, ...does not have a corresponding column in the data reader with the same name

Question

I have a problem with my working project (Using ADO.net Entity Framework) . My database has 2 tables:

https://i.stack.imgur.com/y3NzM.png

Here is my stored procedure:

CREATE PROCEDURE [dbo].[SP_SELECT]
AS
BEGIN
    SELECT I.Id, I.Name, C.Name
    FROM ITEM I, CATEGORY C
    WHERE I.CategoryID = C.Id
END

I try to run this stored procedure and I get a message error show that: "The data reader is incompatible with the specified 'TEST2Model.SP_SELECT_Result'. A member of the type, 'Name1', does not have a corresponding column in the data reader with the same name."

I do think it has a problem between the attribute "Name" in ITEM and "Name" in CATEGORY. Please help, thanks!

Solution 1

You need to give each column a unique name so that the DataReader can identify each column when you use Item[]. In this example, I changed the name of the second and third column to "ItemName" and "CategoryName" respectively.

CREATE PROCEDURE [dbo].[SP_SELECT]
AS
BEGIN
    SELECT I.Id, I.Name ItemName, C.Name CategoryName
    FROM ITEM I, CATEGORY C
    WHERE I.CategoryID = C.Id
END

Solution 2

The data reader is incompatible with the specified 'TEST2Model.SP_SELECT_Result'. A member of the type, 'Name1', does not have a corresponding column in the data reader with the same name.

That error is pretty clear. Most like you have a class named SP_SELECT_Result and at one point your stored procedure was returning a result with the column Name1 in it and therefore that class was generated. You have then modified your stored procedure buy you have not updated that code. Therefore, during reading the result of the stored procedure, the DataReader is trying to set the Name1 property of SP_SELECT_Result and it cannot find it because your stored procedure does not have that in its result anymore.

Also now you have 2 columns with the same name: Name in your result. This is not going to work.

How to fix?

Change your stored procedure to return columns with unique names. Also, right click your stored procedure in your model in Visual Studio and select Update From Database so it can update the SP_SELECT_Result with your latest stored procedure changes.

Comments

[Kircali]

Thanks for your explain and solution. It worked for me. I give an alias to my column. My column ise "ACTION_NAME" and i changed my query to "Select ACTION_NAME ActionName...". Because property name of class is "ActionName". Thanks again.

[Chitra Nandpal]

so what i need to do in that case?

[GutierrezDev]

@ChitraNandpal Make sure that your return the expected objects. Even if you make validations try to return the expected result. If EF expect an Int make sure you return an int in the stored procedure.