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 - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection — marc_s
– marc_s, Commented Dec 31, 2014 at 10:31

Community · Accepted Answer · 2020-06-20 09:12:55Z

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, 2020 at 9:12

CommunityBot

11 silver badge

answered Dec 31, 2014 at 11:03

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

CptVince Over a year ago

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.

Soner Gönül Over a year ago

@CptVince Argh, maybe.. He saved 3 concatenation, huh? :) Still think it is unnecessary.

CptVince Over a year ago

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.

Soner Gönül Over a year ago

@CptVince When you use parameterized queries, you will not need to format your string ;) Use them, USE THEM ALWAYS!

CptVince Over a year ago

Jap sorry you are right i just thought about the concatenation.

CptVince · Accepted Answer · 2014-12-31 11:02:31Z

0

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 + "')");

answered Dec 31, 2014 at 11:02

CptVince

891 silver badge9 bronze badges

Collectives™ on Stack Overflow

syntax error insert into database

2 Answers 2

But more important

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

But more important

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related