Error from union query

I am trying to list all schedules and then if there are services list those also, but I can't seem to get it to work.

I get the error...The query results cannot be enumerated more than once.

What is wrong with my Union?

Thanks



SELECT ISNULL(dbo.Schedules.ServiceID, 0) AS ServiceID, dbo.Schedules.SchedStartDateTime, dbo.Schedules.ServiceText, dbo.Schedules.CustomerID, 
                      ISNULL(dbo.Schedules.Completed, 0) AS Completed, dbo.Stylists.FullName, dbo.Stylists.StylistID, 'No Title' AS BeforePicName, 
                      '../images2020/resume-photo.jpg' AS BeforePicPath, 'No Title' AS AfterPicName, '../images2020/resume-photo.jpg' AS AfterPicPath
FROM         dbo.Schedules INNER JOIN
                      dbo.Stylists ON dbo.Schedules.UserID = dbo.Stylists.UserID

Where dbo.Schedules.CustomerID = 27
UNION 

SELECT ISNULL(dbo.Services.ServiceID, 0) AS ServiceID, dbo.Schedules.SchedStartDateTime, dbo.Schedules.ServiceText, dbo.Schedules.CustomerID, 
                      ISNULL(dbo.Services.Completed, 0) AS Completed, dbo.Stylists.FullName, dbo.Stylists.StylistID, ISNULL(dbo.Services.BeforePicName, 'No Title') AS BeforePicName, 
                      ISNULL(dbo.Services.BeforePicPath, '../images2020/resume-photo.jpg') AS BeforePicPath, ISNULL(dbo.Services.AfterPicName, 'No Title') AS AfterPicName, 
                      ISNULL(dbo.Services.AfterPicPath, '../images2020/resume-photo.jpg') AS AfterPicPath
FROM         dbo.Schedules INNER JOIN
                      dbo.Stylists ON dbo.Schedules.UserID = dbo.Stylists.UserID INNER JOIN
                      dbo.Services ON dbo.Schedules.ServiceID = dbo.Services.ServiceID

Where dbo.Schedules.CustomerID = 27
ORDER BY dbo.Schedules.SchedStartDateTime DESC

Open in new window

SheritlwAsked:
Who is Participating?
 
SheritlwAuthor Commented:
Ok, I figured it out.  
In my linq code...
        Dim q = dc.stp_Select_ClientStyles(iCustID).ToList
I didn't have .tolist.  Needed that in order to bind to listview.

Thanks for response.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Howdy....

My suspicion is the ORDER BY clause.  Change it to "ORDER BY 2" or "ORDER BY SchedStartDateTime"



Kent
0
 
SheritlwAuthor Commented:
?? The order by shouldn't have anything to do with it. I have used Desc in multiple stored procedures without any problems.
Any other ideas?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
ThomasianCommented:
Have you tried running the query in SSMS?

I think the problem is on LINQ rather than on the SQL.
0
 
SheritlwAuthor Commented:
Yes, It runs fine in ssms.  I also agree that it is in Linq, but what do I need to change to get it to work?

Thanks
0
 
ThomasianCommented:
Sorry, I'm not familiar with LINQ. You might want to repost the question in the LINQ section or call the attention of a moderator to change the topic of this question.
0
 
SheritlwAuthor Commented:
Found and posted solution
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.