Error (-2147467259) at UpdateBatch

I use the following Code  Snippet in VB6:

  Dim sSqlAs400 As String
  dim sConnectAs400 As String
  Dim rstAs400 As ADODB.Recordset

  Set rstAs400 = New ADODB.Recordset
  rstAs400.CursorType = adOpenKeyset
  rstAs400.LockType = adLockOptimistic
  "Provider=MSDASQL.1;Password=****;Persist Security Info=True;" _
    & "User ID=****;Data Source=As400Sim;Extended Properties="DSN=As400Sim;" _
    & "Description=AS400 Simulation;UID=****;PWD=****;APP=Visual Basic;" _
    & "WSID=PC62;DATABASE=As400Sim";Initial Catalog=As400Sim"

  sSqlAs400 = "SELECT * FROM Data"
  rstAs400.Open sSqlAs400, sConnectAs400
  bRstAs400Open = True
  rstAs400.MoveFirst

  rstAs400!DDSTAT = 1
  ... some more assignments
  iErrPos = 20
  rstAs400.UpdateBatch

At this point sometimes the follwing error occurs:
Fehler bei einer abfragebasierten Aktualisierung, da die zu aktualisierende Zeile nicht gefunden werden konnte. (-2147467259)
(Translated to English:)
"Error at query based update, since the line(record?) could not be found."


Why does this error occure?

Sincerly
Josef
joschrammAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
is this really SQL Server or AS400 database behind?

the error occurs, from what I know, when there is no primary key on the table, or it's data type is not "compatible" with ADO's capabilities for primary keys, or the record below indeed does change.

to try this out, we would need more details about the actual table design, the server version, and a bit more of " ... more assignments" ..


also, you do a SELECT * FROM DATA without a where clause, and it's not clear if you are doing .MoveNext to go through the records, or are indeed just updating 1 record ...
0
joschrammAuthor Commented:
Hi angelIII

>is this really SQL Server or AS400 database behind?
My customer says it is a DB400 database engine.
There exists  n o  primary key at all!

So for more clerness, my code snippet again with some mor details:

  Dim sSqlAs400 As String
  dim sConnectAs400 As String
  Dim rstAs400 As ADODB.Recordset

  Set rstAs400 = New ADODB.Recordset
  rstAs400.CursorType = adOpenKeyset
  rstAs400.LockType = adLockOptimistic
  sConnectAs400 = "Provider=MSDASQL.1;Password=****;Persist Security Info=True;" _
    & "User ID=****;Data Source=ALPHA400;Extended Properties=""DSN=ALPHA400;UID=***;PWD=***;"""

  sSqlAs400 = "SELECT DDKNM1,DDKNM2,DDKSTR,DDKLDS,DDKPLZ,DDKORT,DDKDNR,DDHKNR,DDHKPO,DDWKDT,DDWKTM,DDSTAT,DDFU01,DDFU02,DDFU03," _
        & "DDFU04,DDFU05,DDFU06,DDFU07,DDFU08,DDFU09,DDFU10,DDATB1,DDPRDT,DDPRTM,DDATNR,DDBSMG,DDBSTX,DDLFDT " _
        & "From S10733BT.MBH00FIL.LABDAT " _
        & "WHERE (DDWKDT = '') AND (DDKDNR <> '') AND (not DDATNR like '%00')"

  rstAs400.Open sSqlAs400, sConnectAs400
  bRstAs400Open = True
  rstAs400.MoveFirst
 
  Do While Not rstAs400.EOF
        'read AS400 Values and do some work ...
        txtAuftragsnummer.Text = rstAs400!DDHKNR & ""
        '...
        'The work is done, so mark the record as "done with"
        
    iErrPos = 12
    For i = 1 To iTokenCount
      sFieldName = "DDFU" & Format$(i, "00")
      rstAs400(sFieldName) = sToken(i - 1)
    Next i
        iErrPos = 14
    sHeute = Format$(Now, "YYYY.MM.DD")
    sJetzt = Format$(Now, "HH:MM:SS")
    If bGedruckt Then
      rstAs400!DDPRDT = sHeute
      rstAs400!DDPRTM = sJetzt
    End If 'bGedruckt?
    iErrPos = 15
    rstAs400!DDWKDT = sHeute
    rstAs400!DDWKTM = sJetzt
    iErrPos = 16
         rstAs400!DDSTAT = 1
    iErrPos = 20
    rstAs400.UpdateBatch  
   
    rstAS400.MoveNext
  Loop
At iErrPos = 20 sometimes the follwing error occurs:
Fehler bei einer abfragebasierten Aktualisierung, da die zu aktualisierende Zeile nicht gefunden werden konnte. (-2147467259)
(Translated to English:)
"Error at query based update, since the line(record?) could not be found."

thanks
Josef
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>There exists  n o  primary key at all!
I think this is the major issue, and this needs to be fixed.
otherwise you will not be able to use recordset + updatebatch
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

joschrammAuthor Commented:
okay, my customer will try to fix this. If that will not be possible: Wht could I do to avoid the problem?
Many thanks
Josef
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you may try to also set the cursorlocation to adUseServer.
to be sure, check the values of the CursorLocation, CursorType etc after you opened the recordset. if the values don't match with what you requested initially, it tells you that mode is not supported.

the only alternative otherwise is to use plain UPDATE statements (or stored procedure with arguments) instead of recordset object.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
joschrammAuthor Commented:
many thanks for this quick solutions. I will try it out.
Sincerly,
Josef
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.