Link to home
Start Free TrialLog in
Avatar of Modellium
Modellium

asked on

Oracle Provider For OLE DB 9.2.0.4.0 can't update the recordset when it contains an INNER JOIN clause

Hello!
I'm trying to edit a recordset obtained from an Oracle 9.2.0.5 database.

When the SQL query is made with an inner join in it, I can't edit fields.
"SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME From EMP inner join DEPT  on EMP.DEPTNO = DEPT.DEPTNO "
But when the join is made within the "Where" section (and not with an inner join in the "from" section), it works fine.
"SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME From EMP, DEPT Where EMP.DEPTNO = DEPT.DEPTNO "

I just change the provider, from MS provider for Oracle to Oracle Ole DB provider to resolve an other problem with the clob datatype. All my queries in my code are made with "inner join" SQL.

Is there a way to resolve this issue without changing all my queries?




Here is the way I use to obtain the recordset :

Public Function ShowForm(strUserName As String, strPassword As String, strBD As String) As Boolean
 ' Set up Command and Connection objects
  Dim rs As ADODB.Recordset, Cmd As ADODB.Command
  Set rs = New ADODB.Recordset
  Set Cmd = New ADODB.Command

  'query with an inner join - not working to edit field
  strSQL = "SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME From EMP inner join DEPT  on EMP.DEPTNO = DEPT.DEPTNO "
 
  'query whithout an inner join - working well to edit field
  'strSQL = "SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME From EMP, DEPT Where EMP.DEPTNO = DEPT.DEPTNO "
 

  'Run the procedure
  Cmd.ActiveConnection = "Provider=OraOLEDB.Oracle.1;Password=" & strPassword & ";Persist Security Info=True;User ID=" & strUserName & ";Data Source=" & strBD

  Cmd.CommandText = strSQL
  Cmd.CommandType = adCmdText
  Cmd.CommandTimeout = 0
 
 
  rs.CursorLocation = adUseClient
  rs.Open Cmd, , adOpenStatic, adLockBatchOptimistic
 
  ' Disconnect the recordset
  Set rs.ActiveConnection = Nothing
  Cmd.ActiveConnection.Close
 
End Function
ASKER CERTIFIED SOLUTION
Avatar of Helena Marková
Helena Marková
Flag of Slovakia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of VeluN
VeluN

Hi,

Search to Metalink to get latest patch set.

Rgds,

Velu N