Error #156: Incorrect syntax near the keyword 'File'.

[tats]

I have an ASP.NET page which updates, deletes and adds records to/from one table in the database, but not another. The only difference between the two tables that I can see is that the 2nd table contains [Date] and [File] columns (Sql Server puts the brackets automatically to distinquish between the Date and File reserved keywords).

I tried to change the File column name to FilePath, and that worked. But I'm suspicious. Why doesn't Date produce a problem? If Sql Server allows keywords in the column names, howcome it cannot handle the updates? Perhaps, my problem is somewhere else?!

Any suggestions?

 

[tlbroadbent]

Did you include the brackets around the keywords?

If that isn't the problem, please post the query. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[tats]

When I used brackets, I got a different error saying that no such field
exists.

I'm using .NET SqlCommandBuilder class which automatically builds the
query for me. For eg, for an insert it generates the following:
INSERT INTO Press( Author , Date , Title , PublicationTitle ,
PageReference , File , Abstract ) VALUES ( @p1 , @p2 , @p3 , @p4 , @p5 ,
@p6 , @p7 )
Each of the parameters corresponds to the columns in the dataset table,
which I also pass.

It looks like this:
connection = new SqlConnection(mccConn); //dbase name, location etc.
adapter = new SqlDataAdapter("SELECT * FROM Person", connection);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;//
retrieves primary & unique key information.
commandBuilder = new SqlCommandBuilder(adapter); // generates the
delete, insert and update commands as required
adapter.Update(dataSet,"Person" //submits dataset and the table name
to the database

This code works well for delete, insert and update commands (unless you
have a column with a name 'File' - I'm more or less convinced of that now).

I guess I shouldn't have used the name 'File' in a number of my tables
:-(.
There's a lesson in here somewhere!

Thanks, Terry.

 

[patrician]

Hi Tats

I think you'll find that File is a reserved word and therefore shouldn't be used as a column name.

 

[tlbroadbent]

It is best to avoid using reserved words as identifiers. If you are not able to change the columns names, can you modify the generated SQL Statements? Perhaps you can use the REPLACE function.

SQLStatement = REPLACE(SQLStatement, " File ", " [File] &quot Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[tats]

Aha, so the use of a reserved word as a column name WAS my problem.
I've only used it in 3 tables! I'd better get started with changing things. Fortunately, it is in my power to do so.

Thanks, patrician and Terry.