Incorrect Syntax near ',' using scaffolding

Incorrect Syntax near ',' using scaffolding

  

I have a task status type table (i.e. Closed, Open, etc) and a task status table with a foreign key to the task status type table. I created the Task Status and Task Status Detail screens with scaffolding, and the combo box for Status Type ID was generated automatically but throws a syntax error when viewing the status detail view as a new or edited record. Could someone please help? I am new to outsystems and can't seem to decipher the message or find a direction to try. This is actually happening on a few different tables with similar set ups.


Thank you!



Incorrect syntax near ','.


Environment InformationeSpaceVer: 86 (Id=267, PubId=280, CompiledWith=10.0.816.0)
RequestUrl: https://fgp.outsystemscloud.com/CMS2/TaskStatusDetail.aspx?TaskStatusId=0&(Not.Licensed.For.Production)= (Method: GET)
AppDomain: /LM/W3SVC/8/ROOT/CMS2-248-131738883501906966
FilePath: C:\OutSystems\Sandboxes\NIREAX005\Platform Server\running\CMS2\TaskStatusDetail.aspx
ClientIp: 72.50.244.170
Locale: en-US
DateFormat: yyyy-MM-dd
PID: 6416 ('w3wp', Started='6/4/2018 4:41:49 PM', Priv=900Mb, Virt=18277Mb)
TID: 150
Thread Name:
.NET: 4.0.30319.42000
Stack:
Incorrect syntax near ','.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   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 OutSystems.HubEdition.DatabaseProvider.SqlServer.ExecutionService.ExecutionService.ExecuteReader(IDbCommand cmd)
   at #Ylb.#7lb.ExecuteReader(String description, Boolean isApplication, Boolean transformParameters, Boolean skipLog)
   at OutSystems.Internal.Db.DatabaseAccessProvider`1.ExecuteQuery[T](Command cmd, GenericRecordList`1 rl, String description, Boolean transformParameters, Boolean skipLog)
   at ssCMS2.Flows.FlowMainFlow.ScrnTaskStatusDetail.selTaskStatus_StatusTypeID_onDataBinding(Object sender, EventArgs e)
   at System.EventHandler.Invoke(Object sender, EventArgs e)
   at System.Web.UI.Control.OnDataBinding(EventArgs e)
   at System.Web.UI.WebControls.ListControl.OnDataBinding(EventArgs e)
   at System.Web.UI.WebControls.ListControl.PerformSelect()
   at OutSystems.HubEdition.WebWidgets.DropDownList.DataBind()
   at OutSystems.HubEdition.WebWidgets.Container.DataBindChildren()
   at System.Web.UI.Control.DataBind(Boolean raiseOnDataBinding)
   at OutSystems.HubEdition.WebWidgets.Container.DataBind()
   at OutSystems.HubEdition.WebWidgets.Container.DataBindChildren()
   at System.Web.UI.Control.DataBind(Boolean raiseOnDataBinding)
   at OutSystems.HubEdition.WebWidgets.Container.DataBind()
   at OutSystems.HubEdition.WebWidgets.Container.DataBindChildren()
   at System.Web.UI.Control.DataBind(Boolean raiseOnDataBinding)
   at OutSystems.HubEdition.WebWidgets.Container.DataBind()
   at OutSystems.HubEdition.WebWidgets.Container.DataBindChildren()
   at System.Web.UI.Control.DataBind(Boolean raiseOnDataBinding)
   at OutSystems.HubEdition.WebWidgets.Container.DataBind()
   at OutSystems.HubEdition.WebWidgets.Container.DataBindChildren()
   at System.Web.UI.Control.DataBind(Boolean raiseOnDataBinding)
   at OutSystems.HubEdition.WebWidgets.Container.DataBind()
   at OutSystems.HubEdition.WebWidgets.Container.DataBindChildren()
   at System.Web.UI.Control.DataBind(Boolean raiseOnDataBinding)
   at OutSystems.HubEdition.WebWidgets.Container.DataBind()
   at OutSystems.HubEdition.WebWidgets.OSUserControl.DataBindChildren()
   at System.Web.UI.Control.DataBind(Boolean raiseOnDataBinding)
   at OutSystems.HubEdition.WebWidgets.OSUserControl.DataBind()
   at OutSystems.HubEdition.WebWidgets.Form.DataBindChildren()
   at System.Web.UI.Control.DataBind(Boolean raiseOnDataBinding)
   at OutSystems.HubEdition.WebWidgets.Body.DataBindChildren()
   at System.Web.UI.Control.DataBind(Boolean raiseOnDataBinding)
   at OutSystems.HubEdition.WebWidgets.OSPage.DataBindChildren()
   at System.Web.UI.Control.DataBind(Boolean raiseOnDataBinding)
   at OutSystems.HubEdition.WebWidgets.OSPage.DataBind()
   at ssCMS2.Flows.FlowMainFlow.ScrnTaskStatusDetail.DataBind()
   at ssCMS2.Flows.FlowMainFlow.ScrnTaskStatusDetail.Page_Load(Object sender, EventArgs e)
   at System.Web.UI.Control.OnLoad(EventArgs e)
   at System.Web.UI.Control.LoadRecursive()
   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Hi Jon,

Can you share the espace?

Regards,

Marcelo

I'm not sure how to do that- but there is some sensitive customer data in the attached database. Can I copy and paste some screenshots from a certain area for you? I've also sent this on as a support case to outsystems at their request.

Hi Jon,

Attaching an eSpace will neither copy the sensitive data nor allow us to connect to your database.

That said, have you tried to debug the application to see at what point this goes wrong? Does it even hit the preperation, or does it go wrong even before that?

Thanks for the info- would you just like the project (oml file?) Also I have debugged it, it gets past the preparation just fine, but stops on rendering the combo box Status_TypeID. I have valid StatusType records, and the views for those work fine. 


Hi Jon,

If you can share the oml that would be helpfull. You get the error when you click on the combobox to check the options or is before that?

Regards,

Marcelo

I've attached the OML. The error happens as the screen renders the combo box. In debugging it gets past preparation but fails as the combo box is loading. An end user would just see the error page as the page doesn't finish loading after the exception.

Solution

Hi Jon,

In outsystems everything looks correct. Did you try to get all the records from TaskStatusType with an aggregate and use that list on combobox source record list instead of using source entity. Check if this way it works and you also can check if you are getting any strange values from TaskStatusType

Regards,

Marcelo


Solution

Hello Jon,

The error happens when the system tries to fetch data from the TaskStatusType entity to fill the combo box.
The Incorrect syntax near ',' tells me that the query is being ill formed. 

This can be a bug in the platform, or possibly (and more probable), your external tables, when imported, are not properly configured.

In any case, I would do a simple test. If you open the TaskStatusTypes screen, do you get any kind of error?
If not, follow Marcelo's advice and change the combo box to fetch data from an aggregate instead of directly from the Entity. It's a workaround.

If the error is still there, than we have to investigate more.

Cheers.

Also, if the workaround works, I'd still contact OutSystems Support and tell them the problem, as this might, like Eduardo said, be a Platform bug.

I can access the task status type list and detail screens with no errors. 

Here are a few screenshots from my external DB setup

Task Status Table- with Foreign key to task status type:

TaskStatus Table's 'StatusType' Column Properties:


TaskStatusType Table's ID Properties


The integration studio values look the same after importing the entities, and the entities within outsystems look good as far as fields and referencing:

It is also worth noting that I can view the task status list screen which contains a column for status type. See Below:

This works without error, so it only seems to be the combo box as the issue. 


Marcelo: I tried your work around and that was a success! Is there any issue with setting up combo boxes in that fashion in the future?

Also I have reported the original issue to outsystems support to see their thoughts as well.

Hi Jon,

If the combo box works with the aggregate but not with the entity, unless it is a known limitation that I am not aware (and I think it is not), I would say it is a platform bug.

If you told OutSystems, they will be able to take a look in order to find the problem and, eventually, in a future version, solve it.

Cheers.

P.S. There is no problem in use Lists as source or a combo box. If you don't want all the records you also use this approach.

Thank you all for the help!