Hello all,

I have an ORACLE 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!

---

Request for Question Clarification by hammer-ga on 20 Nov 2002 08:05 PST

Have you tried something like:

sql = "SELECT " & Chr(34) & "Source" & Chr(34) & " FROM ... "

---

Clarification of Question by dignan-ga on 20 Nov 2002 08:52 PST

I have tried that.  The SELECT statement works and I can retrieve
records from the table.  However, when I try to update the field
through the recordset rs(0) = "newvalue" and call rs.update I get an
error.

I have found out that I can create the fields with all caps and quotes
and when I refer to them in the code I do not need to use the quotes.

This method seems to work for most reserved words with the exception
of Comment.  It looks like I must refer to the field as "COMMENT" even
if it was created in all caps.  Does anyone have any thoughts on how
to make Comment act like the other reserved words?

Thanks,
Brian

---

Request for Question Clarification by hammer-ga on 20 Nov 2002 09:15 PST

Have you tried:
rs(0) = 'newvalue'

Note the single quotes instead of the double quotes.

---

Clarification of Question by dignan-ga on 20 Nov 2002 12:15 PST

The problem isn't in the value that is being stored to the field.  It
is in the field name.  What you suggest isn't valid syntax in VB
anyways.

I am going to close this question since it isn't going anywhere here
and I am having better luck elsewhere.

Just for the benefit of others here is what I have found out:
- It is legal to create the column SOURCE in ORACLE without quotes.
- You can refer to reserved word columns created in all upper case
without quotes
- The reserved word Comment seems to be an expection.  You can create
the field "COMMENT" and you must refer to it as "COMMENT"

Thanks for you help,
Brian