When I execute the following sql statment in VB I get an error.

Incorrect syntax near the keyword 'ORDER'.

Executing it without the quotes works great in MS.SQL Server Management Studio Express.

Can anyone identify an extra space or a missing space or a quote out of place maybe?

I've been working with this single query for ages now! Thx!



HTML Code:
            myCommand = New SqlCommand("DECLARE @PLANNED_START varchar(50), @PLANNED_FINISH varchar(50), @DATEDIFF INT, @RFC INT " & _
"SET @PLANNED_START = '6/11/2010' " & _
"SET @PLANNED_FINISH = '6/13/2010' " & _
"SET @DATEDIFF = 2 " & _
"SET @RFC = 113560 " & _
"Select WO.* " & _
"INTO #WO " & _
"FROM dbo.v_Work_Order as WO with(nolock) " & _
"WHERE ((WO.PLANNED_START BETWEEN @PLANNED_START and @PLANNED_FINISH) or " & _
"(WO.PLANNED_FINISH BETWEEN @PLANNED_START and @PLANNED_FINISH) or " & _
"(WO.PLANNED_START < @PLANNED_START and WO.PLANNED_FINISH > @PLANNED_FINISH)) and " & _
"DATEDIFF(DAY,WO.PLANNED_START, WO.PLANNED_FINISH) < @DATEDIFF " & _
"SELECT 'RFC#' + CAST(RFC1 as varchar(10)) + ' Collides with RFC#' + CAST(RFC2 as varchar(10)) + ' On ' + CI + ' Between ' + " & _
"CASE WHEN RFC1PLANNED_START > RFC2PLANNED_START THEN CAST(RFC1PLANNED_START AS VARCHAR(30)) ELSE CAST(RFC2PLANNED_START AS VARCHAR(30)) END + ' and ' " & _
" + CASE WHEN RFC1PLANNED_FINISH < RFC2PLANNED_FINISH THEN CAST(RFC1PLANNED_FINISH AS VARCHAR(30)) ELSE CAST(RFC2PLANNED_FINISH AS VARCHAR(30))END[ColissionResults] " & _
"FROM (SELECT DISTINCT WO1.RFC# as RFC1, WO2.RFC# as RFC2, WO1.NAME_1 as CI, WO1.PLANNED_START AS RFC1PLANNED_START, WO1.PLANNED_FINISH as RFC1PLANNED_FINISH, " & _
"WO2.PLANNED_START AS RFC2PLANNED_START, WO2.PLANNED_FINISH as RFC2PLANNED_FINISH " & _
"FROM (SELECT DISTINCT WO.CHANGE_ID as RFC#, MIN(WO.PLANNED_START) as PLANNED_START, MAX(WO.PLANNED_FINISH) as PLANNED_FINISH, " & _
"CIs.SEARCH_CODE, CIs.NAME_1 " & _
"FROM #WO as WO with(nolock) join " & _
"dbo.v_Work_Orders_CI as WOCI with(nolock) on WO.ID = WOCI.WORK_ORDER_ID join " & _
"(SELECT DISTINCT MLTPL.SEARCH_CODE, MLTPL.NAME_1 " & _
"FROM (Select DISTINCT WO.CHANGE_ID as RFC#, CIs.SEARCH_CODE, CIs.NAME_1 " & _
"From #WO as WO with(nolock) join " & _
"dbo.v_Work_Orders_CI as WOCI with(nolock) on WO.ID = WOCI.WORK_ORDER_ID join " & _
"(SELECT DISTINCT MLTPL.SEARCH_CODE, MLTPL.NAME_1 " & _
"FROM (Select DISTINCT WO.CHANGE_ID as RFC#, CIs.SEARCH_CODE, CIs.NAME_1 " & _
"From #WO as WO with(nolock) join " & _
"dbo.v_Work_Orders_CI as WOCI with(nolock) on WO.ID = WOCI.WORK_ORDER_ID join " & _
"(Select DISTINCT CI.SEARCH_CODE, CI.NAME_1, CI.CATEGORY " & _
"From #WO as WO with(nolock) join " & _
"dbo.v_Work_Orders_CI as WOCI with(nolock) on WO.ID = WOCI.WORK_ORDER_ID join " & _
"dbo.v_Configuration_Items as CI with(nolock) on WOCI.CONFIGURATION_ITEM_SEARCH_CODE = CI.SEARCH_CODE " & _
"Group by CI.SEARCH_CODE, CI.NAME_1, CI.CATEGORY " & _
"HAVING count(*) > 1 ) " & _
"AS CIs on WOCI.CONFIGURATION_ITEM_SEARCH_CODE = CIs.SEARCH_CODE " & _
"Where WO.CHANGE_ID is not null ) " & _
"AS MLTPL " & _
"GROUP BY MLTPL.SEARCH_CODE, MLTPL.NAME_1 " & _
"HAVING COUNT(*) > 1) " & _
"AS CIs on WOCI.CONFIGURATION_ITEM_SEARCH_CODE = CIs.SEARCH_CODE " & _
"WHERE WO.CHANGE_ID = @RFC " & _
"GROUP BY WO.CHANGE_ID, CIs.SEARCH_CODE, CIs.NAME_1) " & _
"as WO1, " & _
"(SELECT DISTINCT WO.CHANGE_ID as RFC#, MIN(WO.PLANNED_START) as PLANNED_START, MAX(WO.PLANNED_FINISH) as PLANNED_FINISH, " & _
"CIs.SEARCH_CODE, CIs.NAME_1 " & _
"FROM #WO as WO with(nolock) join " & _
"dbo.v_Work_Orders_CI as WOCI with(nolock) on WO.ID = WOCI.WORK_ORDER_ID join " & _
"(SELECT DISTINCT MLTPL.SEARCH_CODE, MLTPL.NAME_1 " & _
"FROM (Select DISTINCT WO.CHANGE_ID as RFC#, CIs.SEARCH_CODE, CIs.NAME_1 " & _
"From #WO as WO with(nolock) join " & _
"dbo.v_Work_Orders_CI as WOCI with(nolock) on WO.ID = WOCI.WORK_ORDER_ID join " & _
"(Select DISTINCT CI.SEARCH_CODE, CI.NAME_1 " & _
"From #WO as WO with(nolock) join " & _
"dbo.v_Work_Orders_CI as WOCI with(nolock) on WO.ID = WOCI.WORK_ORDER_ID join " & _
"dbo.v_Configuration_Items as CI with(nolock) on WOCI.CONFIGURATION_ITEM_SEARCH_CODE = CI.SEARCH_CODE " & _
"Group by CI.SEARCH_CODE, CI.NAME_1 " & _
"HAVING count(*) > 1) " & _
"AS CIs on WOCI.CONFIGURATION_ITEM_SEARCH_CODE = CIs.SEARCH_CODE " & _
"Where WO.CHANGE_ID is not null) " & _
"AS MLTPL " & _
"GROUP BY MLTPL.SEARCH_CODE, MLTPL.NAME_1 " & _
"HAVING COUNT(*) > 1) " & _
"AS CIs on WOCI.CONFIGURATION_ITEM_SEARCH_CODE = CIs.SEARCH_CODE " & _
"WHERE WO.CHANGE_ID is not null and WO.CHANGE_ID <> @RFC " & _
"GROUP BY WO.CHANGE_ID, CIs.SEARCH_CODE, CIs.NAME_1) " & _
"as WO2 " & _
"WHERE WO1.SEARCH_CODE = WO2.SEARCH_CODE and WO1.NAME_1 = WO2.NAME_1 and " & _
"((WO1.PLANNED_START BETWEEN WO2.PLANNED_START and WO2.PLANNED_FINISH or WO1.PLANNED_FINISH BETWEEN WO2.PLANNED_START and WO2.PLANNED_FINISH) OR " & _
"(WO2.PLANNED_START BETWEEN WO1.PLANNED_START and WO1.PLANNED_FINISH or WO2.PLANNED_FINISH BETWEEN WO1.PLANNED_START and WO1.PLANNED_FINISH) OR " & _
"(WO1.PLANNED_START < WO2.PLANNED_START and WO1.PLANNED_FINISH > WO2.PLANNED_FINISH))) " & _
"AS Report " & _
"ORDER BY CI " & _
"GO " & _
"drop table #WO", myConnection)

            'execute the command and assigning it to connection
            dr = myCommand.ExecuteReader()