C# with SQL server syntax error

Question

So I have been looking at this for too long now and I am probably missing something But I cannot fathom what. I am receiving an error when trying to add a database entry in the third column titled "Unit Number" the error states that it is invalid syntax in this area when trying to add.

SqlConnection cn = new SqlConnection(global::ProjectAssessment.Properties.Settings.Default.Database1ConnectionString);
        try
        {
            string sql = "INSERT INTO Students (Student_Id,Student_name,Unit_number,Unit_grade) values(" +textBox1.Text+ ",'" +textBox2.Text+ ",'" +textBox3.Text+ ",'" +textBox4.Text+"')";
            SqlCommand exesql = new SqlCommand(sql, cn);
            cn.Open();
            exesql.ExecuteNonQuery();

            MessageBox.Show("Student record added", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
            this.studentsTableAdapter.Fill(this.database1DataSet.Students);
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
        finally
        {
            cn.Close();
        }

Habib · Accepted Answer · 2015-03-06 17:22:04Z

string sql = "INSERT INTO Students (Student_Id,Student_name,Unit_number,Unit_grade) 
     values(" +textBox1.Text+ ",'" +textBox2.Text+ ",'" +textBox3.Text+ ",'" +textBox4.Text+"')";

                                                //^^^^ and others

On a single look, one can see that you are missing single quotes in your values. You can fix them, but don't. Use SqlParameters, they will not only save you from errors like these but also save you from SQL Injection.

So your code should look like:

try
{
    using (SqlConnection cn = new SqlConnection(global::ProjectAssessment.Properties.Settings.Default.Database1ConnectionString))
    using (SqlCommand exesql = new SqlCommand(@"INSERT INTO Students (Student_Id,Student_name,Unit_number,Unit_grade) values(@studentID, @studentName, @unitNumber, @unitGrade)", cn))
    {
        exesql.Parameters.Add("@studentID", SqlDbType.Int).Value = textBox1.Text;
        exesql.Parameters.Add("@studentName", SqlDbType.VarChar).Value = textBox2.Text;
        //... and others

        cn.Open();
        exesql.ExecuteNonQuery();

    }

    MessageBox.Show("Student record added", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
    this.studentsTableAdapter.Fill(this.database1DataSet.Students);
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}

Enclosing your SqlConnection and SqlCommand object in using statement will ensure the disposal of resources. using will translate into try-finally block and in case of SqlConnection it will close/dispose the connection.

With the above code you don't need the finally block as connection will be diposed/closed after using statement's scope ends.

Copy/paste error: the cn object in the finally is now out of scope...so no finally required.
@ShellShock, thanks for pointing it out. Modified the answer.

Russell Uhl · Accepted Answer · 2015-03-06 16:36:41Z

string sql = "INSERT INTO Students (Student_Id,Student_name,Unit_number,Unit_grade) values(" +textBox1.Text+ ",'" +textBox2.Text+ ",'" +textBox3.Text+ ",'" +textBox4.Text+"')";

should be

string sql = "INSERT INTO Students (Student_Id,Student_name,Unit_number,Unit_grade) values(" +textBox1.Text+ ",'" +textBox2.Text+ "','" +textBox3.Text+ "','" +textBox4.Text+"')";

Please note that this is VERY unsafe, and prone to SQL injection! For instance, if somebody decided that the student id should be ;DROP TABLE STUDENTS; --, then your SQL statement would look like this:

INSERT INTO STUDENTS (Student_Id, Student_name, Unit_number, Unit_grade) values(;DROP TABLE STUDENTS; --, 'tb2val', 'tb3val', 'tb4val')

Depending on how things get executed, the first statement would throw a syntax error, the second statement (DROP TABLE STUDENTS;) would drop your table, and the rest would be treated as comments.

Whoops, there goes your students table!

You should use Habib's C# code to avoid this issue.

Thanks for the advice I actually didn't know that I would have left it open to injection there so I appreciate the tip :)

Collectives™ on Stack Overflow

C# with SQL server syntax error

2 Answers 2

2 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Related