Trouble updating record using ADO with Oracle dB

[fenwah]

I am using the following code just to update one record. I am able to update records using SQL*Plus in my Oracle dB. My code (shown below), runs fine UNTIL I get to the rs.Update. When that happens, the application just hoses. I left it sit for about an hour with no success.

Since fgCn is a variable that I am calling, here is what is inside that called variable (maybe that will help).

Provider=MSDASQL.1;Extended Properties="DRIVER={ORACLE ODBC DRIVER};SERVER=9z_pmsd;UID=fennigbr;PWD=xxxxx;DBQ=9z_pmsd;DBA=W;APA=T;PFC=10;TLO=O;"


I can read records just fine. I just can't update them. I do have write cabibilites to the database since I can update records using SQL*Plus.


Private Sub btnUpdateRecord_Click()

Dim TempSQL As String
Dim Rs As ADODB.Recordset
Dim A As String
Dim Count As Integer

lblTime.Caption = ""

If txtF4Date.Text = "" Then
MsgBox "Please enter a valid date by double clicking on the date text box.", vbOKOnly + vbExclamation
Exit Sub
End If

TempSQL$ = "SELECT PIIN_SPIIN_ID, A3_PURCH_OFC_ID, F4_SIGNATURE_DT FROM PMS.TPMS05_DD350 WHERE PIIN_SPIIN_ID = '" & fgPSQuery$ & "'"
Set Rs = New ADODB.Recordset
Rs.CursorLocation = adUseClient
Rs.Open TempSQL$, fgCn, adOpenDynamic, adLockOptimistic

If Rs.EOF = False And Rs.BOF = False Then


Rs("F4_SIGNATURE_DT") = txtF4Date

Rs.Update '*****HOSES UP RIGHT HERE*****

Rs.Close
Set Rs = Nothing

End If

End Sub

[twistedthoughts]

this may help

cnfg.execute "update pms.tpms05_dd350 set F4_signature_Dt = '" & txtF4Date & "' where piin_spiin_id = '" & fgPSQuery & '"

[jspurrier]

I've had a similar problem. Using the connection objects execute method with an Update SQL statement is the workaround I used too. You may want to try updating your Oracle driver.

[Gaffer]

The fact you're not getting an error message returned suggests there is something wrong with your connection. What version of MDAC are you using?
Also, you may want to avoid using ODBC, it's much slower than using OLE DB.
Possibly try using a serverside cursor, but I don't think that is necessarily the problem.

[abhijit]

WHICH DRIVERS CAN YOU USE TO UPDATE USING A RECORDSET? I HAVE TRIED THAT WITH A NUMBER OF DRIVERS AND THE ONLY THING THAT HAS WORKED FOR ME IS A DSN BASED CONNECTION.


Cheers!
Abhijit

[Gaffer]

Hang on, I know what's wrong here: you need to move to the first record of the recordset.

Use rs.MoveFirst after "If Rs.EOF = False And Rs.BOF = False Then"

[fenwah]

I changed my connection string to as follows:

Conn = "Provider=MSDAORA.1;Password=conwrite1;User ID=fennigbr;Data Source=9z_pmsd;Persist Security Info=True"

It works just fine and dandy now.

[abhijit]

So which driver did you use? I am using MDAC 2.6 in my application, but I am wondering if there is anything at the oracle end which needs updating as well?

Please post yoru dll version minor as well as major overe.

Cheers,
Abhijit