Error from union query

Posted on 2012-09-07
Last Modified: 2012-09-12
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?


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

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

Question by:Sheritlw
    LVL 45

    Expert Comment


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


    Author Comment

    ?? 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?
    LVL 22

    Expert Comment

    Have you tried running the query in SSMS?

    I think the problem is on LINQ rather than on the SQL.

    Author Comment

    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?

    LVL 22

    Assisted Solution

    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.

    Accepted Solution

    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.

    Author Closing Comment

    Found and posted solution

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    This video discusses moving either the default database or any database to a new volume.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now