CODEWITHSUNDEEP
c#asp.netentity-framework-4
Developer
Developer

Reputation: 8636

Visual Studio – EntityFramework -The Selected Store Procedure Returns no columns

Hi all I have written my query as follows in sql which gives me a result, this is my query

SELECT Technology,TechDescription, Technology.TechID, COUNT(Question) AS 
  'Totalposts' FROM Technology LEFT JOIN Question ON Question.TechID = Technology.TechID    GROUP BY Technology.TechID, Technology,TechDescription

enter image description here

I just write it in to a stored procedure as follows

USE [newForumDB]
GO
/****** Object:  StoredProcedure [dbo].[selectTechQuestions]    Script Date: 01/24/2013 15:06:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[selectTechQuestions]
 As
  SET FMTONLY OFF
Begin
  SELECT Technology,TechDescription, Technology.TechID, COUNT(Question) AS 
  'Totalposts'

FROM Technology

LEFT JOIN Questions

ON Questions.TechID = Technologies.TechID

GROUP BY Technologies.TechID, Technology,TechDescription
End

and added this in to my model, and tried to add an function for the procedure, but I am getting the message as The selected stored procedure returns no columns can some one help me

enter image description here

As per Habib requested I tried in both ways i.e 

1) writing the query in a string and filled the DataSet using SqlCommand and SqlDataAdapter which works fine

2) SqlCommand cmd = new SqlCommand();
cmd.CommandText = "selectTechQuestions";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds);

The second one gives me the error {"Invalid object name 'Questions'."}

Upvotes: 0

Views: 663

Answers (1)

Sagar Upadhyay
Sagar Upadhyay

Reputation: 819

Put your as 

SELECT Technology AS 'Technology',TechDescription AS 'TechDescription', Technology.TechID AS 'ID', COUNT(Question) AS 
'Totalposts'

FROM Technology

LEFT JOIN Questions

ON Questions.TechID = Technologies.TechID

GROUP BY Technologies.TechID, Technology,TechDescription

So, many time before when I get this type of prob, at that time I had done this. This is silly, solution but I had come out with my prob, may be It can be help full to you....




Detail description
As all we know select statement return result as single data-set, even if its from multiple table i.e query like inner join.

When a data comes form the multiple tables in there is a possibility to over come two different column from different table with same name.

This clause does not made any problem in simple data fetching style i.e as we simply done with DataAdapter and SqlCommand, but entity framework can't handle this thing, and there fore compiler does not allow query such like which containing inner join or multiple table queries.

So to resolve this problem we just have to assign a different name fore each column as I had done here and in this way there will be not at all any problem can cause...

Upvotes: 1

Related Questions