SqlException: Incorrect syntax near ',' (v2021)

[lfernandes]

I verified that after the last template update, all the forms that had a field configured using the Lookup table with text lookup field (automatic suggestion) showed errors when saving. See the error below:

An unhandled exception occurred while processing the request.
SqlException: Incorrect syntax near ','.
Microsoft.Data.SqlClient.SqlCommand+<>c.<ExecuteDbDataReaderAsync>b__188_0(Task<SqlDataReader> result)

Stack Query Cookies Headers Routing
SqlException: Incorrect syntax near ','.
Microsoft.Data.SqlClient.SqlCommand+<>c.<ExecuteDbDataReaderAsync>b__188_0(Task<SqlDataReader> result)
System.Threading.Tasks.ContinuationResultTaskFromResultTask<TAntecedentResult, TResult>.InnerInvoke()
System.Threading.Tasks.Task+<>c.<.cctor>b__277_0(object obj)
System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, object state)
System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, object state)
System.Threading.Tasks.Task.ExecuteWithThreadLocal(ref Task currentTaskSlot, Thread threadPoolThread)
Dapper.SqlMapper.QueryAsync<T>(IDbConnection cnn, Type effectiveType, CommandDefinition command) in SqlMapper.Async.cs
AspNetMaker2021.Models.ead+DatabaseConnectionBase<N, M, R, T>.GetRowsAsync(string sql, bool main) in DatabaseConnectionBase.cs
AspNetMaker2021.Models.ead+TpPolosEditBase.RenderRow() in TpPolosEdit.cs
AspNetMaker2021.Models.ead+TpPolosEditBase.Run() in TpPolosEdit.cs
AspNetMaker2021.Controllers.HomeController.TpPolosEdit() in TpPolosController.cs
Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor+TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, object controller, object[] arguments)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask<IActionResult> actionResultValueTask)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
Microsoft.AspNetCore.Authorization.Policy.AuthorizationMiddlewareResultHandler.HandleAsync(RequestDelegate next, HttpContext context, AuthorizationPolicy policy, PolicyAuthorizationResult authorizeResult)
Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Session.SessionMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Session.SessionMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

[lfernandes]

ASPNETMAKER VERSION 2021.0.9, the project was created in version 2021 and only minor updates were applied

[MichaelG]

Please show your table schema and lookup settings for the problem field. Also enable debug mode (Tools -> Advanced Settings -> Debug) and show the problem update SQL. Alternatively you can use VS2019 to open and run the project and what the problem SQL is.

[lfernandes]

Follow the information:

Table schema:

TABLE [dbo].[TPPolos](
	[IDPolo] [int] IDENTITY(1,1) NOT NULL,
	[IDMarca] [int] NULL,
	[IDInstituicao] [int] NULL,
	[IDEmpresa] [int] NULL,
	[IDLocalidade] [int] NULL,
	[Codigo] [int] NULL,
	[Sistema] [int] NULL,
	[IngressoOP] [char](6) NULL,
	[Nome] [nvarchar](255) NULL,
	[Tipo] [int] NULL,
	[CNPJ] [char](18) NULL,
	[CNPJTipo] [nvarchar](6) NULL,
	[CNPJCnaeAtende] [int] NULL,
	[CNPJCnae] [varchar](255) NULL,
	[ContratoTipo] [int] NULL,
	[ContratoData] [datetime] NULL,
	[IDRegional] [int] NULL,
	[Diretor] [int] NULL,
	[PPO] [int] NULL,
	[IDHub] [int] NULL,
	[Gestor] [int] NULL,
	[Responsavel] [int] NULL,
	[Apontamento] [int] NULL,
	[Bloqueio] [int] NULL,
	[Meta] [int] NULL,
	[ParceiroMaster] [int] NULL,
	[IDStatus] [int] NULL,
	[IDSituacao] [int] NULL,
	[DataRegistro] [datetime] NULL,
	[DataAtualizacao] [datetime] NULL,
 CONSTRAINT [PK_TPPolos] PRIMARY KEY CLUSTERED 

Lookup Table schema :
TABLE [dbo].[TALocalidades](
	[IDLocalidade] [int] IDENTITY(1,1) NOT NULL,
	[CEP] [char](9) NULL,
	[Logradouro] [nvarchar](255) NULL,
	[Numero] [int] NULL,
	[Complemento] [nvarchar](50) NULL,
	[UF] [char](2) NULL,
	[Estado] [nvarchar](255) NULL,
	[Cidade] [nvarchar](255) NULL,
	[Bairro] [nvarchar](255) NULL,
	[Latitude] [float] NULL,
	[Longitude] [float] NULL,
	[Status] [int] NULL,
	[DataRegistro] [datetime] NULL,
	[DataAtualizacao] [datetime] NULL,
 CONSTRAINT [PK_TALocalidades] PRIMARY KEY CLUSTERED 

Edit Tag settings:
INPUT Tag
Use Lookup Table = true
Force Selection = True

LookupTable
Table Name: TALocalidades
Link field: IDLocalidade

Display Fields
Display Field#1: CEP
Display Field#2: Logradouro
Display Field#3: Complemento
Display Field#4: UF

Allow add

IMPORTANT:

1. It is worth mentioning that these forms worked before the template update.

[MichaelG]

Cannot simulate. Lookup is working correctly.

You may want to use VS2019 to open and run your project, see the stack trace to see what the problem SQL is.

[lfernandes]

Michael,
The problem was related to a duplicate field reference.
Thank you for your help!