syntax error insert into database

Question

I have some problem when I want to insert data into database ACCESS using C#

The message error is:

System.data.OleDb.OleDbException (0x80040E14): error de syntaxe dans l'instruction INSERT INTO...........

Does someone know what the problem is?

Here is my code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Windows.Forms;

namespace First_cnx
{
    public partial class Form2 : Form
    {
        private OleDbConnection connection = new OleDbConnection();
        public Form2()
        {
            InitializeComponent();
            connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Reeda\Documents\Warface.accdb;
Persist Security Info=False;";
        }

        private void save_Click(object sender, EventArgs e)
        {
            try
            {
                connection.Open();
                OleDbCommand command = new OleDbCommand();
                command.Connection = connection;
                command.CommandText = String.Format(@"INSERT INTO [membre] (Player, Password, Gun, Claass) VALUES('" + player.Text + "', '" + password.Text + "', '" + gun.Text + "', '" + kind.Text + "')");

                command.ExecuteNonQuery();
                MessageBox.Show("Data Saved !");
                connection.Close();
            }
            catch (Exception ex) {
                MessageBox.Show("Error " + ex);
            }
        }
    }
}

[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, Dec 31 '14 at 10:31
Looks like you have *a syntax error* in the SQL: `Claass` instead of expected `Class` — Dmitry Bychenko, Dec 31 '14 at 10:41
possible duplicate of [C# Inserting Data from a form into an access Database](http://stackoverflow.com/questions/19275557/c-sharp-inserting-data-from-a-form-into-an-access-database) — CptVince, Dec 31 '14 at 11:28
Have you tryed to run your query directly in access? access error message aren't really good but by doing it in access you can be sure the query itself is good — Rémi, Jan 24 '15 at 04:09
@Reda in your class field in the UI, enter this: `whatever'); drop table membre; --`. This will show you what marc_s means. — jeroenh, Jul 22 '15 at 12:53

score 4 · Answer 1 · edited Jun 20 '20 at 09:12

4

Besides on your insert values, I think this happens because Password is a reserved keyword in OLE DB Provider. You should use it with square brackets like [Password]. The best solution is to change your column name to a non-reserved word.

But more important

You should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks. You don't need to use String.Format in your case as well since you didn't format your string.

Also use using statement to dispose your OleDbConnection and OleDbCommand.

using(OleDbConnection connection = new OleDbConnection(conString))
using(OleDbCommand command = connection.CreateCommand())
{
    // Set your CommandText property.
    // Define and add your parameter values.
    // Open your OleDbConnection.
    // Execute your query.
}

edited Jun 20 '20 at 09:12

Community

1
1

answered Dec 31 '14 at 11:03

Soner Gönül

97,193
102
206
364

I think he uses String.Format to avoid string Concatenation which would take more memory space and is slower. Even if this usage of String.Format isn't the usual way. – CptVince Dec 31 '14 at 11:21
1

@CptVince Argh, maybe.. He saved 3 concatenation, huh? :) Still think it is unnecessary. – Soner Gönül Dec 31 '14 at 11:35
i don't know if this is right but i use even for this little concatenation.[code]String.Format(@"INSERT INTO [membre] (Player, [Password], Gun, Claass) VALUES('{0}', '{1}', '{2}', '{3}')", player.Text, password.Text, gun.Text, kind.Text);[/code] With normal String Concatenation it would be with the + ", " + thing over 9 concatenations i think. – CptVince Dec 31 '14 at 13:49
1

@CptVince When you use parameterized queries, you will not need to format your string ;) Use them, **USE THEM ALWAYS**! – Soner Gönül Dec 31 '14 at 14:03
Jap sorry you are right i just thought about the concatenation. – CptVince Dec 31 '14 at 14:38

score 0 · Accepted Answer · answered Dec 31 '14 at 11:02

Password is a reserved words in Access. Try the Query like this:

command.CommandText = String.Format(@"INSERT INTO [membre] (Player, [Password], Gun, Claass) VALUES('" + player.Text + "', '" + password.Text + "', '" + gun.Text + "', '" + kind.Text + "')");

syntax error insert into database

2 Answers2

But more important