为什么这个查询会抛出异常？

Question

这是代码：

DataTable ds = new DataTable();
SqlConnection cons = new SqlConnection(strConnString);
SqlDataAdapter ad = new SqlDataAdapter();
SqlCommand cmd = new SqlCommand();
cons.Open();
string strQuery = "SELECT" +
    "  material," +
    "  SUM([Amount]) AS [Amount]," +
    "  Dates = RIGHT(CONVERT(varchar(50), [date_in], 106), 8) " +
    "INTO #TMP " +
    "FROM " +
    "  [rec_stats] " +
    "GROUP BY " +
    "  material," +
    "  RIGHT(CONVERT(varchar(50), [date_in], 106), 8) " +
    "ORDER BY " +
    "  material, min ([date_in])"+
    "SELECT DATES," +
    "       SUM(CASE material WHEN 0 THEN Amount ELSE 0 END)AS PAPER," +
    "       SUM(CASE material WHEN 1 THEN Amount ELSE 0 END)AS PLASTIC," +
    "       SUM(CASE material WHEN 2 THEN Amount ELSE 0 END)AS GLASS," +
    "       SUM(CASE material WHEN 3 THEN Amount ELSE 0 END)AS METALS," +
    "       SUM(CASE material WHEN 4 THEN Amount ELSE 0 END)AS FABRIC" +
    "FROM #TMP" +
    "GROUP BY DATES";

cmd.CommandText = strQuery;         
cmd.Connection = cons;
ad.SelectCommand = cmd;

try
{
    ad.Fill(ds);
}
catch (Exception d)
{
    MessageBox.Show("CRAP!! " + d.ToString());
}
string dropper = "DROP TABLE #TMP";
cmd.CommandText = dropper;
cmd.ExecuteNonQuery();
cons.Close();

fill 函数抛出了 sqlException，知道问题出在哪里吗？

这是一个例外：

System.Windows.Markup.XamlParseException was unhandled
  Message='The invocation of the constructor on type 'RecyclingStats.MainWindow' that matches the specified binding constraints threw an exception.' Line number '4' and line position '9'.
  Source=PresentationFramework
  LineNumber=4
  LinePosition=9
  StackTrace:
       at System.Windows.Markup.XamlReader.RewrapException(Exception e, IXamlLineInfo lineInfo, Uri baseUri)
       at System.Windows.Markup.WpfXamlLoader.Load(XamlReader xamlReader, IXamlObjectWriterFactory writerFactory, Boolean skipJournaledProperties, Object rootObject, XamlObjectWriterSettings settings, Uri baseUri)
       at System.Windows.Markup.WpfXamlLoader.LoadBaml(XamlReader xamlReader, Boolean skipJournaledProperties, Object rootObject, XamlAccessLevel accessLevel, Uri baseUri)
       at System.Windows.Markup.XamlReader.LoadBaml(Stream stream, ParserContext parserContext, Object parent, Boolean closeStream)
       at System.Windows.Application.LoadBamlStreamWithSyncInfo(Stream stream, ParserContext pc)
       at System.Windows.Application.LoadComponent(Uri resourceLocator, Boolean bSkipJournaledProperties)
       at System.Windows.Application.DoStartup()
       at System.Windows.Application.<.ctor>b__1(Object unused)
       at System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs)
       at MS.Internal.Threading.ExceptionFilterHelper.TryCatchWhen(Object source, Delegate method, Object args, Int32 numArgs, Delegate catchHandler)
       at System.Windows.Threading.DispatcherOperation.InvokeImpl()
       at System.Windows.Threading.DispatcherOperation.InvokeInSecurityContext(Object state)
       at System.Threading.ExecutionContext.runTryCode(Object userData)
       at System.Runtime.CompilerServices.RuntimeHelpers.ExecuteCodeWithGuaranteedCleanup(TryCode code, CleanupCode backoutCode, Object userData)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Windows.Threading.DispatcherOperation.Invoke()
       at System.Windows.Threading.Dispatcher.ProcessQueue()
       at System.Windows.Threading.Dispatcher.WndProcHook(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
       at MS.Win32.HwndWrapper.WndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Boolean& handled)
       at MS.Win32.HwndSubclass.DispatcherCallbackOperation(Object o)
       at System.Windows.Threading.ExceptionWrapper.InternalRealCall(Delegate callback, Object args, Int32 numArgs)
       at MS.Internal.Threading.ExceptionFilterHelper.TryCatchWhen(Object source, Delegate method, Object args, Int32 numArgs, Delegate catchHandler)
       at System.Windows.Threading.Dispatcher.InvokeImpl(DispatcherPriority priority, TimeSpan timeout, Delegate method, Object args, Int32 numArgs)
       at MS.Win32.HwndSubclass.SubclassWndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam)
       at MS.Win32.UnsafeNativeMethods.DispatchMessage(MSG& msg)
       at System.Windows.Threading.Dispatcher.PushFrameImpl(DispatcherFrame frame)
       at System.Windows.Threading.Dispatcher.PushFrame(DispatcherFrame frame)
       at System.Windows.Threading.Dispatcher.Run()
       at System.Windows.Application.RunDispatcher(Object ignore)
       at System.Windows.Application.RunInternal(Window window)
       at System.Windows.Application.Run(Window window)
       at System.Windows.Application.Run()
       at RecyclingStats.App.Main() in G:\Learning\RecyclingStats\RecyclingStats\obj\x86\Debug\App.g.cs:line 0
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: System.Data.SqlClient.SqlException
       Message=Incorrect syntax near '#TMPGROUP'.
       Source=.Net SqlClient Data Provider
       ErrorCode=-2146232060
       Class=15
       LineNumber=1
       Number=102
       Procedure=""
       Server=localhost
       State=1
       StackTrace:
            at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
            at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
            at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
            at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
            at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
            at System.Data.SqlClient.SqlDataReader.get_MetaData()
            at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
            at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
            at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
            at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
            at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
            at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
            at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
            at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
            at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
            at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
            at RecyclingStats.MainWindow.setChartData() in G:\Learning\RecyclingStats\RecyclingStats\MainWindow.xaml.cs:line 333
            at RecyclingStats.MainWindow..ctor() in G:\Try\MainWindow.xaml.cs:line 44
       InnerException: 

Answer 1

这是带有适当空格的完整查询：

string strQuery = "SELECT " +
    "  material, " +
    "  SUM([Amount]) AS [Amount], " +
    "  Dates = RIGHT(CONVERT(varchar(50), [date_in], 106), 8) " +
    "INTO #TMP " +
    "FROM " +
    "  [rec_stats] " +
    "GROUP BY " +
    "  material, " +
    "  RIGHT(CONVERT(varchar(50), [date_in], 106), 8) " +
    "ORDER BY " +
    "  material, min ([date_in])"+
    "SELECT DATES, " +
    "       SUM(CASE material WHEN 0 THEN Amount ELSE 0 END)AS PAPER, " +
    "       SUM(CASE material WHEN 1 THEN Amount ELSE 0 END)AS PLASTIC, " +
    "       SUM(CASE material WHEN 2 THEN Amount ELSE 0 END)AS GLASS, " +
    "       SUM(CASE material WHEN 3 THEN Amount ELSE 0 END)AS METALS, " +
    "       SUM(CASE material WHEN 4 THEN Amount ELSE 0 END)AS FABRIC " +
    "FROM #TMP " +
    "GROUP BY DATES";

没有空格：

"FROM #TMP " +
"GROUP BY DATES";

但你有

"FROM #TMP" +
"GROUP BY DATES";

它变成 "FROM #TMPGROUP BY DATES";

Answer 2

“FROM #TMP” +

您需要一个空间。