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

Dominic Lennon
Dominic Lennon used Ask the Experts™
on
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;
            }
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
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.
be sure with the parameters you are passing, i see in your code you are passing topicid in both the parameters

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial