Get around reserved words in SQL statement

[pizzaguy]

I have an ASP page that use ODBC to connect to a SQL table in Tandem. One of the fields of the SQL table is MONTH which I think is a reserved word in ASP. So when I run the page that have a SQL satement "select MONTH .....", it give me the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Tandem][ODBC Driver][NonStop SQL] ODBC Server message (32010) : Syntax error near MONTH.

Is there a way to get around it?

[pizzaguy]

Oh, forgot one thing, the SQL table is being used in many other programs and so the column name cannot be changed.

[aCcodeMonkey]

One way to confuse.... Uh, use reserved words in SQL is to bracket the field names

SQL = "INSET INTO Accounts (username,[password]) Values('" & myVar & "','" & myVar1 & "')"

This also works for field names/aliases with spaces

SELECT First+' '+Last AS [User Name] FROM Users


The other "prefferred" is tu use a fully qualified id
Owner.TableName.FieldName

dbo.mytable.name

Hope this helps :cool:

[pizzaguy]

Both methods tried, did not work. Any other ideas?

[aCcodeMonkey]

Did you try aliasing the Field
Select MONTH As 'myMonth'

What is your query?

[pizzaguy]

I just try it with a simple "SELECT MONTH FROM TABLE1 WHERE COLUMN1 = ....", and asp just doesn't like to see the word MONTH. No matter it enclosed it with [] or use alias or owner.table.columnname.

I am thinking this may be caused by the ODBC connection to Tandem.

[aCcodeMonkey]

Another solution would be to have your DBA create a "View" or stored prcedure for this query.

[pizzaguy]

This sounds pretty promising, l will give it a try. Thanks aCcodeMonkey.

[Roelf]

do SELECT * FROM Table, then use only rs.Fields("MONTH")

[pizzaguy]

Can't use SELECT * because the actual SQL statement is a very complicated one that left joins 2 other tables and have to specified the MONTH and some other columns. The good news is I finally got it done. There is a tandem command that I can use to wrap around the SQL statement in another SQL statement.

Thanks everybody.