Fixing “Unclosed quotation mark after the character string” error in ASPDOTNETSTOREFRONT

Fixing “Unclosed quotation mark after the character string” error in ASPDOTNETSTOREFRONT

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 resolving your issue with the store front. If you have any questions please leave a comment below. Happy coding! 🙂

About The Author

Joe Sumpter

6 Comments

  • articles of organization on January 19, 2011

    If you could e-mail me with a few suggestions on just how you made your blog look this excellent, I would be grateful.

  • matt on December 5, 2011

    Excellent information, and well written. This boolean logic information is really helpful thanks!

  • Jon on January 21, 2013

    I was working on a Joomla site and came up with the same error. After trying everything, I found your site and it led me in the right direction. Thanks so much!!

  • Joe Sumpter on January 30, 2013

    Glad to help Jon!

%d bloggers like this: