locked
SqlException incorrect syntax near ')'. RRS feed

  • Question

  • User364161245 posted

    Hey I am new here and am looking for urgent answer I am getting SqlException incorrect syntax near ')'. unhandled exception and I don't know why! 

    my code is here 

    using System;
    using System.Collections;
    using System.Configuration;
    using System.Linq;
    using System.Data;
    using System.Web.UI;
    using System.Web.UI.WebControls;
    using Ykm.Entities.CMS;
    using Ykm.Entities.CMS.BasePages;
    using Ykm.Entities.Common;
    using Ykm.Utilities;
    using Ykm.Entities.Website;


    public partial class Mod_Reviews_index : CMSbasePage
    {
    private CMSUsers cUser;
    private string strTableName = "Reviews";
    private string[] arrSearchFields = { "ReviewID", "UserName", "Date" };
    private BaseEntity cBase = new Reviews();

    protected void Page_Init(object sender, EventArgs e)
    {
    cUser = CMSUsers.LoginFromSession();
    YkFormUtilities.DisablePageCache();

    if (cUser.Permission == CMSUsers.CmsPermissions.None)
    {
    Response.Redirect(ConfigurationManager.AppSettings["CmsLoginPage"]);
    }
    }

    protected void Page_Load(object sender, EventArgs e)
    {
    txtSearch.Attributes.Add("onKeyPress", "return CheckEvent13(event,frmPageSearch);");
    theBody.Attributes.Add("onload", "Init();");

    try
    {
    InitPage();
    InitPager();

    if (!IsPostBack)
    {
    if (!string.IsNullOrEmpty(Request.QueryString["sort"]))
    {
    hdnGridViewSortDirection.Value = Request.QueryString["sort"];
    }

    BindGrv();
    }
    }

    catch (Exception err)
    {
    YkFormUtilities.ExceptionHandler(err, ConfigurationManager.AppSettings["CmsErrorPage"]);
    }
    }

    protected void InitPage()
    {
    string[] ArrNameAndDescription = CMSModules.ReturnModuleNameDescription(YkStringUtilities.GetCurrentModule());

    if (ArrNameAndDescription != null)
    {
    litModName.Text = ArrNameAndDescription[0];
    litDescription.Text = ArrNameAndDescription[1];
    }
    }

    protected void InitPager()
    {
    if (!IsPostBack)
    {
    ddlPageSize.SelectedValue = cUser.CmsPageSize.ToString();
    }
    pager.PageSize = cUser.CmsPageSize;
    }

    protected void FillDDL()
    {

    }

    protected override void OnError(EventArgs e)
    {
    Exception EX = new Exception("<br>" + Server.GetLastError() + "<br>");
    YkFormUtilities.ExceptionHandler(EX, ConfigurationManager.AppSettings["CmsErrorPage"]);
    }

    protected void BindGrv()
    {
    string strWhereClause = arrSearchFields.Length > 0 ? hdnFilterString.Value + " AND " + hdnSearchString.Value : "(1=1)";
    DataTable dtItems = cBase.GetAllItemsForCMS(strTableName, pager.CurrentPageIndex + 1, Convert.ToInt32(ddlPageSize.SelectedValue), strWhereClause, hdnGridViewSortDirection.Value);
    pager.PageCount = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(cBase.GetCount(strTableName, strWhereClause)) / Convert.ToDouble(ddlPageSize.SelectedValue)));
    pager.BindData();
    grvItems.DataSource = dtItems;
    grvItems.DataBind();
    dtItems.Dispose();
    }

    protected void grvItems_Sorting(object sender, GridViewSortEventArgs e)
    {
    Response.Redirect("index.aspx?" + YkFormUtilities.BuildQS("sort", YkStringUtilities.BuildSortString(hdnGridViewSortDirection.Value, e.SortExpression), null));
    }

    protected void grvItems_RowCommand(object sender, GridViewCommandEventArgs e)
    {
    throw new NotFiniteNumberException();
    }

    protected void grvItems_RowDataBound(object sender, GridViewRowEventArgs e)
    {
    throw new NotFiniteNumberException();
    }

    protected void ddlPageSize_SelectedIndexChanged(object sender, EventArgs e)
    {
    cUser.CmsPageSize = Convert.ToInt16(ddlPageSize.SelectedValue);
    cUser.UpdateCmsPageSize();

    Response.Redirect("index.aspx?" + YkFormUtilities.BuildQS("page", "", null));
       }
    }

    and I am getting the exception here 

      DataTable dtItems = cBase.GetAllItemsForCMS(strTableName, pager.CurrentPageIndex + 1, Convert.ToInt32(ddlPageSize.SelectedValue), strWhereClause, hdnGridViewSortDirection.Value);

    any suggestions? :(

    Thursday, January 1, 2015 8:55 AM

Answers

  • User281315223 posted

    I am getting the query from metadata and it is looking like this: 

      public virtual DataTable GetAllItemsForCMS(string strTableName, int nPageIndex, int nNumOfRows, string strSearchPhrase, string strSortBy);

    But what does the actual query within that look like (e.g "SELECT * FROM YourTable WHERE X = Y"). You must be constructing the query within your GetAllItemsForCMS method and there is likely something wrong with how it is being constructed.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 1, 2015 9:40 AM
  • User753101303 posted

    So see the values for strTableName, strSearchPhrase, strSortyBy to see if they look correct.

    Asusming GetAllItemsForCMS is your own code, go see what is the final SQL string you are building to spot the problem within this SQL statement.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 1, 2015 9:47 AM

All replies

  • User281315223 posted

    A SQL Exception is going to be occurring due because of a syntax error within a query that you are building within your application. I don't currently see any within the code provided, so I am assuming that it is within one of your other methods (like "GetAllItemsForCMS").

    The only code that is SQL in nature is the following line :

    string strWhereClause = arrSearchFields.Length > 0 ? hdnFilterString.Value + " AND " + hdnSearchString.Value : "(1=1)";

    Have you tried placing a space before and after your "(1=1)" value that you are appending within the query :

    string strWhereClause = arrSearchFields.Length > 0 ? hdnFilterString.Value + " AND " + hdnSearchString.Value : " (1=1) ";

    The best way to troubleshoot this issue would be to place a breakpoint and see what your query looks like prior to executing it. This is going to be the best way to identify any major syntax issues and correct them.

    Additionally, you should not be directly concatenating values directly from input within your queries are you are currently doing. All SQL queries should be constructed using parameters to avoid issues with SQL Injection attacks.

    Thursday, January 1, 2015 9:10 AM
  • User753101303 posted

    Hi,

    This is a SQL Server syntax error in your SQL statement. Have you tried to check what is the SQL statement you are sending from your GetAllItemsForCMS method? Maybe the generated where clause is not correct?

    For now you have to show this SQL statement and the error should be then quite clearly visible.

    Thursday, January 1, 2015 9:10 AM
  • User364161245 posted

    I am getting the query from metadata and it is looking like this: 

      public virtual DataTable GetAllItemsForCMS(string strTableName, int nPageIndex, int nNumOfRows, string strSearchPhrase, string strSortBy);

    Thursday, January 1, 2015 9:28 AM
  • User281315223 posted

    I am getting the query from metadata and it is looking like this: 

      public virtual DataTable GetAllItemsForCMS(string strTableName, int nPageIndex, int nNumOfRows, string strSearchPhrase, string strSortBy);

    But what does the actual query within that look like (e.g "SELECT * FROM YourTable WHERE X = Y"). You must be constructing the query within your GetAllItemsForCMS method and there is likely something wrong with how it is being constructed.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 1, 2015 9:40 AM
  • User753101303 posted

    So see the values for strTableName, strSearchPhrase, strSortyBy to see if they look correct.

    Asusming GetAllItemsForCMS is your own code, go see what is the final SQL string you are building to spot the problem within this SQL statement.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 1, 2015 9:47 AM