Exception thrown whilst inserting data into an access database using the command "insert into"

Question

I'm working on a project for a college course I'm currently on and I'm having some trouble trying to insert data into a database from an application developed in Visual Studio 2017. I've already connected to the database and am able to open it from the application.

The issue arises when I try to enter data into the table. I get a syntax error. Usually this means there an issue with the actual command right? Well i thought about this and created a new database, and another project and got the inert into command working for that one. At this point , a week of trying to get it to work, I'm stuck and desperately need help. Here is the code I'm using to try and add data to the table.

try 
{
     Form1.DataBaseConnection.Open();
} 
catch (Exception E) 
{
     MessageBox.Show("!!ATTENTION!! - Error accessing database. Please restart the application. ::::" + E.ToString());    
     Form1.DataBaseConnection.Close(); 
}

OleDbCommand DataBaseAddEntry = new OleDbCommand();
DataBaseAddEntry.Connection = Form1.DataBaseConnection;

DataBaseAddEntry.CommandText = "insert into Shoe(Size, Type, Name) values('" + int.Parse(TxtBoxSize.Text) + "','" + TxtBoxType.Text + "','" + TxtBoxName.Text + "')";

DataBaseAddEntry.ExecuteNonQuery();

Form1.DataBaseConnection.Close();
RefreshDataGridView();

As a side not, I didn't design the database or create it. I have to work in a team and the database was created by another member. So when I created my own database it worked fine. Could it be something to do with the actual database and not the code?

Size is a reserved keyword in MS-Access, you need square brackets around it. But you really need to change this code and use parameters when you create sql commands — Steve
– Steve, Commented Jun 2, 2019 at 18:58

Christian Phillips · Accepted Answer · 2019-06-02 19:17:22Z

1

Try removing the single quotes from the value for Size, as this looks like it should be an int types.

DataBaseAddEntry.CommandText = "insert into Shoe([Size], [Type], [Name]) values(" + int.Parse(TxtBoxSize.Text) + ",'" + TxtBoxType.Text + "','" + TxtBoxName.Text + "')";

Also, as the comment suggests, you should use parameterized SQL and consider wrapping the try block over the whole database operation, and not only the attempt to open.

edited Jun 2, 2019 at 19:17

answered Jun 2, 2019 at 19:12

Christian Phillips

18.8k8 gold badges55 silver badges85 bronze badges

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

1 Comment

Charlie Over a year ago

Thank you so much! The application is now doing exactly what it is meant to do. I did have a try catch block around the part where i tried to add an entry to the database but i removed it in the hope of receiving a little bit more information for the error. I'll also look into the parameterised SQL

Collectives™ on Stack Overflow

Exception thrown whilst inserting data into an access database using the command "insert into"

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related