Problem updating text field containing special characters in Oracle 10g from VB

We recently upgraded from Oracle 9i to 10g.

We periodically update an Oracle table using a VB program.

One of the rows to be updated contains the special character corresponding to "1/2".

When the UPDATE SQL statement attempts to update that row, the OLEDB provider for Oracle returns the following error:
  ROW-00014: Cannot update row as the data in the database has changed

If we remove that character, the updates completes successfully. The update never complained under 9i.

Unfortunately, a response was not given to the issuer of the related question I found in your forum.

Any Idea how to resolve this issue?

Thanks in advance
ZoumAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
fluglashConnect With a Mentor Commented:
this must solve your problem:

Public Sub Update(ByRef ConnectionEXP As ADODB.Connection, ByVal BidRS As Recordset)
Dim RS2 As New ADODB.Recordset
Dim strSQL As String
    strSQL = "SELECT * FROM CSMT_Table WHERE Project_Name = 'C07006' AND Cost_Code = '003'"
    RS2.Open strSQL, ConnectionEXP, adOpenDynamic, adLockOptimistic, adCmdText
    With RS2
 
       .Requery
 
        !Title = Left(BidRS!Description, 34)
        !Project = BidRS!Project
        .Update
    End With
    RS2.Close
End Sub

Open in new window

0
 
fluglashCommented:
wich driver you use to connect Oracle`s or Microsoft`s?
is the source of VB program available? is it VB6 or .NET?
0
 
ZoumAuthor Commented:
Oracle version running: 10.2.0.2.0

Oracle OLEDB Provider Version installed on running workstation: 10.2.0.1.0

Connection string as defined in .ini file:

   ConnectionStringExp="Provider=OraOLEDB.Oracle;Data Source=EXPDEV;User Id=exp;Password=sql;"

Simplified VB6 code used:

Public Sub Update(ByRef ConnectionEXP As ADODB.Connection, ByVal BidRS As Recordset)
Dim RS2 As New ADODB.Recordset
Dim strSQL As String

    strSQL = "SELECT * FROM CSMT_Table WHERE Project_Name = 'C07006' AND Cost_Code = '003'"

    RS2.Open strSQL, ConnectionEXP, adOpenDynamic, adLockOptimistic, adCmdText

    With RS2
        !Title = Left(BidRS!Description, 34)
        !Project = BidRS!Project
        .Update
    End With
               
    RS2.Close

End Sub
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
ZoumAuthor Commented:
I asked the programmer to test your suggestion although I do not quite see why this would make any difference since this is not a problem of concurrent updates as the server running the VB program is the only access to the Oracle database.

The programmer hasn't come back to me yet.

I should get results soon, I hope.

0
 
ZoumAuthor Commented:
I am still waiting for the programmer to try the suggestion.

I forgot to mention that we were using UTF-8 encoding under Oracle 9i and now using UTF-16 on 10g.
0
 
ZoumAuthor Commented:
I still have not received a reply from my programmer.

I was told he is 100% busy doing something else.

If you must cancel the question, I will have to reopen it later when the programmer gets around to listen to me.

Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.