Syntax error in INSERT INTO statement

Question

I wrote a program that connects to MS Access. When I fill in the fields and add a new item to Access the program fails. The exception is "Syntax error in INSERT INTO statement"

Here is the relevant code.

****************************************************************
AdoHelper.cs
****************************************************************

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace Yad2
{
    class AdoHelper
    {
        //get the connection string from the app.config file
        //Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Yad2.accdb
        static string connectionString = Properties.Settings.Default.DBConnection.ToString();

        //declare the db connection
        static OleDbConnection con = new OleDbConnection(connectionString);

        /// <summary>
        /// To Execute queries which returns result set (table / relation)
        /// </summary>
        /// <param name="query">the query string</param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string query)
        {

            try
            {
                con.Open();
                OleDbCommand command = new OleDbCommand(query, con);
                System.Data.OleDb.OleDbDataAdapter tableAdapter = new System.Data.OleDb.OleDbDataAdapter(command);
                DataTable dt = new DataTable();
                tableAdapter.Fill(dt);
                return dt;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            con.Close();
        }
    }

    /// <summary>
    /// To Execute update / insert / delete queries
    /// </summary>
    /// <param name="query">the query string</param>
    public static void ExecuteNonQuery(string query)
    {
        try
        {
            con.Open();
            System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(query, con);
            command.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            con.Close();
        }
    }

    /// <summary>
    /// To Execute queries which return scalar value
    /// </summary>
    /// <param name="query">the query string</param>
    public static object ExecuteScalar(string query)
    {
        try
        {
            con.Open();
            System.Data.OleDb.OleDbCommand command = new System.Data.OleDb.OleDbCommand(query, con);  /// here is the Excaption !!!!!!!!!
            return command.ExecuteScalar();
        }
        catch
        {
            throw;
        }
        finally
        {
            con.Close();
        }
    }

}
}

****************************************************************************


****************************************************************************
DataQueries.cs
****************************************************************************
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace Yad2
{
    class DataQueries
    {

        public static DataTable GetAllItems()
        {
            try
            {
                string query = "Select * from Messages";

                DataTable dt = AdoHelper.ExecuteDataTable(query);

                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public static void AddNewItem(string mesNumber, string title , string mesDate , string contactMail , string mesType , string Details )
        {
            string query = "Insert into Messages values(" + mesNumber + " , '" + title + "' , '" + mesDate + "' , '" + contactMail + "' , , '" + mesType + "' , '" + Details + "')";
            AdoHelper.ExecuteNonQuery(query);
       }

        public static void DeleteDept(int mesNumber)
        {
            string query = "Delete from Item where MessageNumber=" + mesNumber;
            AdoHelper.ExecuteNonQuery(query);
        }
    }
}
***********************************************************************************************

Why does the program fail?

Answer 1

When you put your string into the SQL, you get a invalid syntax.
This would happen if one of the strings contains a '.

You need to use parameters.

Also, your SQL contains , ,, which is invalid syntax.

Answer 2

Try this

INSERT INTO table (column1, column2, ...)
VALUES ('value1', 'value2', ...)

Answer 3

string query = "Insert into Messages values(" + mesNumber + " , '" + title + "' , '" + mesDate + "' , '" + contactMail + "' , , '" + mesType + "' , '" + Details + "')";

yields

Insert into Messages
values(
    <number> , 
    '<title>' , 
    '<mesDate>' , 
    '<contactMail>' , , 
    '<mesType>' , 
    '<Details>'
)

Notice the two commas after with a space between them. This is not valid SQL. You will also have a bad query if mesNumber is a null value in your code.

As Joe White commented with his link to XKCD #327, always sanitize your database inputs! That means that if a string is passed to your method, you must escape all single quotes.

As SLaks commented, never use throw ex;, just use throw;

Answer 4

Why don't you simply print out the value of query (to the debug window, console, message box, log file... wherever!), in AddNewItem, then examine the message. It really should become clear then.