Results 1 to 4 of 4
  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
    Apr 2001
    Location
    Netherlands
    Posts
    191

    Re: Update Quoted Field in ORACLE Using ADO

    Just don't call the field 'Source'. That's why it's called a reserved word ... Rename the field to thesource or source1.
    Ruud Schilders
    -----------------
    Oracle DBA
    e-mail : ruud@schilders.it
    URL : www.schilders.it
    Twitter : www.twitter.com/ruudschilders

  3. #3
    Join Date
    Nov 2002
    Posts
    4

    Re: Update Quoted Field in ORACLE Using ADO

    Renaming the column is not an option. The field can be setup and accessed using quotes. In my digging I have also found out that the field can be created using all uppercase and quotes and then referred to as all uppercase without the quotes.

    This seems to work for most reserved words except Comment. When you refer to COMMENT you must use quotes around it.

  4. #4
    Join Date
    Jul 2002
    Location
    India
    Posts
    13
    You need not write the sql statements within quotes .... simply have your SQL as usual for ex

    sql = "Select Source, ControlName from tablename"

    then while opening the recordset, specify the cursortype and locktype while updating through the recordset for ex

    Rs.Open sql, Conn,adOpenKeyset , adLockOptimistic

    Now have the assigning statements and invoke the update method of the recordset.

    Note : The Cursor types can be Dynamic, keyset or static and lock types can be optimistic or pesimistic.....


    hope this will help u...........
    Balaji

Posting Permissions

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