Link to home
Start Free TrialLog in
Avatar of Dominic Lennon
Dominic LennonFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Subquery results in SP getting lost when called in .Net App

Hi,

I've written a Stored Procedure below:-

CREATE PROCEDURE [dbo].[getQuestionsByTopic]
      -- Add the parameters for the stored procedure here
      @topicId int,
      @quoteId int
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for procedure here
      SELECT q.*, a.questionAnswer, a.answerId FROM question q
      LEFT JOIN (SELECT questionId, questionAnswer, quoteId, Id AS answerId FROM questionAnswer WHERE quoteId = @quoteId) AS a ON q.questionId = a.questionId
      WHERE q.topicId = @topicId;
END

When ran from SQL Management Studio, it returns what I want - all questions and any answers based on Quote ID. However, when I call it in my .Net app, the fields specifically in the subquery never appear. Any ideas? Here's my code which grabs a dataset anyway:-

public DataTable getQuestionsByTopic (int topicId, int quoteId) {
                  DataTable dt = new DataTable();
                  SqlConnection conn = new SqlConnection();
                  
                  Open(conn);
                        using (SqlCommand command = new SqlCommand()) {
                              command.Connection = conn;
                              command.CommandType = CommandType.StoredProcedure;
                              command.CommandText = "getQuestionsByTopic";
                    command.Parameters.AddWithValue("@quoteId", topicId);
                    command.Parameters.AddWithValue("@topicId", topicId);

                              try { using(SqlDataAdapter adapt = new SqlDataAdapter(command)) adapt.Fill(dt); }
                              catch (Exception ex) { WritetoEventLog(ex.Message); }
                              finally { conn.Close(); }
                        }
                  
                  return dt;
            }
Avatar of lcohan
lcohan
Flag of Canada image

I suggest you replace SELECT q.* with the actual column list and to be very specific you could even cast each to the data type expected by the .NET page code.
ASKER CERTIFIED SOLUTION
Avatar of BuggyCoder
BuggyCoder
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Dominic Lennon

ASKER

Jesus christ. How obvious was that! Thanks for the answer - no wonder I never got the results I was after.