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.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_d8409812562745efa763f8c9c021a23c.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:52c1093d-0133-4c28-8ed2-fc9ddb2cf0dd
Error Number:156,State:1,Class:15

1 @inherits Dynamicweb.Rendering.ViewModelTemplate<Dynamicweb.Frontend.ParagraphViewModel> 2 @using Dynamicweb.Ecommerce.ProductCatalog 3 @using System 4 @using DW_CPQ_API 5 6 @{ 7 ModelController modelController = new ModelController(Model.PageID, Dynamicweb.Context.Current.Request.Params); // expected sales quote no 8 9 if(modelController!=null && string.IsNullOrEmpty( modelController.SalesQuoteNo)) 10 { 11 modelController.SalesQuoteNo="1144"; // to be removed later 12 } 13 14 System.Collections.Specialized.NameValueCollection? requestVars = Dynamicweb.Context.Current.Request.Params; 15 if(requestVars!=null && requestVars["quote"]!=null) 16 { 17 modelController.SalesQuoteNo=requestVars["quote"]!; 18 } 19 20 string theme = !string.IsNullOrWhiteSpace(Model.Item.GetRawValueString("Theme")) ? $"theme {Model.Item.GetRawValueString("Theme").Replace(" ", "").Trim().ToLower()}" : "alert-dark"; 21 22 var cpqPage = Dynamicweb.Content.Services.Pages.GetPage(Model.PageID); 23 string cpqTheme = cpqPage.Item["CPQ_Theme"]?.ToString() ?? "cpq-bold"; 24 25 string cardModelName = Pageview.Page.Item["CPQ_ModelVersionId"]?.ToString() ?? ""; 26 DW_CPQ_API.CPQCard card= new DW_CPQ_API.CPQCard(cardModelName); 27 28 string result=""; 29 if(requestVars!=null && requestVars["cardaction"]!=null && Dynamicweb.Context.Current.Request.HttpMethod == "POST") 30 { 31 if( requestVars["cardaction"].ToString()=="addcardtoquote") 32 { 33 34 string templateId=requestVars["templateId"]; 35 36 card.AddCardTempletetoQuote(modelController.SalesQuoteNo,templateId); 37 38 } 39 else if( requestVars["cardaction"].ToString()=="deletecarditem") 40 { 41 string itemdatakeyline=requestVars["itemdatakeyline"]; 42 CPQCard.DeleteCarditemInQuote(itemdatakeyline); 43 44 } 45 else if( requestVars["cardaction"].ToString()=="duplicatecarditem") 46 { 47 string itemdatakeyline=requestVars["itemdatakeyline"]; 48 CPQCard.DuplicateCarditemInQuote(itemdatakeyline); 49 50 } 51 else if( requestVars["cardaction"].ToString()=="createQuote") 52 { 53 result=CPQCard.CreateSalesQuoteFromCards(cardModelName,modelController.SalesQuoteNo); 54 Dynamicweb.Context.Current.Session["redirectdata"] = result; 55 56 } 57 58 Dynamicweb.Context.Current.Response.Redirect(Dynamicweb.Context.Current.Request.Url.AbsoluteUri); // to prevent resubmission when refreshing 59 return; 60 61 } 62 Dictionary<string,object> carditems =CPQCard.LoadCardItemsByQuote(cardModelName,modelController.SalesQuoteNo); 63 64 var targetPage = Dynamicweb.Content.Services.Pages 65 .GetPages() 66 .FirstOrDefault(p => 67 (p.NavigationTag?.Contains("cpqcustom") ?? false) && 68 (p.Item["CPQ_ModelVersionId"]?.ToString() ?? "") == cardModelName 69 ); 70 71 string editUrl = targetPage != null ? $"/Default.aspx?ID={targetPage.ID}" : "#"; 72 string? myData = Dynamicweb.Context.Current.Session["redirectdata"] as string; 73 myData = myData != null ? myData.Replace("\"", "\\\"") : ""; // Escape double quotes for JavaScript 74 Dynamicweb.Context.Current.Session.Remove("redirectdata"); 75 } 76 <script> 77 window.editUrl = "@editUrl"; 78 79 function addcardtolist(templateId) { 80 81 FormaddItem.templateId.value = templateId; 82 FormaddItem.submit(); 83 } 84 function editcarditem(keyline,pageid) { 85 Formedititem.itemdatakeyline.value = keyline; 86 Formedititem.action= "/Default.aspx?ID="+pageid; 87 Formedititem.submit(); 88 } 89 function deleteitem(keyline) { 90 FormdelItem.itemdatakeyline.value = keyline; 91 FormdelItem.submit(); 92 } 93 94 function duplicateitem(keyline) { 95 FormduplicateItem.itemdatakeyline.value = keyline; 96 FormduplicateItem.submit(); 97 } 98 99 function createSalesQuote() { 100 FormCreateQuote.submit(); 101 // to be implemented 102 } 103 document.addEventListener("DOMContentLoaded", function () { 104 var myData = "@myData"; 105 //window.close(); 106 //alert(myData); 107 @* if (myData) { 108 alert("Sales Quote Created: "); 109 110 } *@ 111 }); 112 </script> 113 114 @* need to be considered : 115 carditem's modelversionid can be different from cardtemplete's modelversionid or pageid's modelversion 116 our design is, here at cardtemplete user can combine manny carditems with different CPQ model (modelversionid) 117 *@ 118 119 <form name="FormaddItem" id="FormaddItem" method="post" action=""> 120 <input type="hidden" name="modelversionid" id="modelversionid" value="@(cardModelName)" /> 121 <input type="hidden" name="cardaction" id="cardaction" value="addcardtoquote" /> 122 <input type="hidden" name="salesQuoteNo" id="salesQuoteNo" value="@(modelController.SalesQuoteNo)" /> 123 <input type="hidden" name="templateId" id="templateId" value="" /> 124 </form> 125 <form name="FormduplicateItem" id="FormduplicateItem" method="post" action=""> 126 <input type="hidden" name="cardaction" id="cardaction" value="duplicatecarditem" /> 127 <input type="hidden" name="itemdatakeyline" id="itemdatakeyline" value="" /> 128 </form> 129 <form name="FormdelItem" id="FormdelItem" method="post" action=""> 130 <input type="hidden" name="cardaction" id="cardaction" value="deletecarditem" /> 131 <input type="hidden" name="itemdatakeyline" id="itemdatakeyline" value="" /> 132 </form> 133 <form name="Formedititem" id="Formedititem" method="post" action=""> 134 <input type="hidden" name="templatemodelversionid" id="modelversionid" value="@(cardModelName)" /> 135 <input type="hidden" name="pageid" id="pageid" value="" /> 136 <input type="hidden" name="cardaction" id="cardaction" value="editcarditem" /> 137 <input type="hidden" name="salesQuoteNo" id="salesQuoteNo" value="@(modelController.SalesQuoteNo)" /> 138 <input type="hidden" name="itemdatakeyline" id="itemdatakeyline" value="" /> 139 </form> 140 <form name="FormCreateQuote" id="FormCreateQuote" method="post" action=""> 141 <input type="hidden" name="modelversionid" id="modelversionid" value="@(cardModelName)" /> 142 <input type="hidden" name="cardaction" id="cardaction" value="createQuote" /> 143 <input type="hidden" name="quote" id="quote" value="@(modelController.SalesQuoteNo)" /> 144 145 </form> 146 <div class="container mt-3"> 147 @if( !string.IsNullOrEmpty(modelController.SalesQuoteNo) ) 148 { 149 <div class="action-bar"> 150 @foreach(var item in card.Cards) 151 { 152 <button class="action" onclick="addcardtolist('@(item.TemplateId)')"> 153 <span class="icon"> 154 @if(item.Image != null && item.Image != ""){ 155 <img src="@item.Image" width="50px" /> 156 } 157 </span> 158 <span class="label">+ @item.Name</span> 159 </button> 160 } 161 </div> 162 } 163 164 <div class="bc-list mb-3"> 165 @if (carditems == null || !carditems.Any()) 166 { 167 <div class="bc-empty list-group-item text-center text-muted no-item">No Item</div> 168 } 169 else 170 { 171 @foreach (var key in carditems.Keys.ToList()) 172 { 173 Dictionary<string,object> citem = (Dictionary<string,object>)carditems[key]; 174 string cpageid= citem.ContainsKey("pageid") ? citem["pageid"].ToString()! : ""; 175 string description= citem.ContainsKey("itemlabel") ? citem["itemlabel"].ToString()! : ""; 176 <div class="bc-row"> 177 <span class="cell desc">@description</span> 178 179 <span class="cell qty">qty</span> 180 <span class="cell code"><button onclick="editcarditem('@(key)','@(cpageid)')">Edit</button></span> 181 <span class="cell code"><button onclick="duplicateitem('@(key)')">Copy</button></span> 182 <span class="cell code"><button onclick="deleteitem('@(key)')">Delete</button></span> 183 </div> 184 } 185 } 186 </div> 187 188 <div class="ms-4 mt-8 text-end"> 189 @if( !string.IsNullOrEmpty(modelController.SalesQuoteLineNo) ) 190 { 191 <button class="me-4 btn al-button-primary" onclick="window.close()">Close</button> 192 } 193 else 194 { 195 <button class="me-4 btn al-button-primary" onclick="createSalesQuote()">Create Sales Quote</button> 196 <button class="me-4 btn al-button-primary" onclick="window.close()">Close</button> 197 } 198 </div> 199 </div>