Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-07-14
8
Medium Priority
?
1,825 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
  • 4
  • 2
6 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 2000 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
Technology Partners: 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

772 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