How to handle an awkward column name in an updateable ADODB.RecordSet

[laran]

I have a table with a column named "add". I know how bad this is. It's a legacy database though and I have no choice in the matter.

This database was migrated from Access into SQL Server. The code worked when it was in Access but fails now.

I have the following code which blows up:

set rs_memupdt = Server.CreateObject("ADODB.RecordSet")
rs_memupdt.source = "foo"
rs_memupdt.activeconnection = conn_foo
rs_memupdt.locktype = 3
rs_memupdt.open
WITH rs_memupdt
.ADDNEW
.FIELDS("add") = REQUEST.FORM("add")

set rs_memupdt = Server.CreateObject("ADODB.RecordSet")
rs_memupdt.source = "foo"
rs_memupdt.activeconnection = conn_foo
rs_memupdt.locktype = 3
rs_memupdt.open

WITH rs_memupdt
.ADDNEW
.FIELDS("add") = REQUEST.FORM("add")



This code blows up with this error:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'add'.


I thought using brackets would help. So I changed this:

.FIELDS("add") = REQUEST.FORM("add")

to this:

.FIELDS("[add]") = REQUEST.FORM("add")


That then blows up with this error:

ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.



So my question is this:

How can I refer to the "add" column successfully?


Thank you!

[brucevde]

Use a KeySet cursortype instead of the default ForwardOnly.

rs_memupdt.CursorType= 1 'adOpenKeyset
rs_memupdt.locktype = 3


The Insert sql statement generated by the recordset will be different (how I am not sure but it works) and pass syntax checking.