ERROR [42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.

[Landley]

Hi All,

I have a "lovely" access database as my datasource. The table 4 columns (of
data types Number, Date/Time, Text, Text) and a number column is the primary
key.

I am using the command builder in ODBC objects to generate the INSERT,
UPDATE and DELETE commands.

The INSERT command generated looks like this:

"INSERT INTO my_table ( number_column, date_column, text_column1,
text_column2 ) VALUES ( ? , ? , ? , ? )"

which is what I expect. I have added a new row to my datatable. I then use
a dataadapter.Update to apply this change to the database.

This great exception occurs:

ERROR [42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in
INSERT INTO statement.

If this was happening across the board, then I would immediately think that
I am doing something wrong. But it's not, so I am not. This save routine
is generic and works for 4 other tables with similar structure. It takes a
table name as a parameter, does as SELECT * FROM the table name for the
SelectCommand and generates the other commands based on this.

Any ideas?

Landers

 

[Patrick Sullivan]

If this was done with SQL server, I would suggest using Profiler to see
what's actually happening with the insert command. But I don't think you can
use that with an Access table insert. What about stuffing the database into
SQL Server and then trying it? HTH

 

[Landley]

Thanks for the response.

I'll give it a go tomorrow. I'll try anything now. I just don't get how it
can work for one table and not the other.

Landers.

If this was done with SQL server, I would suggest using Profiler to see
what's actually happening with the insert command. But I don't think you can
use that with an Access table insert. What about stuffing the database into
SQL Server and then trying it? HTH

--

Patrick Sullivan, AA-BA, BA-IT

Hi All,

I have a "lovely" access database as my datasource. The table 4 columns (of
data types Number, Date/Time, Text, Text) and a number column is the primary
key.

I am using the command builder in ODBC objects to generate the INSERT,
UPDATE and DELETE commands.

The INSERT command generated looks like this:

"INSERT INTO my_table ( number_column, date_column, text_column1,
text_column2 ) VALUES ( ? , ? , ? , ? )"

which is what I expect. I have added a new row to my datatable. I then use
a dataadapter.Update to apply this change to the database.

This great exception occurs:

ERROR [42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in
INSERT INTO statement.

If this was happening across the board, then I would immediately think that
I am doing something wrong. But it's not, so I am not. This save routine
is generic and works for 4 other tables with similar structure. It

takes

a
table name as a parameter, does as SELECT * FROM the table name for the
SelectCommand and generates the other commands based on this.

Any ideas?

Landers

 

[Guest]

Landley,

Are the columns of the table really named number_column, date_column,
text_column1, text_column2?

If not, perhaps you are using a column name that is an Access reserved word.
Such names must be enclosed in square brackets.

Just a thought,

Kerry Moorman

 

[Landley]

Well done!

I had a column called "note". I renamed this, and all works fine.

I never knew that "note" was a reserved word. I am used to being pampered
by SQL server, where it puts the square brackets in for you!

Thanks again,

Landers

Landley,

Are the columns of the table really named number_column, date_column,
text_column1, text_column2?

If not, perhaps you are using a column name that is an Access reserved word.
Such names must be enclosed in square brackets.

Just a thought,

Kerry Moorman

Hi All,

I have a "lovely" access database as my datasource. The table 4 columns (of
data types Number, Date/Time, Text, Text) and a number column is the primary
key.

I am using the command builder in ODBC objects to generate the INSERT,
UPDATE and DELETE commands.

The INSERT command generated looks like this:

"INSERT INTO my_table ( number_column, date_column, text_column1,
text_column2 ) VALUES ( ? , ? , ? , ? )"

which is what I expect. I have added a new row to my datatable. I then use
a dataadapter.Update to apply this change to the database.

This great exception occurs:

ERROR [42000] [Microsoft][ODBC Microsoft Access Driver] Syntax error in
INSERT INTO statement.

If this was happening across the board, then I would immediately think that
I am doing something wrong. But it's not, so I am not. This save routine
is generic and works for 4 other tables with similar structure. It takes a
table name as a parameter, does as SELECT * FROM the table name for the
SelectCommand and generates the other commands based on this.

Any ideas?

Landers