here code:
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]) [amount]," + " dates = right(convert(varchar(50), [date_in], 106), 8) " + "into #tmp " + "from " + " [rec_stats] " + "group " + " material," + " right(convert(varchar(50), [date_in], 106), 8) " + "order " + " material, min ([date_in])"+ "select dates," + " sum(case material when 0 amount else 0 end)as paper," + " sum(case material when 1 amount else 0 end)as plastic," + " sum(case material when 2 amount else 0 end)as glass," + " sum(case material when 3 amount else 0 end)as metals," + " sum(case material when 4 amount else 0 end)as fabric" + "from #tmp" + "group 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(); the fill function thrown sqlexception, idea problem?
here exception:
system.windows.markup.xamlparseexception unhandled message='the invocation of constructor on type 'recyclingstats.mainwindow' matches specified binding constraints threw exception.' line number '4' , line position '9'. source=presentationframework linenumber=4 lineposition=9 stacktrace: @ system.windows.markup.xamlreader.rewrapexception(exception e, ixamllineinfo lineinfo, uri baseuri) @ system.windows.markup.wpfxamlloader.load(xamlreader xamlreader, ixamlobjectwriterfactory writerfactory, boolean skipjournaledproperties, object rootobject, xamlobjectwritersettings settings, uri baseuri) @ system.windows.markup.wpfxamlloader.loadbaml(xamlreader xamlreader, boolean skipjournaledproperties, object rootobject, xamlaccesslevel accesslevel, uri baseuri) @ system.windows.markup.xamlreader.loadbaml(stream stream, parsercontext parsercontext, object parent, boolean closestream) @ system.windows.application.loadbamlstreamwithsyncinfo(stream stream, parsercontext pc) @ system.windows.application.loadcomponent(uri resourcelocator, boolean bskipjournaledproperties) @ system.windows.application.dostartup() @ system.windows.application.<.ctor>b__1(object unused) @ system.windows.threading.exceptionwrapper.internalrealcall(delegate callback, object args, int32 numargs) @ ms.internal.threading.exceptionfilterhelper.trycatchwhen(object source, delegate method, object args, int32 numargs, delegate catchhandler) @ system.windows.threading.dispatcheroperation.invokeimpl() @ system.windows.threading.dispatcheroperation.invokeinsecuritycontext(object state) @ system.threading.executioncontext.runtrycode(object userdata) @ system.runtime.compilerservices.runtimehelpers.executecodewithguaranteedcleanup(trycode code, cleanupcode backoutcode, object userdata) @ system.threading.executioncontext.runinternal(executioncontext executioncontext, contextcallback callback, object state) @ system.threading.executioncontext.run(executioncontext executioncontext, contextcallback callback, object state, boolean ignoresyncctx) @ system.threading.executioncontext.run(executioncontext executioncontext, contextcallback callback, object state) @ system.windows.threading.dispatcheroperation.invoke() @ system.windows.threading.dispatcher.processqueue() @ system.windows.threading.dispatcher.wndprochook(intptr hwnd, int32 msg, intptr wparam, intptr lparam, boolean& handled) @ ms.win32.hwndwrapper.wndproc(intptr hwnd, int32 msg, intptr wparam, intptr lparam, boolean& handled) @ ms.win32.hwndsubclass.dispatchercallbackoperation(object o) @ system.windows.threading.exceptionwrapper.internalrealcall(delegate callback, object args, int32 numargs) @ ms.internal.threading.exceptionfilterhelper.trycatchwhen(object source, delegate method, object args, int32 numargs, delegate catchhandler) @ system.windows.threading.dispatcher.invokeimpl(dispatcherpriority priority, timespan timeout, delegate method, object args, int32 numargs) @ ms.win32.hwndsubclass.subclasswndproc(intptr hwnd, int32 msg, intptr wparam, intptr lparam) @ ms.win32.unsafenativemethods.dispatchmessage(msg& msg) @ system.windows.threading.dispatcher.pushframeimpl(dispatcherframe frame) @ system.windows.threading.dispatcher.pushframe(dispatcherframe frame) @ system.windows.threading.dispatcher.run() @ system.windows.application.rundispatcher(object ignore) @ system.windows.application.runinternal(window window) @ system.windows.application.run(window window) @ system.windows.application.run() @ recyclingstats.app.main() in g:\learning\recyclingstats\recyclingstats\obj\x86\debug\app.g.cs:line 0 @ system.appdomain._nexecuteassembly(runtimeassembly assembly, string[] args) @ system.appdomain.executeassembly(string assemblyfile, evidence assemblysecurity, string[] args) @ microsoft.visualstudio.hostingprocess.hostproc.runusersassembly() @ system.threading.threadhelper.threadstart_context(object state) @ system.threading.executioncontext.run(executioncontext executioncontext, contextcallback callback, object state, boolean ignoresyncctx) @ system.threading.executioncontext.run(executioncontext executioncontext, contextcallback callback, object state) @ 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: @ system.data.sqlclient.sqlconnection.onerror(sqlexception exception, boolean breakconnection) @ system.data.sqlclient.sqlinternalconnection.onerror(sqlexception exception, boolean breakconnection) @ system.data.sqlclient.tdsparser.throwexceptionandwarning() @ system.data.sqlclient.tdsparser.run(runbehavior runbehavior, sqlcommand cmdhandler, sqldatareader datastream, bulkcopysimpleresultset bulkcopyhandler, tdsparserstateobject stateobj) @ system.data.sqlclient.sqldatareader.consumemetadata() @ system.data.sqlclient.sqldatareader.get_metadata() @ system.data.sqlclient.sqlcommand.finishexecutereader(sqldatareader ds, runbehavior runbehavior, string resetoptionsstring) @ system.data.sqlclient.sqlcommand.runexecutereadertds(commandbehavior cmdbehavior, runbehavior runbehavior, boolean returnstream, boolean async) @ system.data.sqlclient.sqlcommand.runexecutereader(commandbehavior cmdbehavior, runbehavior runbehavior, boolean returnstream, string method, dbasyncresult result) @ system.data.sqlclient.sqlcommand.runexecutereader(commandbehavior cmdbehavior, runbehavior runbehavior, boolean returnstream, string method) @ system.data.sqlclient.sqlcommand.executereader(commandbehavior behavior, string method) @ system.data.sqlclient.sqlcommand.executedbdatareader(commandbehavior behavior) @ system.data.common.dbcommand.system.data.idbcommand.executereader(commandbehavior behavior) @ system.data.common.dbdataadapter.fillinternal(dataset dataset, datatable[] datatables, int32 startrecord, int32 maxrecords, string srctable, idbcommand command, commandbehavior behavior) @ system.data.common.dbdataadapter.fill(datatable[] datatables, int32 startrecord, int32 maxrecords, idbcommand command, commandbehavior behavior) @ system.data.common.dbdataadapter.fill(datatable datatable) @ recyclingstats.mainwindow.setchartdata() in g:\learning\recyclingstats\recyclingstats\mainwindow.xaml.cs:line 333 @ recyclingstats.mainwindow..ctor() in g:\try\mainwindow.xaml.cs:line 44 innerexception:
here full query proper spaces:
string strquery = "select " + " material, " + " sum([amount]) [amount], " + " dates = right(convert(varchar(50), [date_in], 106), 8) " + "into #tmp " + "from " + " [rec_stats] " + "group " + " material, " + " right(convert(varchar(50), [date_in], 106), 8) " + "order " + " material, min ([date_in])"+ "select dates, " + " sum(case material when 0 amount else 0 end)as paper, " + " sum(case material when 1 amount else 0 end)as plastic, " + " sum(case material when 2 amount else 0 end)as glass, " + " sum(case material when 3 amount else 0 end)as metals, " + " sum(case material when 4 amount else 0 end)as fabric " + "from #tmp " + "group dates"; there no space:
"from #tmp " + "group dates"; but have
"from #tmp" + "group dates"; which turns "from #tmpgroup dates";
Comments
Post a Comment