Cannot connect MS Access to C#

Question

I am trying to create a sign-up for our movie database. I am trying to establish a connection from the MS Access that we made. But whenever I run my code, I get an error. I am using Visual Studio Express 2012 C#.

Why does my code trigger this error?

"An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll Additional information: Syntax error in INSERT INTO statement."

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

namespace BigScreen
{
public partial class sign_up : Form
{
    private OleDbConnection connection = new OleDbConnection();


    public sign_up()
    {
        InitializeComponent();

    }

    private void sign_up_Load(object sender, EventArgs e)
    {
        connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\raizel\Desktop\DataBase\Movie_Database.accdb;
        Persist Security Info=False;";

    }

    private void sign_up_FormClosed(object sender, FormClosedEventArgs e)
    {
        Form1 thisform = new Form1();
        thisform.Show();
    }

    }

    private void button1_Click(object sender, EventArgs e)
    {
            connection.Open();   
            OleDbCommand command = new OleDbCommand();
            command.Connection = connection;
            command.CommandText = "Insert into User ([firstname], [lastname], [username], [password]) values ('" + textBox2.Text + "','" + textBox5.Text + "','" + textBox4.Text + "','" + textBox1.Text + "')";

            command.ExecuteNonQuery();
            userID++;
            MessageBox.Show("Data Saved!");
            connection.Close();

    }
}

}

Perhaps your textboxes have junk/no data causing a syntax error. Btw, that code is *massively* open to SQL Injection — BradleyDotNET, Sep 29 '15 at 00:32
I'm pretty sure that I have inputted the values first, before I click save. — random, Sep 29 '15 at 00:41
Then check your full SQL string. The error you are getting is clear, there is a SQL syntax error in it. — BradleyDotNET, Sep 29 '15 at 00:42
I really can't seem to find it. This code worked perfectly before. — random, Sep 29 '15 at 00:46
I tried removing the single quotes, but it still has the same error — random, Sep 29 '15 at 00:50

score 4 · Answer 1 · answered Sep 29 '15 at 01:30

4

User is a reserved word. Bracket it like this to inform the db engine that word is an object name:

Insert into [User] ...

You would be wise to switch to a parameterized query as Bradley hinted but you still need to bracket the reserved word there, too.

answered Sep 29 '15 at 01:30

HansUp

95,961
11
77
135

Beyond SQL injection, using parameters will radically de-ugly your code. It's definitely worth a look. Here's a good example, and you can make it even cleaner using `AddWithValue`. http://stackoverflow.com/questions/5893837/using-parameters-inserting-data-into-access-database – Hambone Sep 29 '15 at 01:39
1

@Hambone Nice tip. I'll try it. – random Sep 29 '15 at 14:12

score 0 · Answer 2 · answered Sep 29 '15 at 01:51

User is a reserved word at leat in sql server. You have here in your code some things, you are using concatenation and this is making your code in danger of sql injection, the password can be in clear text in the database you need encryption.

You need to use using in your code to be sure the connection is closed after the insert is complete. If the error throw an exception the connection remain open and you will have problem in future attempts to execute anything. Close your db and make sure you can do an action using the designer and then check your code again.

Try to move to sql server ms access is not a good database for your system.

I made this example using your structure in sql fiddle

Cannot connect MS Access to C#

2 Answers2