Problem with INSERT INTO statement (Access and VB .NET)

[Christian Soltenborn]

Hi experts,

I am working on a university project based on VB .NET. I have a
connection to MS Access, and I want to insert some data into the
database. The sql command

INSERT INTO Shoe (Brand, Model, DateOfBuy, MaximumKilometers,
KilometersUsed, Comments, RunnerID) VALUES ('Whatever', 'Nike',
'12.04.2004', 400, 0, 'no comments :-)', 1)

works fine, but with

INSERT INTO TrainingUnit (RunnerID, Description, AvgHeartrate,
DateAndTime, FeelingID, MethodID, ShoeID, StrainID, Temperature, Time,
TrackID, WeatherID) VALUES (1, 'test', 132, '12.04.2004 17:45:51', 2, 2,
8, 2, 22, '00:00:00', 5, 2)

I get a "Syntaxfehler in der INSERT INTO-Anweisung." (german for "Syntax
error in INSERT INTO-statement.")
As far as I can see, the tables etc are fine.

Does anybody have an idea?

Thanks in advance,
Christian

[James Fortune]

Christian Soltenborn <no*****************@web.de> wrote in message news:<c5************@ID-36219.news.uni-berlin.de>...

Hi experts,

I am working on a university project based on VB .NET. I have a
connection to MS Access, and I want to insert some data into the
database. The sql command

INSERT INTO Shoe (Brand, Model, DateOfBuy, MaximumKilometers,
KilometersUsed, Comments, RunnerID) VALUES ('Whatever', 'Nike',
'12.04.2004', 400, 0, 'no comments :-)', 1)

works fine, but with

INSERT INTO TrainingUnit (RunnerID, Description, AvgHeartrate,
DateAndTime, FeelingID, MethodID, ShoeID, StrainID, Temperature, Time,
TrackID, WeatherID) VALUES (1, 'test', 132, '12.04.2004 17:45:51', 2, 2,
8, 2, 22, '00:00:00', 5, 2)

I get a "Syntaxfehler in der INSERT INTO-Anweisung." (german for "Syntax
error in INSERT INTO-statement.")
As far as I can see, the tables etc are fine.

Does anybody have an idea?

Thanks in advance,
Christian

It's possible that Access is getting confused by its Time() function.
I'd definitely rename that field. I also suggest that you start with
a small test table with one field and add one field at a time when its
SQL command is successful to isolate which field is causing the
problem. Also, does the Shoe table have a nice primary key that is
lacking in TrainingUnit? Your first test with one field should tell
you if this is an issue. The primary key is an issue with SQL Server
and perhaps VB.NET. In spite of the list you provide, Access
sometimes just tries to insert the values in the order they are listed
in the table design. Is the order of fields in Shoe the same as in
the table design? If so, how about for TrainingUnit?

Hope this helps,
James A. Fortune