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;
            }
ASP.NETMicrosoft SQL Server

Avatar of undefined
Last Comment
Dominic Lennon
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

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
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.
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo