• Share
  • Sharebar
  • Share

While working with ASPDOTNETSTOREFRONT there is a bug that causes an unhandled exception when performing product searches. This error is caused by entering a single quote in the search field. The single quote causes the SQL statement to close and thereby causes the unhandled exception. Here is an example of the error message:

Error Message

Page URL:/search.aspx
Source:.Net SqlClient Data Provider
Message:Incorrect syntax near ‘s’.
Unclosed quotation mark after the character string ”.
Stack Trace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
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)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
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 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at AspDotNetStorefrontCore.DB.ExecuteStoredProcReader(String StoredProcName, SqlParameter[] spa, SqlConnection dbconn)
at AspDotNetStorefront.ProductDetails.Logic.GetProductsByCriteria(String title, String keyword, String category, Decimal price, String searchBy)
at AspDotNetStorefront.search.SearchProducts()
at AspDotNetStorefront.search.btnSearch_OnClick(Object sender, EventArgs e)
at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

The fix:

In order to fix this issue, I modified the search.aspx.cs code behind page of the ASPDOTNETSTOREFRONT package as follows:

-
--Added Regular expressions class
using System.Text.RegularExpressions;

--Added replace statement to change single quotes to two single quotes
private void SearchProducts()
{
string title = String.Empty;
string keyword = String.Empty;
string category = String.Empty;
decimal price = 0;
string searchBy = ddlSearch.Items[ddlSearch.SelectedIndex].Value;

switch (searchBy)
{
case "T":
title = dstxtSearch.Text.Trim();
title = Regex.Replace(title, "'", "''");
break;
case "K":
keyword = dstxtSearch.Text.Trim();
keyword = Regex.Replace(title, "'","''");
break;
case "C":
category = dstxtSearch.Text.Trim();
category = Regex.Replace(title, "'", "''");
break;
case "P":
decimal.TryParse(dstxtSearch.Text.Trim(), out price);
break;
default:
break;
}

// search products
List products = ProductDetails.Logic.GetProductsByCriteria(title, keyword, category, price, searchBy);
if (products != null && products.Count > 0)
{
dgProducts.AllowPaging = (products.Count > dgProducts.PageSize);
dgProducts.DataSource = products;
dgProducts.DataBind();
pnlProductMatches.Visible = true;
pnlSearchResultNotFound.Visible = false;
}
else
{
dgProducts.DataSource = null;
dgProducts.DataBind();
pnlProductMatches.Visible = false;
pnlSearchResultNotFound.Visible = true;
}
}

The code above adds a regular expression that coverts the single quote to two single quotes. This allows the SQL statement to process without error.

Hope this helps with resolvng your issue with the store front.

Be Sociable, Share!