Updating Oracle DB using VB6

[MarcelBeals]

I am having such a problem with ADO that I need some help.
I must be looking at this problem to close and can not see the obvious. Here is the code:

Private Sub cmdUpdateEI_Click()
Dim rs_CHH As Recordset
Dim rs_ROE As Recordset

Dim strSQL_CHH As String
Dim strSQL_ROE As String
Dim strDate As String

Set rs_CHH = New ADODB.Recordset
Set rs_ROE = New ADODB.Recordset

strDate = "20000801"
strEmployee = "01000"

Screen.MousePointer = ccHourglass

strSQL_CHH = "select CHH.EM_EMPLOYEE_ID, " & _
"CHH.CHH_PAY_PERIOD_END_DATE, " & _
"CHH.CHH_WEEKS_HRS_WORKED " & _
"From CHH " & _
"where CHH.CHH_PAY_PERIOD_END_DATE > " & strDate & " and " & _
&quot;CHH.EM_EMPLOYEE_ID < &quot; & strEmployee & _
&quot;ORDER BY CHH.EM_EMPLOYEE_ID, CHH.CHH_PAY_PERIOD_END_DATE&quot;

rs_CHH.Open strSQL_CHH, curConnection, adOpenForwardOnly
rs_CHH.MoveFirst

strSQL_ROE = &quot;select ROE.EM_EMPLOYEE_ID, &quot; & _
&quot;ROE.ROE_PAY_PERIOD_END_DATE, &quot; & _
&quot;ROE.ROE_NUMBER_OF_WEEKS_IN_52WKS &quot; & _
&quot;From ROE &quot; & _
&quot;ORDER BY ROE.EM_EMPLOYEE_ID, ROE.ROE_PAY_PERIOD_END_DATE&quot;

rs_ROE.Open strSQL_ROE, curConnection, adOpenDynamic, adLockPessimistic
rs_ROE.MoveFirst

While rs_CHH.EOF = False And rs_ROE.EOF = False

If rs_ROE!EM_EMPLOYEE_ID = rs_CHH!EM_EMPLOYEE_ID And _
rs_ROE!ROE_PAY_PERIOD_END_DATE = rs_CHH!CHH_PAY_PERIOD_END_DATE And _
rs_ROE!ROE_NUMBER_OF_WEEKS_IN_52WKS <> rs_CHH!CHH_WEEKS_HRS_WORKED Then

rs_ROE!ROE_NUMBER_OF_WEEKS_IN_52WKS = rs_CHH!CHH_WEEKS_HRS_WORKED
rs_ROE.Update
rs_CHH.MoveNext
Else
If rs_ROE!EM_EMPLOYEE_ID > rs_CHH!EM_EMPLOYEE_ID Then
rs_CHH.MoveNext
Else
If rs_ROE!EM_EMPLOYEE_ID < rs_CHH!EM_EMPLOYEE_ID Then
rs_ROE.MoveNext
Else
If rs_ROE!EM_EMPLOYEE_ID = rs_CHH!EM_EMPLOYEE_ID And _
rs_ROE!ROE_PAY_PERIOD_END_DATE > rs_CHH!CHH_PAY_PERIOD_END_DATE Then
rs_CHH.MoveNext
Else
If rs_ROE!EM_EMPLOYEE_ID = rs_CHH!EM_EMPLOYEE_ID And _
rs_ROE!ROE_PAY_PERIOD_END_DATE < rs_CHH!CHH_PAY_PERIOD_END_DATE Then
rs_ROE.MoveNext
Else
If rs_ROE!EM_EMPLOYEE_ID = rs_CHH!EM_EMPLOYEE_ID And _
rs_ROE!ROE_PAY_PERIOD_END_DATE = rs_CHH!CHH_PAY_PERIOD_END_DATE And _
rs_ROE!ROE_NUMBER_OF_WEEKS_IN_52WKS = rs_CHH!CHH_WEEKS_HRS_WORKED Then
rs_CHH.MoveNext
End If
End If
End If
End If
End If
End If

Wend

rs_CHH.Close
rs_ROE.Close

dbg_ROE_After.Refresh

Screen.MousePointer = ccArrow

End Sub


Can someone please help.

Marcel.

 

[Snaggs]

Can you give us a hint where or what the error is? Snaggs
tribesaddict@swbell.net

 

[MarcelBeals]

I do not have an error.
This works fine. The problem is that it does not update the database with my changes. I can see the record being changed but it does not update the database after the program ends.

Marcel.

 

[culshaja]

You are using adForwardOnly which is a read-only recordset!
You need to change the ROE cusrsor type to adOpenDynamic or adOpenKeySet.

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[MarcelBeals]

I am using:
rs_ROE.Open strSQL_ROE, curConnection, adOpenDynamic, adLockPessimistic
Any other suggestions?

Marcel.

 

[culshaja]

Which ODBC driver are you using? Oracle or Microsoft version ofthe Oracle ODBC driver? Which version?

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[MarcelBeals]

Microsoft ODBC Driver For Oracle
Version 2.00.00.6325

 

[BigLar]

When you write &quot;I can see the record being changed but it does not update the database after the program ends.&quot;, do you mean that the first and last of the debug statements shown below print different values, yet a subsequent query of this record does not show the change?

debug.print &quot;1: &quot;;rs_ROE!ROE_NUMBER_OF_WEEKS_IN_52WKS
rs_ROE!ROE_NUMBER_OF_WEEKS_IN_52WKS
= rs_CHH!CHH_WEEKS_HRS_WORKED
debug.print &quot;2: &quot;;rs_ROE!ROE_NUMBER_OF_WEEKS_IN_52WKS
rs_ROE.Update
debug.print &quot;3: &quot;;rs_ROE!ROE_NUMBER_OF_WEEKS_IN_52WKS
rs_CHH.MoveNext

Are you using transaction control? If so, are you issuing an MTS .SetComplete or other commit action when you are finished?

Hope this helps

Larry
Larryh@ecn.ab.ca