I guess this is a basic question, but that is where I am! I have a item with an apostrophe or single quote in it like Leo's. When this is passed in the SQL query -- I get a sql exception.
Here are my queries in the code behind. Would you be kind enough and take a look to see how I can avoid thia problem without having to take the single quote off of Leo's. In the code behind, Leo's is a **************** Manufacturer. Thanks very much.
___________________________
public void Page_Load(object sender, System.EventArgs e)
        {


            // Put user code to initialize the page here
            if (!IsPostBack)
            {
                query = "select distinct ShoeManufacturer from Shoes";
                typesList.DataSource = fetchReader(query, "shoes");
                typesList.DataBind();

                getSubTypes (null, null);


            }
            else category = subtypeslist.SelectedItem.Value;




        }

        public void getSubTypes(object src, EventArgs e)
        {
            query = "select distinct ShoeModel from Shoes where ShoeManufacturer='" +
                typesList.SelectedItem.Value + "'";
            subtypeslist.DataSource = fetchReader(query, "shoes");
            subtypeslist.DataBind();

            subtypeslist.SelectedIndex = 0;
            category = subtypeslist.SelectedItem.Value;
            getFirstPage (null, null);
        }

        public void getFirstPage (object src, EventArgs e)
        {
            myGrid.VirtualItemCount = countRows();

            myGrid.CurrentPageIndex = 0;
            ViewState [ "topID" ] = "";
            ViewState [ "endID" ] = "";

            query = "select top " + myGrid.PageSize + " * from Shoes where ShoeModel='" + category + "' order by ShoeID";
            bindGrid();
        }

        public void setPage (object src, DataGridPageChangedEventArgs e)
        {
            if (e.NewPageIndex == myGrid.CurrentPageIndex +1)
            {
                query = "select top " + myGrid.PageSize + " * from Shoes where ShoeModel='" + category + "' and ShoeID > '" +
                    ViewState [ "endID" ] + "' order by ShoeID";
            }
            else
            {
                query = "select top " + myGrid.PageSize + " * from Shoes where ShoeModel='" + category + "' and ShoeID < '" +
                    ViewState[ "topID" ] + "' order by ShoesID desc";
            }
            myGrid.CurrentPageIndex = e.NewPageIndex;
            bindGrid();
        }

        public void bindGrid()
        {
            myGrid.DataSource = fetchView();
            myGrid.DataBind();
            lblTracker.Text = "Page " + (myGrid.CurrentPageIndex+1) + " of " + myGrid.PageCount;


        }

        DataView fetchView()
        {
            DataTable dataSegment = fetchData (query, "shoes").Tables [ 0 ];

            DataRow [ ] currentRows = dataSegment.Select ("", "ShoeID");
            ViewState [ "topID" ] = currentRows [ 0 ] ["ShoeID"];
            ViewState [ "endID" ] = currentRows [ currentRows.Length-1 ] ["ShoeID"];

            DataView gridView = dataSegment.DefaultView;
            gridView.Sort = "ShoeID";

            return gridView;
        }

        public int countRows()
        {
            SqlConnection cnShoes = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);

            query = "select Count (*) from Shoes where ShoeModel='" + category + "'";
            SqlCommand cmdCount = new SqlCommand(query, cnShoes);
            cnShoes.Open();
            rowCount = (int) cmdCount.ExecuteScalar();
            cnShoes.Close();
            return rowCount;
        }
}
}

Hello,

In SQL, to represent a single-quote, you have to use two single-quotes, so:

Leo's

would have to be represented as:

Leo''s

in the SQL query. For example, if the category has a single quote in it, you need to do:

ShoeModel='" + category.Replace("'", "''") + "'

Brian

Hi Brian
Thanks for your suggestion. I hate to bother your again -- but would it be possible for you to elaborate a little bit more -- are there two double quotes around the single quotes in the parenthesis ("'", "'") -- . In any case, I am going to try your suggestion and post the result. Thanks. Shailesh.

Shailesh,

In brian's suggested solution, it is like this.

where the red marked are single quotes and blue marked is what you got to change in addition to your code.

Just on this page the single and double quotes are displayed in a way it can't be differentiated and formatting them would make a little difference. Anyway, hope a copy/paste of that into your code window would have cleared your doubt.

Cheers!

_________________________
- Vijay G
Strive for Perfection

Hi Vijay, that's perfect. I will try it and post the result -- many thanks. Shailesh.

Thanks very much Vijay: to put it simply - it is working! i needed to make the same change in
where ShoeManufacturer='" +
                typesList.SelectedItem.Value.Replace("'", "''") + "'";
besides making this changes in category.Replace(",", "''")+ "'";
elsewhere in my code. Thanks for having regard for my very basic skills and coming down to my level -- I tried following the Books online for escape character, but was limited in my understanding and could not implement it.

Thanks Brian for making the initial beachhead into this situation. Shailesh.