Whats wrong with this SQL statment?

[WireMilitia]

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!

            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()

[sapator]

So if it works ok on sql server, why not write it to a stored procedure and just pass the procedure name and the values to your code?
I started crying when i saw this on the sqlcommand

[techgnome]

1) sapator is right... a query like that deserves to be in a stored proc.
2) You can't shove a GO statement in the middle of commands like that. It terminates the transaction and runs it... anything that follows, won't run.
3) In fact.... using the GO like that will throw an error. Which is probably what your issue is.

-tg