Incorrect syntax near 'an'

Question

3.00/5 (1 vote)

See more:

VS2010

, +

Hello,
I'm new to asp.net and sql, In my page i have a rich textbox named TextNews that allow the user to enter a text and save it to the database in a column named News , the data type is nvarchar(MAX). when i enter a short text (2 sentences), it works perfectly, but when i enter longer text ( 1 or 2 paragraphs) it give me this error:

CSS

Incorrect syntax near 'an'.
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'an'.
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

行 44: SqlCommand Mycommand = new SqlCommand(strCmd, connection);
行 45: int nResult;
行 46: nResult = Mycommand.ExecuteNonQuery();
行 47: ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('" + "News has been successfuly updated" + "');</script>");
行 48: connection.Close();

this is my code behind:

public partial class news_manager_upload_news : System.Web.UI.Page
{
    string connstring = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory\\travel.mdf;";
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Submit1_Click1(object sender, EventArgs e)
    {
        HttpFileCollection files = HttpContext.Current.Request.Files;
        string filename;
        for (int iFile = 0; iFile < files.Count; iFile++)
        {
            HttpPostedFile postFile = files[iFile];
            filename = System.IO.Path.GetFileName(postFile.FileName);
            if (filename != "")
            {
                postFile.SaveAs(Request.MapPath("~/upload_files/" + filename));
            }
            string conn = ConfigurationManager.ConnectionStrings["travelConnectionString"].ConnectionString;
            SqlConnection connection = new SqlConnection(conn);
            connection.Open();
            string strCmd = "insert into New values('"
                               + TextBox3.Text.Trim() + "','"
                               + TextBox2.Text + "','"
                               + TextBox1.Text + "','"
                               + TextNews.Text + "','"
                               + filename + "',"
                               + 0 + ","
                               + "getdate()" + ",'"
                               + DropDownList1.SelectedItem.Text + "',"
                               + 0
                               + ")"
                               ;
            SqlCommand Mycommand = new SqlCommand(strCmd, connection);
            int nResult;
            nResult = Mycommand.ExecuteNonQuery();
            ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('" + "News has been successfuly updated" + "');</script>");
            connection.Close();
        }
        TextBox1.Text = "";
        TextBox2.Text = "";
        TextBox3.Text = "";
        TextNews.Text = "";
    }
   
}

Posted 19-May-14 6:03am

Member 10704121

Add a Solution

Comments

[no name] 19-May-14 12:15pm

That is an error with your SQL so you need to examine the SQL that is being sent to the database to execute. The string concatenation is probably not working for you. You should probably try using a parameterized query.

Member 10704121 19-May-14 12:34pm

thank you for your answer.
can you please give me an example of how to use a parameterized query, so i can try.

[no name] 19-May-14 12:42pm

http://www.bing.com/search?q=c%23+parameterized+query

3 solutions

Solution 3

It is always recommended to use Stored procedures [^] instead of sql queries[^] . Since stored procedures are treated as compiled queries (whose execution plan is cached ) hence it is faster.

Posted 12-Aug-14 0:13am

pallelokanathareddy

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)

Sergey Alexandrovich Kryukov · Accepted Answer · 2014-05-19T06:43:00

To start with, composing a query by concatenating of the string with the strings taken from the UI is a big mistake. It wide the doors open to a well-known exploit called SQL injection:
http://en.wikipedia.org/wiki/SQL_injection[^].

This is how: http://xkcd.com/327[^].

You should use parametrized statements instead: http://msdn.microsoft.com/en-us/library/ff648339.aspx[^].

Please see my past answers for the explanation:
EROR IN UPATE in com.ExecuteNonQuery();[^],
hi name is not displaying in name?[^].

—SA

pallelokanathareddy · Accepted Answer · 2014-05-19T06:45:00

Instead of writing query as

SQL

insert into table_name values (10,'somevalue')

use query as

SQL

insert into table_name values (@fval,@secval) ;

Now use the parameterised query as follows .

C#

SqlConnection cn=new SqlConnection("connection string here ");
SqlCommand cmd=new SqlCommand("into table_name values (@fval,@secval)",cn);
SqlParamtere p1=new SqlParameter("@fval",sqldbtype.int);
p1.value=valuefromtextbox;
SqlParamtere p2=new SqlParameter("@secval",sqldbtype.varchar,20);
p2.value=valuefromtextbox;
cmd.parameters.add(p1);
cmd.parameters.add(p2);
cn.open();cmd.executenonquery();
cn.close();

Note: For observing what is going from ado.net into sqlserver use sql profiler tool .
Note: This code is not tested in visual studio .

Incorrect syntax near 'an'

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0