OleDBException error INSERT

Question

I build a c# application where you can login and can register a new account. But when I click on my button new account, and fill in the required fields I will get the following error:

vcom.ExecuteNonQuery(); -> OleDBException was unhandled. The instruction INSERT contains a syntaxerror.

See here below our code:

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

namespace Eindopdracht
{
    public partial class maak_account : Form
    {

        OleDbConnection vcon = new OleDbConnection(@"provider= microsoft.jet.oledb.4.0;data source=sample.mdb");

        public maak_account()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            OleDbConnection vcon = new OleDbConnection(@"provider= microsoft.jet.oledb.4.0;data source=sample.mdb");
            vcon.Open();

            string test = string.Format("insert into inlog (PASSWORD, Username, leeftijd, gewicht) VALUES ('" + textBox2.Text + "','" + textBox1.Text + "','" + textBox3.Text + "','" + textBox4.Text + "')");
            OleDbCommand vcom = new OleDbCommand(test, vcon);
            vcom.ExecuteNonQuery();
            MessageBox.Show("Uw gegevens zijn opgeslagen");
            vcom.Dispose();
        }
    }
}

Golden tip nr 1: use parameters instead of string concat to create the query. — Frederik Gheysels
– Frederik Gheysels, Commented Mar 27, 2013 at 10:57

John Woo · Accepted Answer · 2013-03-27 11:00:39Z

The reason for the syntax error is because Password, which happens to be the name of the column, is a reserved keyword.

insert into inlog ([PASSWORD], Username, leeftijd, gewicht)

MS Access Reserved Keywords

From MS Access Docs,

If a reserved word is already in use, you can avoid error messages by surrounding each occurrence of the word with brackets ([ ]). However, the best solution is to change the name to a nonreserved word.

To further improved the code,

use using statement to properly dispose object
use try-catch to properly handle exceptions
parameterized the values to avoid sql injection

example,

string connStr = @"provider= microsoft.jet.oledb.4.0;data source=sample.mdb";
string test = "insert into inlog ([PASSWORD], Username, leeftijd, gewicht) VALUES (?, ?, ?, ?)";

using(OleDbConnection vcon = new OleDbConnection(connStr))
{
    using(OleDbCommand vcom = new OleDbCommand(test, vcon))
    {
        vcom.Parameters.AddWithValue("PASSWORD", textBox2.Text);
        vcom.Parameters.AddWithValue("Username", textBox1.Text);
        vcom.Parameters.AddWithValue("leeftijd", textBox3.Text);
        vcom.Parameters.AddWithValue("gewicht", textBox4.Text);
        try
        {
            vcon.Open();
            com.ExecuteNonQuery();
        }
        catch(OleDbException ex)
        {
            // do something with the exception
        }
    }
}

Using Parameterized Query

Abbas · Accepted Answer · 2013-03-27 10:50:30Z

Use a try/catch block to catch the exception and see the errormessage. Also dispose of your resources in the finally block. The finally block will always be executed, even when an exception is thrown.

try
{
    OleDbConnection vcon = new OleDbConnection(@"provider= microsoft.jet.oledb.4.0;data source=sample.mdb");
    vcon.Open();

    string test = string.Format("insert into inlog (PASSWORD, Username, leeftijd, gewicht) VALUES ('" + textBox2.Text + "','" + textBox1.Text + "','" + textBox3.Text + "','" + textBox4.Text + "')");
    OleDbCommand vcom = new OleDbCommand(test, vcon);
    vcom.ExecuteNonQuery();
    MessageBox.Show("Uw gegevens zijn opgeslagen");
}
catch(Exception ex)
{
    MessageBox.Show(ex.Message);
}
finally
{
    vcom.Dispose();
    vcon.Close();
    vcon.Dispose();
}

Collectives™ on Stack Overflow

OleDBException error INSERT

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related