SQL Incorrect syntax near '00'

Question

I get this error when i try to run my code

System.Data.SqlClient.SqlException: 'Incorrect syntax near '00'.'

I can't figure out the syntax error

using System;
using System.Data.SqlClient;
using System.Data.Sql;
using System.Windows.Forms;

namespace Barcode_Scanning
{
    public partial class AddForm : Form
        {
        SqlCommand cmd;
        SqlConnection con;
        SqlDataAdapter da;

        public AddForm()
        {
            InitializeComponent();
        }

        private void btnBack_Click(object sender, EventArgs e)
        {
            FormHandler.EditForm.Show();
            Hide();
        }
        protected override void OnClosed(EventArgs e)
        {
            Application.Exit();
            base.OnClosed(e);
        }
        private void btnAdd_Click(object sender, EventArgs e)
        {
            int quantity;
            int price;
            int barcodes;
            string name;
            DateTime date;

            name = tbxName.Text;
            date = Convert.ToDateTime(tbxDate.Text);
            barcodes = Convert.ToInt32(tbxBarcode.Text);
            quantity = Convert.ToInt32(tbxQuantity.Text);
            price = Convert.ToInt32(tbxPrice.Text);
            con = new SqlConnection("Data Source = (LocalDB)\\MSSQLLocalDB;     AttachDbFilename = \"C:\\Users\\hannes.corbett\\Desktop\\Barcode     Scanning\\Barcode Scanning\\BarcodeDB.mdf\"; Integrated Security = True");
            con.Open();
        cmd = new SqlCommand("INSERT INTO Products (Barcodes, Name, EDate, Quantity, Price) VALUES (" + barcodes + "," + name + "," + date + "," + quantity + "," + price + ")", con);

            cmd.ExecuteNonQuery();
            con.Close();

            tbxBarcode.Text = String.Empty;
            tbxName.Text = String.Empty;
            tbxDate.Text = String.Empty;
            tbxQuantity.Text = String.Empty;
            tbxPrice.Text = String.Empty;
        }
    }
}

I'm new to C# and to stackoverflow so forgive me for bad structure in my post :) Any tips on how to better my code would be very much appreciated! Thanks beforehand

Obligatory comment about building sql strings: https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work — user6144226, Sep 07 '17 at 06:48
Try to use the debugger to see the statement, and you may want to put `'` for string and date value. — Prisoner, Sep 07 '17 at 06:49
Maybe one of your text box values has a comma? Just use parametrized queries. — user6144226, Sep 07 '17 at 06:50
do you mean?cmd = new SqlCommand("INSERT INTO Products (Barcodes, Name, EDate, Quantity, Price) VALUES (" + barcodes + "," + name + "," + date + "," + quantity + "," + price + ")", con); — Hannes Corbett, Sep 07 '17 at 06:51

Tetsuya Yamamoto · Accepted Answer · 2017-09-07T07:02:26.527

Certainly you're missing single quotes which required at certain parts with string and DateTime values:

cmd = new SqlCommand(@"INSERT INTO Products (Barcodes, Name, EDate, Quantity, Price) VALUES (" + barcodes + ",'" + name + "','" + date + "'," + quantity + "," + price + ")", con);

I recommend you using parameterized query to avoid such hassle with concatenated query values:

using (var cmd = new SqlCommand(@"INSERT INTO Products (Barcodes, Name, EDate, Quantity, Price) VALUES (@Barcodes, @Name, @EDate, @Quantity, @Price)", con))
{
    cmd.Parameters.Add("@Barcodes", SqlDbType.Int).Value = barcodes;
    cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = name;
    cmd.Parameters.Add("@EDate", SqlDbType.DateTime).Value = date;
    cmd.Parameters.Add("@Quantity", SqlDbType.Int).Value = quantity;
    cmd.Parameters.Add("@Price", SqlDbType.Int).Value = price;
    cmd.ExecuteNonQuery();
}

score 3 · Answer 2 · answered Sep 07 '17 at 06:53

There must something in the input values that's making the SQL invalid. Use parameterized queries as given below :

private void btnAdd_Click(object sender, EventArgs e)
        {
            int quantity;
            int price;
            int barcodes;
            string name;
            DateTime date;

            name = tbxName.Text;
            date = Convert.ToDateTime(tbxDate.Text);
            barcodes = Convert.ToInt32(tbxBarcode.Text);
            quantity = Convert.ToInt32(tbxQuantity.Text);
            price = Convert.ToInt32(tbxPrice.Text);
            con = new SqlConnection("Data Source = (LocalDB)\\MSSQLLocalDB;     AttachDbFilename = \"C:\\Users\\hannes.corbett\\Desktop\\Barcode     Scanning\\Barcode Scanning\\BarcodeDB.mdf\"; Integrated Security = True");
            con.Open();
        cmd = new SqlCommand("INSERT INTO Products (Barcodes, Name, EDate, Quantity, Price) VALUES (@barcodes,@name,@date,@quantity,@price)", con);
            cmd.Parameters.AddWithValue("@barcodes",barcodes);
            cmd.Parameters.AddWithValue("@name", names);
            cmd.Parameters.AddWithValue("@date", date);
            cmd.Parameters.AddWithValue("@quantity",quantity);
            cmd.Parameters.AddWithValue("@price",price);
            cmd.ExecuteNonQuery();
            con.Close();

            tbxBarcode.Text = String.Empty;
            tbxName.Text = String.Empty;
            tbxDate.Text = String.Empty;
            tbxQuantity.Text = String.Empty;
            tbxPrice.Text = String.Empty;
        }

SQL Incorrect syntax near '00'

2 Answers2