We help IT Professionals succeed at work.
Get Started

Why DataTable is null (Stored Procedure)

brad_lub
brad_lub asked
on
187 Views
Last Modified: 2012-06-27
I am calling the method 'IFRecentPosts(int UserID, int SOITaskID) [see code below]  which executes the SP 'IF_RecentPosts' [see SP details below].

I am able to run the query and get the appropriate results from SSMS after passing in actual values in place of the variables; however, when I execute the IFRecentPosts(int UserID, int SOITaskID) method the DataTable is empty. When I debug the method, the the variables are receiving the correct values; so the issue must be at the SP level.

Couple questions:
(1)Any thoughts as to why the results are null?
(2) Is there a way to debug the SP and see why it is not returning a result?

Thanks,

Brad

+++++++++++++++++++++++++++++++++
 public static DataTable IFRecentPosts(int UserID, int SOITaskID)
        {
            DataTable dt = new DataTable();

            using (SqlConnection conn = new SqlConnection(AuditData.DataContext.Connection.ConnectionString))
            {
                using (SqlDataAdapter da = new SqlDataAdapter("IF_RecentPosts", conn))
                {
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    da.SelectCommand.Parameters.AddWithValue("@UserID", UserID);
                    da.SelectCommand.Parameters.AddWithValue("@SOITaskID", SOITaskID);

                    da.Fill(dt);
                }
            }

            return dt;
        }

Open in new window

++++++++++++++++++++++++++++++++++++++++

ALTER procedure [dbo].[IF_RecentPosts]  @UserID int, @SOITaskID int as



SELECT     post.ID, 'PP-' + postType.[Description] AS [Type], task.TaskNumber, post.[Subject], u.FirstName, u.LastName, u.TitleDesc AS Title,
                          (SELECT     COUNT(*)
                            FROM          ProgressPosts
                            WHERE      ThreadID = post.ThreadID AND ID != ThreadID) AS Replies, NULL AS Satisfied,
                          (SELECT     TOP 1 DateCreated
                            FROM          ProgressPosts
                            WHERE      ParentProgressPostID = post.ID OR
                                                   ID = post.ID
                            ORDER BY DateCreated DESC) AS [Date], task.RecommendationID, task.ID AS SOITaskID, post.ThreadID, post.TeamPost, u.ID as UserID
FROM         ProgressPosts post INNER JOIN
                      ProgressPostTypes postType ON postType.ID = post.ProgressPostTypeID INNER JOIN
                      SOITasks task ON task.ID = post.SOITaskID INNER JOIN
                      Users u ON u.ID = post.UserID
WHERE     post.ParentProgressPostID IS NULL 
			and task.ID = @SOITaskID 
			--When It is Not a team post and the user that has made the post = @UserId then return the record
			--When is is a team post then return it, in any other case do not return the record
			AND (CASE WHEN post.TeamPost = 0 AND u.ID = @UserID THEN 1
				 WHEN post.TeamPost = 1 THEN 1
				 ELSE  0 
					END) = 1
UNION
SELECT     post.ID, 'Feedback Request' AS [Type], task.TaskNumber, post.[Subject], u.FirstName, u.LastName, u.TitleDesc AS Title,
                          (SELECT     COUNT(*)
                            FROM          FeedbackRequests
                            WHERE      ThreadID = post.ThreadID AND ID != ThreadID) AS Replies, post.HasAnswer AS Satisfied,
                          (SELECT     TOP 1 DateCreated
                            FROM          FeedbackRequests
                            WHERE      ParentFeedbackRequestID = post.ID OR
                                                   ID = post.ID
                            ORDER BY DateCreated DESC) AS [Date], task.RecommendationID, task.ID AS SOITaskID, post.ThreadID, (CASE WHEN post.UserID > 0 THEN 1 ELSE 0 END), u.ID as UserID
FROM         FeedbackRequests post INNER JOIN
                      SOITasks task ON task.ID = post.SOITaskID INNER JOIN
                      Users u ON u.ID = post.UserID
WHERE     post.ParentFeedbackRequestID IS NULL and task.ID = @SOITaskID
order by [Date] DESC

Open in new window

Comment
Watch Question
CERTIFIED EXPERT
Top Expert 2011
Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE