• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 753
  • Last Modified:

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

0
Sheritlw
Asked:
Sheritlw
  • 4
  • 2
2 Solutions
 
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
 
ThomasianCommented:
Have you tried running the query in SSMS?

I think the problem is on LINQ rather than on the SQL.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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:
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
 
SheritlwAuthor Commented:
Found and posted solution
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now