Syntax error in INSERT statement - C#, Access 2013, VS Express 2013

Question

I have been trying to figure out why my program keeps giving me error. system.data.oledb.oledbexception(0x80040E14): Syntax error in INSERT INTO statement.

Table name: User

Columns:

Username
AccountNumber
FirstName
LastName

Code:

namespace Library_System
{
    public partial class CreateAccountWindow : Form
    {
        OleDbConnection connect = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data         Source=C:\Users\Jc\Documents\Visual Studio 2013\Projects\Library System\Library     System\LibrarySystemDatabase.accdb;Persist Security Info=False;");
    OleDbCommand command = new OleDbCommand();
    //OleDbDataReader reader;

    public CreateAccountWindow()
    {
        InitializeComponent();
    }

    private void button2_Click(object sender, EventArgs e)
    {
        string Username = "", AccountNumber = "", FirstName = "", LastName = "";
        //int Borrowed = 0;
        bool hasValue1 = false, hasValue2 = false, hasValue3 = false, hasValue4 = false;
        if (textBox1.Text != "")
        {
            label1.Hide();
            Username = textBox1.Text;
            hasValue1 = true;
        }
        else
        {
            label1.Show();
            label1.Text = "Required";
        }

        if (textBox10.Text != "")
        {
            label21.Hide();

            AccountNumber = textBox8.Text;
            hasValue2 = true;
        }
        else
        {
            label21.Show();
            label21.Text = "Required";
        }

        if (textBox8.Text != "")
        {
            label13.Hide();

            FirstName = textBox10.Text;
            hasValue3 = true;
        }
        else
        {
            label13.Show();
            label13.Text = "Required";
        }

        if (textBox7.Text != "")
        {
            label12.Hide();
            label12.Text = "Required";
            LastName = textBox7.Text;
            hasValue4 = true;
        }
        else
        {
            label12.Show();
            label12.Text = "Required";
        }

        if (hasValue1 || hasValue2 || hasValue3 || hasValue4)
        {
            try
            {
                connect.Open();
                OleDbCommand command = new OleDbCommand();
                command.Connection = connect;
                command.CommandText = "insert into User (Username,AccountNumber,FirstName,LastName) values ('" + Username + "','" + AccountNumber + "','" + FirstName + "','" + LastName + "')";
                command.ExecuteNonQuery();
                MessageBox.Show("REGISTRATION COMPLETE !!", "DONE");
                connect.Close();
            }
            catch (Exception ex)
            {
                connect.Close();
                MessageBox.Show("Error:"+ex.ToString());
            }
        }
    }
}

[SQL Injection alert](http://msdn.microsoft.com/en-us/library/ms161953%28v=sql.105%29.aspx) - you should **not** concatenate together your SQL statements - use **parametrized queries** instead to avoid SQL injection — marc_s, Sep 07 '14 at 09:07
Sorry but can you be more specific. I have just started c# for 3 weeks. I dont understand too technical terminologies — JC Balantakbo, Sep 07 '14 at 09:19
@JCBalantakbo what marc_s meant was that you shouldn't create SQL queries by concatenating strings. For example if your Username was something like `a','b','c','d');drop table User;--` think what will be executed in the database. A malicious user might give that as a username to hack your system. — Can't Tell, Oct 21 '14 at 10:50

Gord Thompson · Answer 1 · 2014-09-07T11:42:08.113

You are getting the error because USER is a reserved word in Access SQL so you need to enclose the table name in square brackets. Also, as marc_s mentioned in a comment to the question, you should be using a parameterized query like this:

// test data
string Username = "gord";
string AccountNumber = "gt001";
string FirstName = "Gord";
string LastName = "Thompson";

command.CommandText =
        "INSERT INTO [User] (Username, AccountNumber, FirstName, LastName) VALUES (?,?,?,?)";
command.Parameters.AddWithValue("?", Username);
command.Parameters.AddWithValue("?", AccountNumber);
command.Parameters.AddWithValue("?", FirstName);
command.Parameters.AddWithValue("?", LastName);
command.ExecuteNonQuery();

score 0 · Answer 2 · answered Sep 07 '14 at 09:14

0

You will need a UNIQUE field as primary key. Fields that generate numbers automatically in Access

SelectCommand = "select @@IDENTITY";
var id = (int)SelectCommand.ExecuteScalar();

answered Sep 07 '14 at 09:14

Hamix

1,323
7
18

The error has nothing to do with a primary key, and there's no reason why [Username] can't be the primary key for that table. – Gord Thompson Sep 07 '14 at 11:40
You should check it manually for replicate.You can not enter duplicate name in primary key fields. – Hamix Sep 07 '14 at 12:06
Yes, but trying to insert a duplicate primary key value will not result in the "Syntax error in INSERT INTO statement" error cited in the question. Instead, such an action would trigger "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship." – Gord Thompson Sep 07 '14 at 12:59
@Gord Thompson, The correct syntax, I think that is the wrong data type.Maybe ``AccountNumber`` a numeric field.That it must be changed to a ``Text`` or ``Memo`` type. – Hamix Sep 07 '14 at 13:11

score 0 · Answer 3 · edited May 23 '17 at 12:21

did you try to run the insert query directly on db console? in your case its access. you might be having some spelling mistake. you have initialized oledbcommand object twice. you might not require to initialize it just after oledbconnection object.

also regarding the approach you can use the validators; check this link How to: Validate Data

for oledbcommand parameters check below links:

OleDBParams

Update Data using OLEDB

Insert data using OLEDB

Syntax error in INSERT statement - C#, Access 2013, VS Express 2013

3 Answers3