Dominic Lennon
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.StoredProcedur e;
command.CommandText = "getQuestionsByTopic";
command.Parameters.AddWith Value("@qu oteId", topicId);
command.Parameters.AddWith Value("@to picId", topicId);
try { using(SqlDataAdapter adapt = new SqlDataAdapter(command)) adapt.Fill(dt); }
catch (Exception ex) { WritetoEventLog(ex.Message ); }
finally { conn.Close(); }
}
return dt;
}
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.StoredProcedur
command.CommandText = "getQuestionsByTopic";
command.Parameters.AddWith
command.Parameters.AddWith
try { using(SqlDataAdapter adapt = new SqlDataAdapter(command)) adapt.Fill(dt); }
catch (Exception ex) { WritetoEventLog(ex.Message
finally { conn.Close(); }
}
return dt;
}
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Jesus christ. How obvious was that! Thanks for the answer - no wonder I never got the results I was after.