I had a really complicated custom query constructed for a report that made use of subqueries, SQL variables, and a JOIN clause. After discovering that JOINS are not supported in ASPR.net (despite the fact that the Results tab in the IDE properly parses it) I started looking at alternatives. I can't save the view in SQL Server and use that for the report because the SQL Variable is used in a subquery. I finally hit on the idea of creating a stored procedure that accepts a parameter when I read in the help file that an SP can be called in a custom view. It seemed my problem was solved.
Unfortunately, I got a similar result with this tactic. I created the SP in SQL Server and tried to create a report based on the SP. Again, it parsed properly in the IDE but this time it resulted in a yellow screen error in the compiled webapp.
I have tried to distill down this issue by removing all the complications, hoping to determine where the problem lies. So I started with a clean database and a new app, removed the variables and just tried calling a SP that joins two tables with identical structures. I'm getting the exact same results. I'm thinking that either this is a bug or I'm using syntax to call the SP that the ASPR framework doesn't like. I tied this on a report and a list page based on the same query.
Here's what I have at this point:
The procedure looks like this in SQL Server:
ALTER procedure [dbo].[CallTest] as
begin
select [Year],Category,[Name] from Items
union select [Year],Category,[Name] from Items2
end
In both the custom view and the report, I pasted the following into the SQL tab:
exec CallTest
Again, at this point it seemed to work, as evidenced by the joined tables displaying as expected in the Results tab. But when the compiled webapp is run, I get different yellow screens.
For the list page based on the custom view, I get this one:
---------------
Must specify table to select from.
No column name was specified for column 1 of 'a'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Must specify table to select from.
No column name was specified for column 1 of 'a'.
Source Error:
Line 239: {
Line 240: if ( !MVCFunctions.HandleError() )
Line 241: throw e;
Line 242: }
Line 243: return null;
Source File: c:\Users\pkoutoul\Documents\ASPRunnerNETProjects\Test1\output\connections\Connection.cs Line: 241
Stack Trace:
[SqlException (0x80131904): Must specify table to select from.
No column name was specified for column 1 of 'a'.]
runnerDotNet.Connection.query(XVar sql) in c:\Users\pkoutoul\Documents\ASPRunnerNETProjects\Test1\output\connections\Connection.cs:241
runnerDotNet.Connection.getFetchedRowsNumber(XVar sql) in c:\Users\pkoutoul\Documents\ASPRunnerNETProjects\Test1\output\connections\Connection.cs:613
runnerDotNet.ListPage.buildSQL() in c:\Users\pkoutoul\Documents\ASPRunnerNETProjects\Test1\output\Classes\listpage.cs:985
runnerDotNet.ListPage.prepareForBuildPage() in c:\Users\pkoutoul\Documents\ASPRunnerNETProjects\Test1\output\Classes\listpage.cs:1820
CallSite.Target(Closure , CallSite , Object ) +95
runnerDotNet.ItemsController.list() in c:\Users\pkoutoul\Documents\ASPRunnerNETProjects\Test1\output\Controllers\Items\list.cs:275
lambdamethod(Closure , ControllerBase , Object[] ) +62
System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) +14
System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary2 parameters) +182
System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary
2 parameters) +27
System.Web.Mvc.<>cDisplayClass15.<InvokeActionMethodWithFilters>b12() +56
System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func1 continuation) +256
System.Web.Mvc.<>c__DisplayClass17.<InvokeActionMethodWithFilters>b__14() +22
System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodWithFilters(ControllerContext controllerContext, IList
1 filters, ActionDescriptor actionDescriptor, IDictionary2 parameters) +190
System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName) +311
System.Web.Mvc.Controller.ExecuteCore() +105
runnerDotNet.BaseController.ExecuteThread(Object arg) in c:\Users\pkoutoul\Documents\ASPRunnerNETProjects\Test1\output\Controllers\BaseController.cs:45
[AggregateException: One or more errors occurred.]
runnerDotNet.BaseController.ExecuteCore() in c:\Users\pkoutoul\Documents\ASPRunnerNETProjects\Test1\output\Controllers\BaseController.cs:32
System.Web.Mvc.ControllerBase.Execute(RequestContext requestContext) +88
System.Web.Mvc.ControllerBase.System.Web.Mvc.IController.Execute(RequestContext requestContext) +10
System.Web.Mvc.<>c__DisplayClassb.<BeginProcessRequest>b__5() +34
System.Web.Mvc.Async.<>c__DisplayClass1.<MakeVoidDelegate>b__0() +16
System.Web.Mvc.Async.<>c__DisplayClass8
1.<BeginSynchronous>b__7(IAsyncResult ) +10
System.Web.Mvc.Async.WrappedAsyncResult1.End() +50
System.Web.Mvc.<>c__DisplayClasse.<EndProcessRequest>b__d() +28
System.Web.Mvc.SecurityUtil.<GetCallInAppTrustThunk>b__0(Action f) +7
System.Web.Mvc.SecurityUtil.ProcessInApplicationTrust(Action action) +23
System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +59
System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) +9
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +9749277
System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step) +48
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +159
---------------
And for the Report based on the exact same SP call, I get this one:
Incorrect syntax near ')'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near ')'.
Source Error:
Line 239: {
Line 240: if ( !MVCFunctions.HandleError() )
Line 241: throw e;
Line 242: }
Line 243: return null;
Source File: c:\Users\pkoutoul\Documents\ASPRunnerNETProjects\Test1\output\connections\Connection.cs Line: 241
Stack Trace:
[SqlException (0x80131904): Incorrect syntax near ')'.]
runnerDotNet.Connection.query(XVar sql) in c:\Users\pkoutoul\Documents\ASPRunnerNETProjects\Test1\output\connections\Connection.cs:241
runnerDotNet.ReportGroups.getDisplayGroups(Object _param_from) in c:\Users\pkoutoul\Documents\ASPRunnerNETProjects\Test1\output\Classes\reportlib.cs:1963
runnerDotNet.ReportLogic.getReport(Object _param_from) in c:\Users\pkoutoul\Documents\ASPRunnerNETProjects\Test1\output\Classes\reportlib.cs:2478
runnerDotNet.ReportPage.setStandartData(Object _options) in c:\Users\pkoutoul\Documents\ASPRunnerNETProjects\Test1\output\Classes\reportpage.cs:558
runnerDotNet.ReportPage.setReportData(Object _param_options) in c:\Users\pkoutoul\Documents\ASPRunnerNETProjects\Test1\output\Classes\reportpage.cs:284
runnerDotNet.ReportPage.process() in c:\Users\pkoutoul\Documents\ASPRunnerNETProjects\Test1\output\Classes\reportpage.cs:121
CallSite.Target(Closure , CallSite , Object ) +96
runnerDotNet.Items_ReportController.report() in c:\Users\pkoutoul\Documents\ASPRunnerNETProjects\Test1\output\Controllers\Items_Report\report.cs:186
lambda_method(Closure , ControllerBase , Object[] ) +62
System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) +14
System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary
2 parameters) +182
System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary2 parameters) +27
System.Web.Mvc.<>c__DisplayClass15.<InvokeActionMethodWithFilters>b__12() +56
System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func
1 continuation) +256
System.Web.Mvc.<>cDisplayClass17.<InvokeActionMethodWithFilters>b14() +22
System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodWithFilters(ControllerContext controllerContext, IList1 filters, ActionDescriptor actionDescriptor, IDictionary
2 parameters) +190
System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName) +311
System.Web.Mvc.Controller.ExecuteCore() +105
runnerDotNet.BaseController.ExecuteThread(Object arg) in c:\Users\pkoutoul\Documents\ASPRunnerNETProjects\Test1\output\Controllers\BaseController.cs:45
[AggregateException: One or more errors occurred.]
runnerDotNet.BaseController.ExecuteCore() in c:\Users\pkoutoul\Documents\ASPRunnerNETProjects\Test1\output\Controllers\BaseController.cs:32
System.Web.Mvc.ControllerBase.Execute(RequestContext requestContext) +88
System.Web.Mvc.ControllerBase.System.Web.Mvc.IController.Execute(RequestContext requestContext) +10
System.Web.Mvc.<>cDisplayClassb.<BeginProcessRequest>b5() +34
System.Web.Mvc.Async.<>cDisplayClass1.<MakeVoidDelegate>b0() +16
System.Web.Mvc.Async.<>cDisplayClass8`1.<BeginSynchronous>b7(IAsyncResult _) +10
System.Web.Mvc.Async.WrappedAsyncResult`1.End() +50
System.Web.Mvc.<>cDisplayClasse.<EndProcessRequest>bd() +28
System.Web.Mvc.SecurityUtil.<GetCallInAppTrustThunk>b__0(Action f) +7
System.Web.Mvc.SecurityUtil.ProcessInApplicationTrust(Action action) +23
System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +59
System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) +9
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +9749277
System.Web.HttpApplication.ExecuteStepImpl(IExecutionStep step) +48
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +159