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?

"porgram"? "Accsses"? "excaption"? You should use spell-checker. — abelenky
– abelenky, Commented Dec 22, 2010 at 18:47
@abelenky: For some people, english is difficult. Hence the Edit button so we can help clean it up. — ChrisLively
– ChrisLively, Commented Dec 22, 2010 at 18:53
@Chris: ESL authors in particular should use spell-checkers. The computer does the spelling so we don't have to. — abelenky
– abelenky, Commented Dec 22, 2010 at 18:56

SLaks · Accepted Answer · 2010-12-22 18:47:06Z

4

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.

answered Dec 22, 2010 at 18:47

SLaks

891k182 gold badges1.9k silver badges2k bronze badges

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

Comments

edze · Accepted Answer · 2010-12-22 18:49:03Z

4

Try this

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

answered Dec 22, 2010 at 18:49

edze

3,0331 gold badge25 silver badges29 bronze badges

1 Comment

ChrisLively Over a year ago

+1 I suspect this is the primary issue. All of the columns should be explicitly defined when doing an insert. Otherwise it's brittle and likely to blow up when a DDL change occurs.

Thorin · Accepted Answer · 2010-12-22 18:56:30Z

1

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;

answered Dec 22, 2010 at 18:56

Thorin

6364 silver badges11 bronze badges

2 Comments

SLaks Over a year ago

No; just remove the catch block entirely.

Thorin Over a year ago

How right you are, SLaks, I never realized we could leave out the catch block. For those interested, here is what Microsoft says about it: "There are three possible forms of try statements: A try block followed by one or more catch blocks. A try block followed by a finally block. A try block followed by one or more catch blocks followed by a finally block." source: msdn.microsoft.com/en-us/library/aa664733(v=vs.71).aspx

abelenky · Accepted Answer · 2010-12-22 18:48:54Z

0

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.

answered Dec 22, 2010 at 18:48

abelenky

65k24 gold badges115 silver badges163 bronze badges

Collectives™ on Stack Overflow

Syntax error in INSERT INTO statement

4 Answers 4

Comments

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related