Insert query for ms-access with Oledb object getting exception of not a valid query

Question

I'm using the following code and it is giving the invalid Insert command exception.

row the DataRow object to be added to the database , conn is the OleDBConnection object.

OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = `"Insert Into Appointments(ID,Body,End,Location,Start,Subject,Properties)"
                                                + "Values(@ID,@Body,@End,@Location,@Start,@Subject,@Properties)";


cmd.Parameters.Add("@ID", OleDbType.WChar).Value = row[0].ToString();
cmd.Parameters.Add("@Body", OleDbType.WChar).Value = row[1].ToString();
cmd.Parameters.Add("@End", OleDbType.Date).Value =  Convert.ToDateTime(row[2]).Date.ToLongDateString();
cmd.Parameters.Add("@Location", OleDbType.WChar).Value = row[3].ToString();
cmd.Parameters.Add("@Start", OleDbType.Date).Value = Convert.ToDateTime(row[4]).Date.ToLongDateString();
cmd.Parameters.Add("@Subject", OleDbType.WChar).Value = row[5].ToString();
cmd.Parameters.Add("@Properties", OleDbType.WChar).Value = row[6].ToString();


                conn.Open();                    
                cmd.ExecuteNonQuery();          //At this line exception is generating
                conn.Close();

Please help me in this.

Post the complete error message – Sandeep G B Sep 15 '11 at 07:07 — Sandeep G B, Sep 15 '11 at 07:07
{"Syntax error in INSERT INTO statement."} – VIKRAM Sep 15 '11 at 07:14 — VIKRAM, Sep 15 '11 at 07:14

score 3 · Accepted Answer · answered Sep 15 '11 at 09:18

You've got one (possibly more) reserved word in your table's field names.

The field name End ... at the very least.

Try

cmd.CommandText = `"Insert Into Appointments(ID,Body,[End],Location,Start,Subject,Properties)"
                                                + "Values(@ID,@Body,@End,@Location,@Start,@Subject,@Properties)";

score 0 · Answer 2 · answered Sep 15 '11 at 07:04

0

Does "Appointments" table support inserting ID? If ID column is the identity value, that may cause problem.

answered Sep 15 '11 at 07:04

denolk

765
14
27

ID is a primary key & it is of type Text but it might not create problem as it is from a dataset with same schema as database. – VIKRAM Sep 15 '11 at 07:20

score 0 · Answer 3 · answered Sep 15 '11 at 07:12

I think that the data-types that you use for your parameters are incorrect.

If your ID column is a numeric column, you shouldn't use OleDbType.WChar, but OleDbType.Integer, for instance

For alfanumeric-columns, I wouldn't use OleDbType.WChar either, but OleDbtype.VarChar.

See the OleDbType enumeration as well.

Insert query for ms-access with Oledb object getting exception of not a valid query

3 Answers3