Syntax error during inserting data using mysql

Question

Error:

Incorrect Syntax near 's'. unclosed quotation mark after the charater string ');'.

The code:

private void btnAdd_Click(object sender, EventArgs e)
{
    SqlConnection cn = new SqlConnection(global::CIMT.Properties.Settings.Default.Database2ConnectionString);

    try 
    {
        string sql = "INSERT INTO Students(Student_Id,First_Name,Last_Name,Fathers_Name,DOB,Mobile,Address,Post_Code) VALUES('"+this.txtId.Text+"','"+this.txtFName.Text+"','"+this.txtLName.Text+"','"+this.txtFaName.Text+"','"+this.txtDOB.Text+"','"+this.txtMob.Text+"','"+this.txtAddress.Text+"','"+this.txtPostCode.Text+ "');";
        SqlCommand exesql = new SqlCommand(sql, cn);
        cn.Open();
        exesql.ExecuteNonQuery();

        MessageBox.Show("Add new record done !!" , "Message" , MessageBoxButtons.OK , MessageBoxIcon.Information);
        this.studentsTableAdapter.Fill(this.database2DataSet.Students);
    }

    catch (Exception ex) 
    {
        MessageBox.Show(ex.Message , "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }

    finally 
    {
        cn.Close();
    }
}

This approach is vulnerable to SQL Injection. Please, for all our sakes, parameterize your queries. That'll almost definitely fix this syntax error, too. — Matthew Haugen
– Matthew Haugen, Commented Oct 5, 2014 at 8:52
Are you getting inputs as "hello's world" , then, it is not escaped, and you are getting error, so as suggested above ( SQL injection), it is better to go with parameterized queries. — Arindam Nayak
– Arindam Nayak, Commented Oct 5, 2014 at 8:53

meda · Accepted Answer · 2014-10-05 21:15:55Z

Use parametrized queries like the guys told you in the comments, not only it will avoids errors but it will also help you avoid SQL injection.

private void btnAdd_Click(object sender, EventArgs e)
{
    var cnString = global::CIMT.Properties.Settings.Default.Database2ConnectionString;
    using (SqlConnection cn = new SqlConnection(cnString))
    {
        try 
        {
            cn.Open();
            using (var exesql = new SqlCommand(
                      @"INSERT INTO Students(Student_Id
                                            ,First_Name
                                            ,Last_Name
                                            ,Fathers_Name
                                            ,DOB
                                            ,Mobile
                                            ,Address
                                            ,Post_Code) 
                        VALUES(@Student_Id
                                ,@First_Name
                                ,@Last_Name
                                ,@Fathers_Name
                                ,@DOB
                                ,@Mobile
                                ,@Address
                                ,@Post_Code);",
            cn))
            {
                exesql.Parameters.AddWithValue("@Student_Id", this.txtId.Text);
                exesql.Parameters.AddWithValue("@First_Name", this.txtFName.Text);
                exesql.Parameters.AddWithValue("@Last_Name",this.txtLName.Text );
                exesql.Parameters.AddWithValue("@Fathers_Name", this.txtFaName.Text);
                exesql.Parameters.AddWithValue("@DOB", this.txtDOB.Text);
                exesql.Parameters.AddWithValue("@Mobile", this.txtMob.Text);
                exesql.Parameters.AddWithValue("@Address", this.txtAddress.Text);
                exesql.Parameters.AddWithValue("@Post_Code", this.txtPostCode.Text);

                exesql.ExecuteNonQuery();

                MessageBox.Show("Add new record done !!" , "Message" , MessageBoxButtons.OK 
                                , MessageBoxIcon.Information);
                this.studentsTableAdapter.Fill(this.database2DataSet.Students);
            }
        }
        catch (Exception ex) 
        {
            MessageBox.Show(ex.Message , "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }
}

read up on SqlParameter Class

Collectives™ on Stack Overflow

Syntax error during inserting data using mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related