This topic is locked
[SOLVED]

 Error when calling stored procedure in custom view

2/12/2019 10:00:06 AM
ASPRunner.NET General questions
Pete K author

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, 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.<>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

Sergey Kornilov 2/13/2019

A lot of misunderstandings here.

  1. JOINs are supported. UNIONs are not. A full list of what is not supported can be found in the manual:

    https://xlinesoft.com/asprunnernet/docs/sql.htm
  2. Stored procedure calls in SQL query are also not supported.
  3. There is also an article that explains how exactly to handle your scenario:

    https://xlinesoft.com/asprunnernet/docs/how_to_display_data_returned_by_sp.htm

Pete K author 2/13/2019



A lot of misunderstandings here.

  1. JOINs are supported. UNIONs are not. A full list of what is not supported can be found in the manual:

    https://xlinesoft.co...et/docs/sql.htm


Yes, of course I meant to say UNION.



2. Stored procedure calls in SQL query are also not supported.
3. There is also an article that explains how exactly to handle your scenario:

https://xlinesoft.co...urned_by_sp.htm


Thank you for that link. I will try that.

Pete K author 2/13/2019

Unfortunately, the suggested solution doesn't seem viable for a report. None of the events mentioned in the article are available for reports.

Pete K author 2/14/2019

I'm marking this as solved since there really isn't a problem other than known and inherent limitations to what can be done with a report. I have actually constructed a workaround by means restructuring my base table.