Saving records into database throws runtime error in ASP.Net C#

Question

I wonder why I got this runtime error by throwing this exception:

SqlException was unhandled by user code

Incorrect syntax near 'm'.
Unclosed quotation mark after the character string ')'.

When I used this code below to add records into my database when in fact I always used this code every time, now its not working.

I hope you can figure out the cause of this error. Thanks...Here's the code below:

protected void Button1_Click(object sender, EventArgs e)
{
   string conn = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Coldwind.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
   SqlConnection connection = new SqlConnection(conn);
   // SqlDataReader dr = new SqlDataReader();

   connection.Open();
   string sql = "INSERT INTO [CommentTab]([Name],[Comments]) Values('" + TextBox1.Text + "','" + TextBox2.Text + "')";
   SqlCommand cmd = new SqlCommand(sql, connection);
   cmd.CommandType = CommandType.Text;

   cmd.ExecuteNonQuery();
   cmd.Dispose();
   connection.Close();
   Response.Redirect("~/Default5.aspx");
}

What does actually contain your sql string when you execute this method? P.S. Use sql parameters instead of string concatenation. — Andrei Schneider
– Andrei Schneider, Commented Mar 24, 2013 at 12:59
can you post what are you entering inside textbox1 and textbox2; seems like a parsing issue. — Praveen Nambiar
– Praveen Nambiar, Commented Mar 24, 2013 at 13:07

evgenyl · Accepted Answer · 2013-03-24 12:59:35Z

2

I think the issue is in your text you are inserting. Can you post it? Also, I'd suggest to use Store Procedure and pass parameters instead.

answered Mar 24, 2013 at 12:59

evgenyl

8,1472 gold badges29 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

timmack Over a year ago

One of my textbox is set to multiline mode. Does this matter?

evgenyl Over a year ago

please, debug your code and see what actual string you built. If you cannot figure it out after this - please, update your post with full string.

timmack Over a year ago

I'm entering pure string values for TextBox1 which is name, and for textbox 2 the user can enter string or paragraph like he can make a comment

timmack Over a year ago

This code works fine in my other webpage and in this section of my page it's not. I wander why is it so?

evgenyl Over a year ago

Comments can contains multiple chracter that will break your sql query sintacsis. Consider to chagne your code to use parameter - you can see @Kaf example.

|

Indi · Accepted Answer · 2013-03-24 13:11:49Z

Your problem could be as you are directly passing user typed string. For example if user types something with single quote it will create an error.

Please avoid directly passing user typed strings with inline sql to the database. You are vulnerable to sql injection attacks. Use a parameterised query to make your query safe and error free. You could amend your code as below.

//Your code
connection.Open();
string sql = "INSERT INTO [CommentTab]([Name],[Comments]) Values(@username,@comments)";
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@username", TextBox1.Text);
cmd.Parameters.AddWithValue("@comments", TextBox2.Text);

cmd.ExecuteNonQuery();
cmd.Dispose();
connection.Close();
Response.Redirect("~/Default5.aspx");

Sachin · Accepted Answer · 2013-03-24 13:03:34Z

1

I think Your input contains quotation mark("'") itself. So better to replace them by double quotation mark like this

string Val1 =TextBox1.Text.Replace("'","''");

and then use this value in your query.

answered Mar 24, 2013 at 13:03

Sachin

41.1k7 gold badges94 silver badges107 bronze badges

2 Comments

timmack Over a year ago

Thank's guys I should use parameterized stored pro.@Sachin,thanks man you made my day your code works for me.Now I realize entering strings with single quote can ruin my codes. Thanks again

Sachin Over a year ago

Great!!! My solution has worked for you but yes @timmack You should use parameterized sql to avoid Sql injection. Thanks

Collectives™ on Stack Overflow

Saving records into database throws runtime error in ASP.Net C#

3 Answers 3

6 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related