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
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Search to Metalink to get latest patch set.
Rgds,
Velu N