SQL Server - INSERT INTO STATEMENT

Asked By Angelo Munoz on 04-Aug-05 02:29 PM
I have a SQL server backend and am using MS Access as the front end DB. 
I'm trying to run a INSERT INTO statement from the Visual Basic editor. However, it says I have a syntax error in my statement. I've looked this over dozens of times and this just puzzles me. As far as I can see, the syntax is right. Maybe someone can make some sense out of this.

the field types are: datetime - datetime, productid - int, lineid - int
here's the assignment statement:

sql = "insert into dbo_Schedule (datetime, productid, lineid) values ('2/1/2005', '99', '1')"
docmd.runsql sql

and this is the error I get: 

-2147217900 Syntax error in INSERT INTO statement.

However, when I remove the datetime field from the insert into statement, it executes successfully adding the new records with the desired values inserted into productid and lineid fields. 
I even tried changing the datetime field to type varchar and was trying to store a simple string in there. That too gave the same error above.

Any help will be much appreciated. 

Thanks in advance
Angelo

First

Asked By Aarthi Saravanakumar on 04-Aug-05 02:35 PM
get your sql and run it on the SQL server directly. That would help you verifying the syntax of your statement.

tried that

Asked By Angelo Munoz on 04-Aug-05 02:46 PM
yea, I tried that and it executed successfully. Just don't seem to can find the problem in VB editor.

Reserved word

Asked By Joe Davis on 04-Aug-05 03:03 PM
datetime is a reserved word.  Try changing that name and see if that helps.
seems to work
Asked By Angelo Munoz on 04-Aug-05 03:25 PM
hey thanks. that seems to be working now. the weird thing is that i've used that variable name before and it worked just fine. crazy.

now it's giving me a primary key violation. when i insert a record with the INSERT INTO statement, shouldn't that automatically enagage the autonumber attribute for the PK field since it is set to "identity (not for replication)"?
Is line ID ur identity column
Asked By Bharath Reddy on 04-Aug-05 04:41 PM
Do not insert lineID in the insert statement.....if lineID is the identity column..
it's not
Asked By Angelo Munoz on 04-Aug-05 04:49 PM
yeah, i know that would cause an insertion error. (insertion not allowed in PK column). 
I actually found what the problem was. There are fields in my table that are foreign keys and the values being inserted didn't match their values in their respective PK tables.

The query works fine now. thanks for your advice anyway.

Angelo