Solved

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

Posted on 2009-07-14
8
1,810 Views
Last Modified: 2013-12-19
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
0
Comment
Question by:Zoum
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
8 Comments
 
LVL 7

Expert Comment

by:fluglash
ID: 24866776
wich driver you use to connect Oracle`s or Microsoft`s?
is the source of VB program available? is it VB6 or .NET?
0
 

Author Comment

by:Zoum
ID: 24870983
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
 
LVL 7

Accepted Solution

by:
fluglash earned 500 total points
ID: 24876686
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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:Zoum
ID: 24924870
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
 

Author Comment

by:Zoum
ID: 24984827
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
 

Author Comment

by:Zoum
ID: 25418451
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

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  â€¦
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup
Suggested Courses

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question