Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2002
    Posts
    4

    Question Unanswered: Update Quoted Field in ORACLE Using ADO

    Hello all,

    I have a database table that has a field named "Source". I created the field with quoted identifiers since Source is a reserved word. I have a VB app that selects rows from this table and performs an update on the "Source" field using an ordinal position in the recordset.

    sql = "SELECT ""Source"" FROM ""DSRACTVYGUIControls"" WHERE ""ControlName"" = 'MyNewComponent_Src1Agg'"
    rs.Open sql, conn, , , adCmdText
    While Not rs.EOF
    rs(0) = Replace(rs(0),"SEARCH", "REPLACE")
    rs.Update
    rs.MoveNext
    Wend

    The problem comes when I call the update. I receive the error:
    [Oracle][ODBC][Ora]ORA-00904: invalid column name

    It would seem that ADO is not using the quoted identifiers when it trys to perform the update. Does anyone have any thoughts on this? Is there an option in the connection or recordset object that I need to set?

    I know that I could just use an update query, but I would really like for the update to be performed through the ado recordset since this works with other databases and I have this trype of scenario many places in my code.

    Thanks for your help,

    brian

  2. #2
    Join Date
    Jul 2001
    Location
    NC
    Posts
    102
    BrianG

    The simplist way to resolve your problem would be to change the table name to TBL_SOURCE and column name to COL_SOURCE.

  3. #3
    Join Date
    Nov 2002
    Posts
    4
    Changing the actual name of the table and column in the database is not an option.

    Any other thoughts?

    Thanks,
    Brian


    Originally posted by vududoc
    BrianG

    The simplist way to resolve your problem would be to change the table name to TBL_SOURCE and column name to COL_SOURCE.

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    I would really recommend doing an update statement - it is faster and you can reuse it. If you still want to persue the other option, how do you set a quoted identifier to true in oracle ? In Sql Server you can use a transact sql statement "SET QUOTED_IDENTIFIER ON" - is this possible in oracle ?

  5. #5
    Join Date
    Jan 2003
    Location
    Dordrecht, The Netherlands
    Posts
    95

    Re: Update Quoted Field in ORACLE Using ADO

    Have you checked that the column name in Oracle is "Source" and not "SOURCE"?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •