Inserting data into MS Access table using c#

Question

Can anyone please tell me whats wrong with this code? Thanks in advance.

myConnection.ConnectionString = myConnectionString;
myConnection.Open();
OdbcCommand command = myConnection.CreateCommand();
command.CommandText = 
     "INSERT INTO Table1(type, from, to, depart, arrival, remain) VALUES(?, ?, ?, ?, ?, ?)";

TimeSpan time = new TimeSpan(10, 7, 00);
DateTime t = new DateTime(2016, 5, 5, 5, 4, 3);
command.Parameters.AddWithValue("@type", "test");
command.Parameters.AddWithValue("@from", "tet");
command.Parameters.AddWithValue("@to", "te");
command.Parameters.AddWithValue("@depart", t);
command.Parameters.AddWithValue("@arrival", t);
command.Parameters.AddWithValue("@remain", 4);

command.ExecuteNonQuery();

Generally your code looks ok. So what's wrong with it? Are you getting an exception or some unexpected result? — Andrey Korneyev
– Andrey Korneyev, Commented Nov 1, 2016 at 12:12
I am getting this exception: ERROR [42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. — Wagner'P
– Wagner'P, Commented Nov 1, 2016 at 12:13
Still getting the same error - what error is that then? Do you not think that might be a useful piece of information? — tom redfern
– tom redfern, Commented Nov 1, 2016 at 13:22

Andrey Korneyev · Accepted Answer · 2016-11-01 12:30:51Z

2

You're using Access reserved words type and from as column names in your table.

This can be a reason why you're getting such an error.

You can wrap such a column names with square brackets and change your command text to:

INSERT INTO Table1([type], [from], to, depart, arrival, remain) VALUES(?, ?, ?, ?, ?, ?)

answered Nov 1, 2016 at 12:30

Andrey Korneyev

26.9k16 gold badges73 silver badges72 bronze badges

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

3 Comments

Andrey Korneyev Over a year ago

@Wagner'P If answer was helpful to you - you can mark it as accepted. This can indicate possible solution to someone who will experience such a problem in future. See stackoverflow.com/help/someone-answers for details.

Wagner'P Over a year ago

How can I do that?

Andrey Korneyev Over a year ago

@Wagner'P well, it was described in the article I've linked in previous comment: "To mark an answer as accepted, click on the check mark beside the answer to toggle it from greyed out to filled in"

Latha Ponselvan · Accepted Answer · 2016-11-01 12:23:11Z

This is coding for web application insertion function:

Coding:

    string dataPath = Server.MapPath("Your DataPath");
    string ConString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + dataPath + ";Persist Security Info=True";            
    OleDbConnection Con = new OleDbConnection(ConString);
    OleDbCommand Command = Con.CreateCommand();
    Con.Open();
    Command.CommandText = "Insert into Table1(type, from, to, depart, arrival, remain) Values (@type, @from, @to, @depart, @arrival, @remain);      
    TimeSpan time = new TimeSpan(10, 7, 00);
    DateTime t = new DateTime(2016, 5, 5, 5, 4, 3);
    command.Parameters.AddWithValue("@type", "test");
    command.Parameters.AddWithValue("@from", "tet");
    command.Parameters.AddWithValue("@to", "te");
    command.Parameters.AddWithValue("@depart", t);
    command.Parameters.AddWithValue("@arrival", t);
    command.Parameters.AddWithValue("@remain", 4);
    command.ExecuteNonQuery();
    Con.Close();

Collectives™ on Stack Overflow

Inserting data into MS Access table using c#

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related