Adding rows to a SQL Server database from data entered by user in asp.net c#

Question

string ConnectionString = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection connection = new SqlConnection(ConnectionString);

SqlCommand cmd = new SqlCommand("INSERT INTO Data (Name, Sur-Name, Score,Avg) VALUES ('" + fName + "','" + sName + "','" + lblScore.Text + "','" + lblAvg.Text + "');");
cmd.CommandType = CommandType.Text;
cmd.Connection = connection;
cmd.Parameters.AddWithValue("@Name", fName);
cmd.Parameters.AddWithValue("@Sur-Name", sName);
cmd.Parameters.AddWithValue("@Score", lblScore.Text);
cmd.Parameters.AddWithValue("@Avg", lblAvg.Text);

try
{
    connection.Open();
    cmd.ExecuteNonQuery();
}
catch (Exception exc)
{
    lblData.Text = exc.Message;              
}
finally
{
    connection.Close();
}

The error I keep getting is a runtime saying

Incorrect syntax near '-'. Incorrect syntax near '-'.

I used the try catch just so page would load and my scores show but the label says this Incorrect syntax as well, I was wondering could anyone please help me with what I am doing wrong

Thanks.

Soner Gönül · Accepted Answer · 2014-03-26 15:49:03Z

I think Sur-Name breaks your query. Use it with square brackets like [Sur-Name]

But more important, please use parameterized queries. This kind of string concatenations are open for SQL Injection attacks. I see you tried to use but you never declare your parameter names in your query.

Also DATA might be a reserved keyword on future versions of SQL Server. You might need to use with also like [DATA]

Consider to use using statement to dispose your SqlConnection and SqlCommand.

using(SqlConnection connection = new SqlConnection(ConnectionString))
using(SqlCommand cmd = connection.CreateCommand())
{
   cmd.CommandText = @"INSERT INTO [Data] (Name, [Sur-Name], Score, Avg)
                       VALUES (@Name, @SurName, @Score, @Avg)";
   cmd.Connection = connection;
   cmd.Parameters.AddWithValue("@Name", fName);
   cmd.Parameters.AddWithValue("@SurName", sName);
   cmd.Parameters.AddWithValue("@Score", lblScore.Text);
   cmd.Parameters.AddWithValue("@Avg", lblAvg.Text);

   try
   {
        connection.Open();
        cmd.ExecuteNonQuery();
   }
   catch (Exception exc)
   {
        lblData.Text = exc.Message;
   }     
}

Community · Accepted Answer · 2017-05-23 12:05:21Z

3

You are trying to mix concatenated queries with parametrized. Always use parametrized queries, It will save you from SQL Injection.

SqlCommand cmd = new SqlCommand(@"INSERT INTO [Data] (Name, [Sur-Name], Score,Avg) VALUES (
                                  @Name, @SurName, @Score, @Avg)");
cmd.CommandType = CommandType.Text;
cmd.Connection = connection;
cmd.Parameters.AddWithValue("@Name", fName);
cmd.Parameters.AddWithValue("@SurName", sName);
cmd.Parameters.AddWithValue("@Score", lblScore.Text);
cmd.Parameters.AddWithValue("@Avg", lblAvg.Text);

Also consider enclosing your connection and command object in using statement.

As @Soner has mentioned in his answer, use Square brackets for Data and Sur-Name

edited May 23, 2017 at 12:05

CommunityBot

11 silver badge

answered Mar 26, 2014 at 15:23

Habib

224k30 gold badges420 silver badges446 bronze badges

Collectives™ on Stack Overflow

Adding rows to a SQL Server database from data entered by user in asp.net c#

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related