inserting data in MSSQL into a column name beginning with a number

[tyutghf]

I have a table in MSSQL that has several columns beginning with numbers i.e.

1A
1B
1C

In MSSQL (2000) design view it places brackets around the column names i.e. [1A].

I can`t change these column names as it is an old system, but I do need to hook this up to a webpage but I am struggling to update to these fields

rs.Fields("[1A]")=request.form("1A")
rs.Update

gives the error 'Item cannot be found in the collection corresponding to the requested name or ordinal'

and

rs.Fields("1A")=request.form("1A")
rs.Update

gives the error '[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '1'. '


Does anyone know of a way around this?

Thanks

 

[gmmastros]

One thing you could do is use the ordinal value. You see, the fields method takes a string or an integer. If you pass a string, it matches on the column name. If you pass in a number, it uses ordinal value. So, if the 1A column in the table is the 4th column, then you could....

rs.Fields(4)=request.form("1A")

Usually, it's not a good idea to rely on ordinal position, so you may want to cycle through the recordset object to get it. The problem is.... 1A may be the 4th column now, but 2 years from now, someone may insert a column... effectively changing the ordinal positions.

With SQL Server, you can determine the ordinal position using this query:

Select Table_Name, Column_Name, Ordinal_Position 
From   Information_Schema.Columns
Where  Table_Name = '[!]YourTableNameHere[/!]'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[vicvirk]

you could try changing your SQL statement,

sql = "update myTable set [1A] = '" & myValue & "' where myCondition = myTest";
set rs = myConn.execute(sql)

or put the above in a stored procedure and call it

sql = "myStoredProcedure " & myValue & "," & myTest;

where myStoredProcedure is something like:

@myValue myDataType;
@myTest myDataType;

"update myTable set [1A] = @myValue where myCondition = @myTest;


TIP: trying googling the answer before posting, you'll find that more times than not someone else somewhere has had the same request and posted an answer online.
----
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javascript enabled browsers

 

[tyutghf]

Thanks guys will try your suggestions tomorrow when I am back at work