Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2009-07-14
8
Medium Priority
?
1,816 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 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

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.  …
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 explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

688 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