Блог

вот код:

 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, Booleanamp; handled)
       at MS.Win32.HwndWrapper.WndProc(IntPtr hwnd, Int32 msg, IntPtr wParam, IntPtr lParam, Booleanamp; 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(MSGamp; 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:LearningRecyclingStatsRecyclingStatsobjx86DebugApp.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:LearningRecyclingStatsRecyclingStatsMainWindow.xaml.cs:line 333
            at RecyclingStats.MainWindow..ctor() in G:TryMainWindow.xaml.cs:line 44
       InnerException: 
  

Вот полный запрос с соответствующими пробелами:

 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";

«ИЗ #TMP»

Вам нужен пробел.