Incorrect syntax near the keyword 'SELECT'

Question

I have an Classic ASP page that lets users search for properties and a results page that then emails the property agents who have matching properties to let them know that there are tenants interested in their properties.

Some agents may have more than 1 property, in which case the agent only needs to receive 1 email rather than an email for each property they have.

I have tested the following query in Management Studio and it returns the correct results;

SELECT DISTINCT CustomerEmail
FROM         (SELECT     ContentID
                       FROM          (SELECT     ContentID
                                               FROM          VWTenantPropertiesResults
                                               WHERE      (ContentStreet = '')
                                               UNION ALL
                                               SELECT     ContentID
                                               FROM         VWTenantPropertiesResults AS VWTenantPropertiesResults_2
                                               WHERE     (ContentTown = 'Hull')
                                               UNION ALL
                                               SELECT     ContentID
                                               FROM         VWTenantPropertiesResults AS VWTenantPropertiesResults_1
                                               WHERE     (ContentPostCode = 'HU7')) AS qi
                       GROUP BY ContentID
                       HAVING      (COUNT(*) >= 2)) AS q INNER JOIN
                      VWTenantPropertiesResults AS r ON r.ContentID = q.ContentID
WHERE     (r.ContentBedRooms BETWEEN 1 AND 4) AND (r.ContentPrice BETWEEN 50 AND 500)

However, when I run the following code in the page it returns an error Incorrect syntax near the keyword 'SELECT';

rsemailagents.Source = "SELECT DISTINCT CustomerEmail"
rsemailagents.Source = rsemailagents.Source& "FROM    ("
rsemailagents.Source = rsemailagents.Source& "        SELECT  ContentID"
rsemailagents.Source = rsemailagents.Source& "        FROM    ("
rsemailagents.Source = rsemailagents.Source& "                SELECT  ContentID"
rsemailagents.Source = rsemailagents.Source& "                FROM    VWTenantPropertiesResults"
rsemailagents.Source = rsemailagents.Source& "                WHERE   ContentStreet = '" & Replace(rspropertyresults__varReqStreet, "'", "''") & "'"
rsemailagents.Source = rsemailagents.Source& "                UNION ALL"
rsemailagents.Source = rsemailagents.Source& "                SELECT  ContentID"
rsemailagents.Source = rsemailagents.Source& "                FROM    VWTenantPropertiesResults"
rsemailagents.Source = rsemailagents.Source& "                WHERE   ContentTown = '" & Replace(rspropertyresults__varReqTown, "'", "''") & "'"
rsemailagents.Source = rsemailagents.Source& "                UNION ALL"
rsemailagents.Source = rsemailagents.Source& "                SELECT  ContentID"
rsemailagents.Source = rsemailagents.Source& "                FROM    VWTenantPropertiesResults"
rsemailagents.Source = rsemailagents.Source& "                WHERE   ContentTrimmedPostCode LIKE '" & Replace(varPostcode, "'", "''") & "%'"
rsemailagents.Source = rsemailagents.Source& "                ) qi"
rsemailagents.Source = rsemailagents.Source& "        GROUP BY"
rsemailagents.Source = rsemailagents.Source& "                ContentID"
rsemailagents.Source = rsemailagents.Source& "        HAVING  COUNT(*) >= 2"
rsemailagents.Source = rsemailagents.Source& "        ) q "
rsemailagents.Source = rsemailagents.Source& "JOIN    VWTenantPropertiesResults r "
rsemailagents.Source = rsemailagents.Source& "ON      r.ContentID = q.ContentID "
rsemailagents.Source = rsemailagents.Source& "WHERE   ContentBedrooms BETWEEN " & Replace(rspropertyresults__varBedroomsNoMin, "'", "''") & " "
rsemailagents.Source = rsemailagents.Source& "AND     " & Replace(rspropertyresults__varBedroomsNoMax, "'", "''") & " "
rsemailagents.Source = rsemailagents.Source& "AND     ContentPrice BETWEEN " & Replace(rspropertyresults__varPriceMin, "'", "''") & " "
rsemailagents.Source = rsemailagents.Source& "AND     " & Replace(rspropertyresults__varPriceMax, "'", "''") & " " & varSQL & " "

I wondered if anyone has any ideas on why the DISTINCT won't work in the code?

Thank you.

Answer 1

Put a space in the string at the end of this line

change

rsemailagents.Source = "SELECT DISTINCT CustomerEmail"

to this

rsemailagents.Source = "SELECT DISTINCT CustomerEmail "

Answer 2

rsemailagents.Source = "SELECT DISTINCT CustomerEmail"

there needs to be a whitespace behind Customer email.

rsemailagents.Source = "SELECT DISTINCT CustomerEmail "

Answer 3

I didn't read the whole stuff, but at the end of the first line, the space is missing :-)

I find it easier to add spaces at the begin of every line, because you can easily see if it is missing or not. Of course, it is still ugly code, as always when putting sql statements together like this.

Answer 4

There are differences between the SQL code you have shown, and what the ASP code renders. You should output the code from the ASP page to see what is going on.

There is a single quote missing in the LIKE statement just before the % sign.