Error executing template "Designs/Swift/Paragraph/CPQ_Card.cshtml" System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'and'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.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) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at Dynamicweb.Data.Database.CreateDataSet(IDbDataAdapter dataAdapter, Boolean withSchema) at Dynamicweb.Data.Database.CreateDataSet(String sql, IDbConnection connection, IDbTransaction transaction, Boolean withSchema, Dictionary`2 sqlParams) at Dynamicweb.Data.Database.CreateDataSet(String sql) at DW_CPQ_API.CPQCard..ctor(String modelversionid) at CompiledRazorTemplates.Dynamic.RazorEngine_c274a92aa9e34f2bb3b9bc1ee5acc0fc.ExecuteAsync() at RazorEngine.Templating.TemplateBase.Run(ExecuteContext context, TextWriter reader) at RazorEngine.Templating.RazorEngineCore.RunTemplate(ICompiledTemplate template, TextWriter writer, Object model, DynamicViewBag viewBag) at RazorEngine.Templating.RazorEngineService.Run(ITemplateKey key, TextWriter writer, Type modelType, Object model, DynamicViewBag viewBag) at RazorEngine.Templating.DynamicWrapperService.Run(ITemplateKey key, TextWriter writer, Type modelType, Object model, DynamicViewBag viewBag) at RazorEngine.Templating.RazorEngineServiceExtensions.Run(IRazorEngineService service, String name, TextWriter writer, Type modelType, Object model, DynamicViewBag viewBag) at RazorEngine.Templating.RazorEngineServiceExtensions.<>c__DisplayClass23_0.<Run>b__0(TextWriter writer) at RazorEngine.Templating.RazorEngineServiceExtensions.WithWriter(Action`1 withWriter) at RazorEngine.Templating.RazorEngineServiceExtensions.Run(IRazorEngineService service, String name, Type modelType, Object model, DynamicViewBag viewBag) at Dynamicweb.Rendering.RazorTemplateRenderingProvider.Render(Template template) at Dynamicweb.Rendering.TemplateRenderingService.Render(Template template) at Dynamicweb.Rendering.Template.RenderRazorTemplate() ClientConnectionId:66b3fcdd-de52-41dc-b092-b6833e1b0df3 Error Number:156,State:1,Class:15
1 @inherits Dynamicweb.Rendering.ViewModelTemplate<Dynamicweb.Frontend.ParagraphViewModel> @using Dynamicweb.Ecommerce.ProductCatalog @using System @using DW_CPQ_API @{ ModelController modelController = new ModelController(Model.PageID, Dynamicweb.Context.Current.Request.Params); // expected sales quote no if(modelController!=null && string.IsNullOrEmpty( modelController.SalesQuoteNo)) { modelController.SalesQuoteNo="1144"; // to be removed later } System.Collections.Specialized.NameValueCollection? requestVars = Dynamicweb.Context.Current.Request.Params; if(requestVars!=null && requestVars["quote"]!=null) { modelController.SalesQuoteNo=requestVars["quote"]!; } string theme = !string.IsNullOrWhiteSpace(Model.Item.GetRawValueString("Theme")) ? $"theme {Model.Item.GetRawValueString("Theme").Replace(" ", "").Trim().ToLower()}" : "alert-dark"; var cpqPage = Dynamicweb.Content.Services.Pages.GetPage(Model.PageID); string cpqTheme = cpqPage.Item["CPQ_Theme"]?.ToString() ?? "cpq-bold"; string cardModelName = Pageview.Page.Item["CPQ_ModelVersionId"]?.ToString() ?? ""; DW_CPQ_API.CPQCard card= new DW_CPQ_API.CPQCard(cardModelName); string result=""; if(requestVars!=null && requestVars["cardaction"]!=null && Dynamicweb.Context.Current.Request.HttpMethod == "POST") { if( requestVars["cardaction"].ToString()=="addcardtoquote") { string templateId=requestVars["templateId"]; card.AddCardTempletetoQuote(modelController.SalesQuoteNo,templateId); } else if( requestVars["cardaction"].ToString()=="deletecarditem") { string itemdatakeyline=requestVars["itemdatakeyline"]; CPQCard.DeleteCarditemInQuote(itemdatakeyline); } else if( requestVars["cardaction"].ToString()=="duplicatecarditem") { string itemdatakeyline=requestVars["itemdatakeyline"]; CPQCard.DuplicateCarditemInQuote(itemdatakeyline); } else if( requestVars["cardaction"].ToString()=="createQuote") { result=CPQCard.CreateSalesQuoteFromCards(cardModelName,modelController.SalesQuoteNo); Dynamicweb.Context.Current.Session["redirectdata"] = result; } Dynamicweb.Context.Current.Response.Redirect(Dynamicweb.Context.Current.Request.Url.AbsoluteUri); // to prevent resubmission when refreshing return; } Dictionary<string,object> carditems =CPQCard.LoadCardItemsByQuote(cardModelName,modelController.SalesQuoteNo); var targetPage = Dynamicweb.Content.Services.Pages .GetPages() .FirstOrDefault(p => (p.NavigationTag?.Contains("cpqcustom") ?? false) && (p.Item["CPQ_ModelVersionId"]?.ToString() ?? "") == cardModelName ); string editUrl = targetPage != null ? $"/Default.aspx?ID={targetPage.ID}" : "#"; string? myData = Dynamicweb.Context.Current.Session["redirectdata"] as string; myData = myData != null ? myData.Replace("\"", "\\\"") : ""; // Escape double quotes for JavaScript Dynamicweb.Context.Current.Session.Remove("redirectdata"); } <script> window.editUrl = "@editUrl"; function addcardtolist(templateId) { FormaddItem.templateId.value = templateId; FormaddItem.submit(); } function editcarditem(keyline,pageid) { Formedititem.itemdatakeyline.value = keyline; Formedititem.action= "/Default.aspx?ID="+pageid; Formedititem.submit(); } function deleteitem(keyline) { FormdelItem.itemdatakeyline.value = keyline; FormdelItem.submit(); } function duplicateitem(keyline) { FormduplicateItem.itemdatakeyline.value = keyline; FormduplicateItem.submit(); } function createSalesQuote() { FormCreateQuote.submit(); // to be implemented } document.addEventListener("DOMContentLoaded", function () { var myData = "@myData"; //window.close(); //alert(myData); @* if (myData) { alert("Sales Quote Created: "); } *@ }); </script> @* need to be considered : carditem's modelversionid can be different from cardtemplete's modelversionid or pageid's modelversion our design is, here at cardtemplete user can combine manny carditems with different CPQ model (modelversionid) *@ <form name="FormaddItem" id="FormaddItem" method="post" action=""> <input type="hidden" name="modelversionid" id="modelversionid" value="@(cardModelName)" /> <input type="hidden" name="cardaction" id="cardaction" value="addcardtoquote" /> <input type="hidden" name="salesQuoteNo" id="salesQuoteNo" value="@(modelController.SalesQuoteNo)" /> <input type="hidden" name="templateId" id="templateId" value="" /> </form> <form name="FormduplicateItem" id="FormduplicateItem" method="post" action=""> <input type="hidden" name="cardaction" id="cardaction" value="duplicatecarditem" /> <input type="hidden" name="itemdatakeyline" id="itemdatakeyline" value="" /> </form> <form name="FormdelItem" id="FormdelItem" method="post" action=""> <input type="hidden" name="cardaction" id="cardaction" value="deletecarditem" /> <input type="hidden" name="itemdatakeyline" id="itemdatakeyline" value="" /> </form> <form name="Formedititem" id="Formedititem" method="post" action=""> <input type="hidden" name="templatemodelversionid" id="modelversionid" value="@(cardModelName)" /> <input type="hidden" name="pageid" id="pageid" value="" /> <input type="hidden" name="cardaction" id="cardaction" value="editcarditem" /> <input type="hidden" name="salesQuoteNo" id="salesQuoteNo" value="@(modelController.SalesQuoteNo)" /> <input type="hidden" name="itemdatakeyline" id="itemdatakeyline" value="" /> </form> <form name="FormCreateQuote" id="FormCreateQuote" method="post" action=""> <input type="hidden" name="modelversionid" id="modelversionid" value="@(cardModelName)" /> <input type="hidden" name="cardaction" id="cardaction" value="createQuote" /> <input type="hidden" name="quote" id="quote" value="@(modelController.SalesQuoteNo)" /> </form> <div class="container mt-3"> @if( !string.IsNullOrEmpty(modelController.SalesQuoteNo) ) { <div class="action-bar"> @foreach(var item in card.Cards) { <button class="action" onclick="addcardtolist('@(item.TemplateId)')"> <span class="icon"> @if(item.Image != null && item.Image != ""){ <img src="@item.Image" width="50px" /> } </span> <span class="label">+ @item.Name</span> </button> } </div> } <div class="bc-list mb-3"> @if (carditems == null || !carditems.Any()) { <div class="bc-empty list-group-item text-center text-muted no-item">No Item</div> } else { @foreach (var key in carditems.Keys.ToList()) { Dictionary<string,object> citem = (Dictionary<string,object>)carditems[key]; string cpageid= citem.ContainsKey("pageid") ? citem["pageid"].ToString()! : ""; string description= citem.ContainsKey("itemlabel") ? citem["itemlabel"].ToString()! : ""; <div class="bc-row"> <span class="cell desc">@description</span> <span class="cell desc"> </span> <span class="cell desc"> </span> <span class="cell code"><button onclick="editcarditem('@(key)','@(cpageid)')">Edit</button></span> <span class="cell code"><button onclick="duplicateitem('@(key)')">Copy</button></span> <span class="cell code"><button onclick="deleteitem('@(key)')">Delete</button></span> </div> } } </div> <div class="ms-4 mt-8 text-end"> @if( !string.IsNullOrEmpty(modelController.SalesQuoteLineNo) ) { <button class="me-4 btn al-button-primary" onclick="window.close()">Close</button> } else { <button class="me-4 btn al-button-primary" onclick="createSalesQuote()">Create Sales Quote</button> <button class="me-4 btn al-button-primary" onclick="window.close()">Close</button> } </div> </div>