locked
Incorrect Syntax near ',' RRS feed

  • Question

  • When i run this code it works fine, when i type in FilmID and FilmName and press Add to database it gives me an error Incorrect Syntax near ','

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using System.Data.SqlClient;

    namespace DataBaseTest2
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }

            private void Form1_Load(object sender, EventArgs e)
            {
                // TODO: This line of code loads data into the 'database1DataSet.Film' table. You can move, or remove it, as needed.
                this.filmTableAdapter.Fill(this.database1DataSet.Film);

            }

            private void btnAdd_Click(object sender, EventArgs e)
            {
                SqlConnection cn = new SqlConnection(global::DataBaseTest2.Properties.Settings.Default.Database1ConnectionString);
                try
                {
                    string sql = "INSERT INTO Film (FilmID,FilmName), values("+txtFilmId.Text+",'"+txtName.Text+"')";
                    SqlCommand exeSql = new SqlCommand(sql,cn);
                    cn.Open();
                    exeSql.ExecuteNonQuery();
                    MessageBox.Show("Film added sucsesfuly!", "Message", MessageBoxButtons.OK,MessageBoxIcon.Information);
                    this.filmTableAdapter.Fill(this.database1DataSet.Film);
                }
                catch(Exception ex)
                {
                    MessageBox.Show(ex.Message, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                finally
                {
                    cn.Close();
                }
            }

            private void btnRefresh_Click(object sender, EventArgs e)
            {
                this.filmTableAdapter.Fill(this.database1DataSet.Film);
            }
        }
    }

    Saturday, May 3, 2014 10:41 AM

Answers

  • Remove the comma after "values":

    string sql = "INSERT INTO Film (FilmID,FilmName) VALUES ('" + txtFilmId.Text + "','" + txtName.Text + "')";

    ..but you should use parameters:

    private void btnAdd_Click(object sender, EventArgs e)
            {
                SqlConnection cn = new SqlConnection(global::DataBaseTest2.Properties.Settings.Default.Database1ConnectionString);
                try
                {
                    string sql = "INSERT INTO Film (FilmID,FilmName) VALUES (@filmId, @filmName)");
                    SqlCommand exeSql = new SqlCommand(sql, cn);
                    exeSql.Parameters.AddWithValue("@filmId", txtFilmId.Text);
                    exeSql.Parameters.AddWithValue("@filmId",  txtName.Text);
                    cn.Open();
                    exeSql.ExecuteNonQuery();
                    MessageBox.Show("Film added sucsesfuly!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    this.filmTableAdapter.Fill(this.database1DataSet.Film);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                finally
                {
                    cn.Close();
                }
            }
    

    • Proposed as answer by BonnieBMVP Saturday, May 3, 2014 4:55 PM
    • Marked as answer by Anne Jing Monday, May 12, 2014 8:05 AM
    Saturday, May 3, 2014 4:35 PM

All replies

  • You are missing two single quotes

    string sql = "INSERT INTO Film (FilmID,FilmName), values('" + txtFilmId.Text + "','" + txtName.Text + "')";


    jdweng

    Saturday, May 3, 2014 2:56 PM
  • Still  give the same error Incorrect Syntax near ','
    Saturday, May 3, 2014 3:07 PM
  • Use SQL Server Management Studio (SSMS) and enter query into NEW QUERY window.  SSMS has better diagnostic messages than VS in determining for the syntax error.

    jdweng

    Saturday, May 3, 2014 3:24 PM
  • I know i did try SSMS but didn't manage to make it working, if you know any good tutorials on it i would be thankful
    Saturday, May 3, 2014 4:03 PM
  • Don't build your SQL with string concatenation, use parameters instead. It's just a little bit of extra code but it's also far, far more secure (significantly less risk of Sql Injection) and the database can also cache the query plan. It's the better way to go. And it will solve your issue with the single quote in the text.

    DevBiker (aka J Sawyer)
    Microsoft MVP - Sql Server (StreamInsight)


    Ruminations of J.net


    If I answered your question, please mark as answer.
    If my post was helpful, please mark as helpful.

    • Proposed as answer by BonnieBMVP Saturday, May 3, 2014 4:55 PM
    Saturday, May 3, 2014 4:10 PM
  • Remove the comma after "values":

    string sql = "INSERT INTO Film (FilmID,FilmName) VALUES ('" + txtFilmId.Text + "','" + txtName.Text + "')";

    ..but you should use parameters:

    private void btnAdd_Click(object sender, EventArgs e)
            {
                SqlConnection cn = new SqlConnection(global::DataBaseTest2.Properties.Settings.Default.Database1ConnectionString);
                try
                {
                    string sql = "INSERT INTO Film (FilmID,FilmName) VALUES (@filmId, @filmName)");
                    SqlCommand exeSql = new SqlCommand(sql, cn);
                    exeSql.Parameters.AddWithValue("@filmId", txtFilmId.Text);
                    exeSql.Parameters.AddWithValue("@filmId",  txtName.Text);
                    cn.Open();
                    exeSql.ExecuteNonQuery();
                    MessageBox.Show("Film added sucsesfuly!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    this.filmTableAdapter.Fill(this.database1DataSet.Film);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
                finally
                {
                    cn.Close();
                }
            }
    

    • Proposed as answer by BonnieBMVP Saturday, May 3, 2014 4:55 PM
    • Marked as answer by Anne Jing Monday, May 12, 2014 8:05 AM
    Saturday, May 3, 2014 4:35 PM