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;
}