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

  • Thread starter Thread starter Landley
  • Start date Start date
L

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
 
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
 
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.

Patrick Sullivan said:
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

Landley said:
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
Click to expand...
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
Click to expand...
Click to expand...
 
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
 
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


Kerry Moorman said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top