OleDb Exception

Question

For 5 hour searching i can't find my mistake. I get this exception. What is wrong?

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in MyDictionary.exe

Additional information: Syntax error in INSERT INTO statement.

My code:

public void Insert(Word word)
{
    string language=FindLanguage();
    try
    {
        command.CommandText ="INSERT INTO "+language+" ( Native , Foreign , Definition , AddingDate)  values ( '" + word.Native + "' , '" + word.Foreign + "' , '" + word.Definition + "' ,'" + word.AddingDate + "')";
            command.CommandType = System.Data.CommandType.Text;
            connection.Open();

            command.ExecuteNonQuery();
    }
    catch (Exception)
    {
        throw;
    }
    finally
    {
        if (connection != null)
        {
            connection.Close();
        }
    }
}

Is there any more information in the exception, or any inner exceptions? What does the generated SQL statement look like? Does it work if executed from the Access IDE? — stuartd, Jul 21 '16 at 16:03
Use parameters to avoid sql injection and formatting errors. Also, try putting brackets around your language variable: `[" + language + "]` in case there is a space or conflicting character in there. — LarsTech, Jul 21 '16 at 16:19
Compose the string into a variable, so you can debug output the command string...then assign it to the CommandText. I bet you're getting some kind of unexpected syntax in your composed statement. — Clay, Jul 21 '16 at 16:24
In particular, if there is a single quote in any of your terms, that will cause invalid syntax. Your Definition column would be a could candidate for that happening. As suggested above, parameters make this problem go away. — John Prideaux, Jul 21 '16 at 16:39
Then try putting brackets around all your field names. Maybe Native, Definition, or Foreign is a keyword, etc. — LarsTech, Jul 21 '16 at 16:48
maybe offtopic: why are you making a table for each language? please show the value of command.CommandText — gsharp, Jul 21 '16 at 16:48
What is the run-time value of Command.Text when the exception is thrown? — John Prideaux, Jul 21 '16 at 16:54

nobody · Accepted Answer · 2016-07-21T17:05:50.177

1

You should use parameters in your insert statement.Also looks like you are missing command.Connection = connection;. Note that your SQL is prone for SQL Injection

command.CommandText ="INSERT INTO "+language+"([Native],[Foreign],[Definition],[AddingDate]) VALUES (@Native,@Foreign,@Definition,@AddingDate)";

command.Parameters.AddWithValue("@Native", word.Native);
command.Parameters.AddWithValue("@Foreign",word.Foreign);
command.Parameters.AddWithValue("@Definition",word.Definition);
command.Parameters.AddWithValue("@AddingDate",word.AddingDate);

command.CommandType = System.Data.CommandType.Text;
command.Connection = connection;
connection.Open();

command.ExecuteNonQuery();

edited Jul 21 '16 at 17:05

answered Jul 21 '16 at 16:29

nobody

10,892
8
45
63

Put brackets [] around your columns ([Native], [Foreign], ...). You might be running afoul of reserved words (especially Foreign). – John Prideaux Jul 21 '16 at 17:00
Just Worked ,Thanks – Ravil Yahyayev Jul 21 '16 at 17:03
@RavilYahyayev Great,Please mark it as answer if it helped – nobody Jul 21 '16 at 17:05
@JohnPrideaux Thanks,Edited. – nobody Jul 21 '16 at 17:05

Bozhidar Stoyneff · Answer 2 · 2016-07-21T16:44:33.023

In OleDb the correct syntax of the INSERT INTO statement involves usage of the SELECT clause even though you're appending static values. So you need to change your query like bellow example.

Further, don't construct try...catch..finally if you don't actually handle a raised exception. In the sake of disposal use using() { } block instead. So here it is:

public void Insert(Word word)
{
    string language=FindLanguage();

    using (var connection = new OleDbConnection("connection string goes here"))
    using (var command = new OleDbCommand...)
    {
        command.CommandText = @
            "INSERT INTO " + language + "(Native, Foreign, Definition, AddingDate)" +
            "SELECT '"
                + word.Native + "' AS Native, '" 
                + word.Foreign + "' AS Foreign, '" 
                + word.Definition + "' AS Definition, '"
                + word.AddingDate + "' AS AddingDate"
        ;

        connection.Open();

        command.ExecuteNonQuery();

        connection.Close();
    }
}

I've used VALUES (as the OP is) instead of SELECT and never had an issue. — John Prideaux, Jul 21 '16 at 16:41
That's funny. I always get this error when use `VALUES`. OleDb just don't get it; SQL server is fine with `VALUES`. — Bozhidar Stoyneff, Jul 21 '16 at 16:46

OleDb Exception

2 Answers2