Problems trying to insert data

[Jerry]

Help anyone,
I am new to C# and using databases. I am trying to insert information into a database using some code from Youtube. I was able to successfully select data from the database, but for whatever reason, when I try to execute the insert query (bolded and underlined below) below, my try catch block throws an exception related to a syntax error and referring to my insert statement:

namespace Movies
{
    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\Graham\Desktop\School.accdb;
                                          Persist Security Info=False;";
        }


        private void btn_save_Click(object sender, EventArgs e)
        {
            try
            {


               
                connection.Open();
                OleDbCommand command = new OleDbCommand();
                command.Connection = connection;
                //command.CommandText = "insert into students(Names, Age, Gpa) values('" + txt_name.Text +"','" + txt_age.Text + "','" + txt_gpa.Text + "')";
                [U][B]command.CommandText = "INSERT INTO students (Names, Age, Gpa) VALUES ('Abeni', '20', '3.2')";[/B][/U]
                int numRows = command.ExecuteNonQuery();
                MessageBox.Show("Data Saved");
                
            }
            catch(Exception excpt)
            {
                MessageBox.Show("Error" + excpt);
            }
        }
    }
}

 

[jmcilhinney]

Firstly, I have wrapped your code snippet in code formatting tags. As you can see, it makes it far more readable. Please do so whenever posting code snippets. Generally you should use:

[xcode]your code here[/xcode]

as it will perform syntax highlighting. If you want to add extra formatting though, like bold or underline, then use:

[code]your code here[/code]

You can type the tags by hand or use the tool bar on the advanced editor.

As for the issue, it's not immediately obvious to me what would cause a syntax error there. A common cause is column names that are reserved words or contain special characters like spaces. There's obviously no special characters in your column names and none that I would have thought was a reserved word. You can find a list of reserved words in ACE SQL online but you might also try escaping all your column names and if it works then you know that was the issue. Escaping a column name is done by wrapping it in brackets, e.g. "[Names], [Age], [Gpa]". There are various other things that can cause syntax errors but I don't see any evidence of them in your code.

One thing I do see though is the potential for data type mismatch errors. I would hope that, in your database, you have defined the Names column as a text data type and the Age and Gpa columns as numeric data types. In SQL code, single quotes are used to denote text, just as double quotes are used in C#. Just as you don't wrap numbers in double quotes in C#, you don't wrap numbers in single quotes in SQL.

It also looks like you have commented out your original code to test hard-coded values. Based on your original code, you need to learn how to use parameters in ADO.NET. That actually removes the possibility of syntax errors in some cases, e.g. if the name was to contain an apostrophe, but, most importantly, it protects against SQL injection. Without using parameters, it may be possible for a malicious user to delete the entire contents of your database. I suggest that you follow the Blog link in my signature below and check out my post on Parameters In ADO.NET.