While inserting data getting error as--> Unclosed quotation mark after the character string ',0)'. Incorrect syntax near ',0)'.

Question

0.00/5 (No votes)

See more:

ASP.NET

<ASP:DATAGRID id="dgMenuSubItems" ShowFooter="True"  runat="server" 
                    DataKeyField="SubItemId" AutoGenerateColumns="False"
                    CssClass="datagrid_style" CellPadding="3" Font-Bold="True" ForeColor="#000099" BackColor="White"
                    Width="100%" EnableViewState="True" AllowSorting="True" 
                    >
                    <HeaderStyle HorizontalAlign="Center" CssClass="datagrid_header_style" VerticalAlign="Middle"></HeaderStyle>
                    <SelectedItemStyle CssClass="datagrid_selecteditem_style"></SelectedItemStyle>
                    <itemstyle horizontalalign="Center" cssclass="datagrid_item_style" verticalalign="Middle"></itemstyle>
                    <alternatingitemstyle cssclass="datagrid_alternatingitemstyle"></alternatingitemstyle>
                    <columns>
                        <asp:templatecolumn headertext="SubItemName" sortexpression="SubItemName" xmlns:asp="#unknown">
                            <itemtemplate>
                                <ASP:Label ID="lblSubItemName"  runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.SubItemName","") %>'>
                                
                            </itemtemplate>
                            <footerstyle horizontalalign="Center"></footerstyle>
                            <footertemplate>
                                <asp:TextBox MaxLength="100" SkinID="Full" ID="txtSubItemNameF" Runat="server" Text="">
                            </footertemplate>
                            <edititemtemplate>
                                <asp:TextBox MaxLength="100" SkinID="Full" ID="txtSubItemNameE" Runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.SubItemName","") %>'>
                                
                            </edititemtemplate>
                        </asp:templatecolumn>
                        <asp:templatecolumn headertext="Price" sortexpression="SubItemPrice" xmlns:asp="#unknown">
                            <itemtemplate>
                                <ASP:Label ID="lblSubItemPrice"  runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.SubItemPrice","") %>'>
                                
                            </itemtemplate>
                            <footerstyle horizontalalign="Center"></footerstyle>
                            <footertemplate>
                                <asp:TextBox MaxLength="20" ID="txtSubItemPriceF" Runat="server" Text="">
                            </footertemplate>
                            <edititemtemplate>
                                <asp:TextBox MaxLength="20" ID="txtSubItemPriceE" Runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.SubItemPrice","") %>'>
                                
                            </edititemtemplate>
                        </asp:templatecolumn>


                         <asp:templatecolumn headertext="SortOrder" sortexpression="SubItemPrice" xmlns:asp="#unknown">
                            <itemtemplate>
                                <ASP:Label ID="lblSubItemSortOrder"  runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.SubItemSortOrder","") %>'>
                                
                            </itemtemplate>
                            <footerstyle horizontalalign="Center"></footerstyle>
                            <footertemplate>
                                <asp:TextBox MaxLength="10" ID="txtSubItemSortOrderF" Runat="server" Text="">
                            </footertemplate>
                            <edititemtemplate>
                                <asp:TextBox MaxLength="10" ID="txtSubItemSortOrderE" Runat="server" Text='<%# DataBinder.Eval(Container, "DataItem.SubItemSortOrder","") %>'>
                                
                            </edititemtemplate>
                        </asp:templatecolumn>



                        <asp:templatecolumn headertext="Modify" xmlns:asp="#unknown">
                            <itemtemplate>
                                <ASP:LinkButton ID="lnkSubEdit"  runat="server" Text="<img border=0 src=images/dg_Edit.gif alt=edit>"
                                    CommandName="Edit" CausesValidation="False">
                            </itemtemplate>
                            <edititemtemplate>
                                <ASP:LinkButton ID="lnkSubUpdate"  runat="server" Text="<img border=0 src=images/dg_OK.gif alt=save/update>"
                                    CommandName="Update" CausesValidation="False">  
                                <ASP:LinkButton ID="lnkSubCancel"  runat="server" Text="<img border=0 src=images/dg_Cancel.gif alt=cancel>"
                                    CommandName="Cancel" CausesValidation="False">
                            </edititemtemplate>
                        </asp:templatecolumn>
                        <asp:templatecolumn headertext="Remove" xmlns:asp="#unknown">
                            <itemtemplate>
                                <ASP:LinkButton ID="lnkSubDelete"  runat="server" Text="<img border=0 src=images/dg_Delete.gif alt=delete>"
                                    CommandName="Delete" CausesValidation="False">
                            </itemtemplate>
                            <footerstyle horizontalalign="Center"></footerstyle>
                            <footertemplate>
                                <asp:Button ID="btnSubAddRow" Runat="server" Text="Add New" CommandName="AddANewRow">
                            </footertemplate>
                        </asp:templatecolumn>
                    </columns>

C#

protected void dgMenuSubItems_ItemCommand(object source, DataGridCommandEventArgs e)
        {
            try
            {
                if (e.CommandName == "AddANewRow")
                {
                    string strItemId = this.dgMenuItems.DataKeys[this.dgMenuItems.SelectedIndex].ToString();
                    string strItem = "";
                    TextBox txtSubItemNameF = e.Item.FindControl("txtSubItemNameF") as TextBox;
                    string strPrice = "";
                    TextBox txtSubItemPriceF = e.Item.FindControl("txtSubItemPriceF") as TextBox;
                    if ((txtSubItemNameF != null) && (txtSubItemPriceF != null))
                    {
                        string strQueryMax = "Select Max(SubItemId) from tbl_MenuSubItems";
                        int nMaxItem = Convert.ToInt32(clsADO.getSingleRecord(strQueryMax));

                        strItem = txtSubItemNameF.Text;
                        strPrice = txtSubItemPriceF.Text;

                        strItem = strItem.Replace("''", "''");

                        string strQuery = "Insert into tbl_MenuSubItems values (" + (nMaxItem + 1) + ",'" + strItem + "','" + strPrice + "'," + strItemId + ")";

                        clsADO.executeNonQuery(strQuery);
                    }
                    else
                    {
                        lblError.Text = "Error finding the SubItem";
                    }

                    this.Rebuild_Sub_Display();
                }
            }
            catch (Exception ex)
            {
                lblError.Text = ex.Message;
            }
        }

Posted 27-Jan-16 3:18am

Member 12115746

Updated 27-Jan-16 3:37am

Jochen Arndt

v2

Add a Solution

Comments

F-ES Sitecore 27-Jan-16 10:11am

You'll get this if strItem contains an apostrophe. Use parameterised queries instead of building up the SQL the way you are. It handles this issues and protects you from SQL injection attacks as well.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jochen Arndt · Answer 1 · 2016-01-27T03:34:00

Solution 1

Check the created strQuery string.

This line should probably handle quote characters in the item string but will effectively do nothing (replaces with the same characters):

C#

strItem = strItem.Replace("''", "''");

[EDIT]
To perform the required replacement it should be:

C#

strItem = strItem.Replace("'", "''");

[/EDIT]

Posted 27-Jan-16 3:34am

Jochen Arndt

Updated 27-Jan-16 4:07am

v2

Comments

Member 12115746 27-Jan-16 9:38am

I tried doing so but it does not work

Jochen Arndt 27-Jan-16 9:43am

What did you try?
The replace function actually does nothing. What you probably want is replacing a single quote by two:
strItem = strItem.Replace("'", "''");

It would be also helpful for you and us when giving an example of strQuery that fails.

F-ES Sitecore 27-Jan-16 9:56am

You'll get this if strItem contains an apostrophe. Use parameterised queries instead of building up the SQL the way you are. It handles this issues and protects you from SQL injection attacks as well.

Jochen Arndt 27-Jan-16 10:05am

Regarding the single quote I realised that and he probably too but he passed a wrong parameter to the replace function. I suggested to check the string so that he can see what is going wrong.

Using parametrised queries is of course the way to do it.

F-ES Sitecore 27-Jan-16 10:11am

Sorry that comment was intended for the OP, not your solution! :o

Jochen Arndt 27-Jan-16 10:14am

No problem. Happened to me as well in the past.

Richard Deeming · Answer 2 · 2016-01-27T04:18:00

Your code is vulnerable to SQL Injection[^].

NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

By fixing this critical security vulnerability in your code, you will also fix the error.

C#

using (var connection = new SqlConnection("-YOUR CONNECTION STRING HERE-"))
using (var command = new SqlCommand("INSERT INTO tbl_MenuSubItems VALUES(1 + IsNull((SELECT Max(SubItemId) FROM tbl_MenuSubItems), 0), @Item, @Price, @ItemId)"))
{
    command.Parameters.AddWithValue("@Item", txtSubItemNameF.Text);
    command.Parameters.AddWithValue("@Price", txtSubItemPriceF.Text);
    command.Parameters.AddWithValue("@ItemId", dgMenuItems.DataKeys[dgMenuItems.SelectedIndex]);
    
    connection.Open();
    command.ExecuteNonQuery();
}

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]
SQL injection attack mechanics | Pluralsight [^]