Solved

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

Posted on 2009-07-14
8
1,800 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

763 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