CODEWITHSUNDEEP
c#ms-accessoledboledbexception
Ravil Yahyayev
Ravil Yahyayev

Reputation: 31

OleDb Exception

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();
        }
    }
}

Upvotes: 2

Views: 839

Answers (2)

nobody
nobody

Reputation: 11080

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();

Upvotes: 1

Bozhidar Stoyneff
Bozhidar Stoyneff

Reputation: 3634

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();
    }
}

Upvotes: 0

Related Questions