locked
How to handle a column with an awkward field name using ADODB.RecordSet RRS feed

  • Question

  • 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")


    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!
    Friday, July 17, 2009 6:35 PM

Answers

  • Fields collection supports numeric indices.
    You could also try and see what the name of the field is inside the collection by doing something like:
    rs_memupdt.Fields(0).Name
    with an appropriate index.

    As I can see you are using MSDASQL with an ODBC driver. Another suggestion is to try an OLEDB provider(SQLOLEDB or SQLNCLI).
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, July 20, 2009 7:44 PM

All replies

  • Did you take a look at this - http://msdn.microsoft.com/en-us/library/aa259228(SQL.80).aspx
    This posting is provided "AS IS" with no warranties, and confers no rights
    Friday, July 17, 2009 6:56 PM
  • How would I use that from my ASP code though?
    Friday, July 17, 2009 6:58 PM
  • Fields collection supports numeric indices.
    You could also try and see what the name of the field is inside the collection by doing something like:
    rs_memupdt.Fields(0).Name
    with an appropriate index.

    As I can see you are using MSDASQL with an ODBC driver. Another suggestion is to try an OLEDB provider(SQLOLEDB or SQLNCLI).
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, July 20, 2009 7:44 PM