ADO-ODBC Update problem 
Author Message
 ADO-ODBC Update problem

Hello all

I receive a textfile from a customer with 6000 records and I want to put

these data into an access97 database. I use the code shown below to do
that.
My problem is that I can put in the data if the database is empty. If I
then take the same textfile to update the database the ADORS.Find
returns an error saying that a VareNr doesn't exist even though it does.

This error first occur from lets say record 231. This in itself isn't a
big problem, but then the loop has finished, the UpdataBatch returns an
error saying that an updata isn't possibel due to multiple VareNr.
Why is that so?

Another thing, do you have any suggestions to speed up the code?

Public ADOCon As New ADODB.Connection
Public ADOCmd As New ADODB.Command
Public ADORS As New ADODB.Recordset

Function OpdaterDB() As Boolean
  Dim CDData As String
  Dim ArrOfData() As String

  On Error GoTo DBErr

  OpdaterDB = False
  ADOCon.Open "DSN=" & ODBCNavn & ";uid=sa;pwd=;database="
  Set ADOCmd.ActiveConnection = ADOCon
  ADOCmd.CommandText = "Select * from VareData"
  ADORS.CursorLocation = adUseClient
  ADORS.Open ADOCmd, , adOpenStatic, adLockBatchOptimistic
  Open CDDataFil For Input As #1
  Do
    ADORS.MoveFirst
    Line Input #1, CDData
    If InStr(CDData, ";") > 0 Then
      ArrOfData = Split(CDData, ";")
      ADORS.Find "VareNr=" & Val(ArrOfData(0))
      ADORS!VareNr = Val(ArrOfData(0))
      ADORS!VareTxt = ArrOfData(1)
      ADORS!EnhedsTxt = ArrOfData(2)
      ADORS!EDI = Left$(ArrOfData(3), 12)
      ADORS!EAN = Left$(ArrOfData(4), 12)
      ADORS!LabelPrPalle = ArrOfData(5)
      ADORS!EnhedPrPalle = Right$(ArrOfData(6), 3)
      ADORS!MHT = ArrOfData(7)
      ADORS!BatchNr = ArrOfData(8)
      ADORS.Update
    End If
    MDIForm1.StatusBar1.Panels(3).Text = Loc(1)
  Loop Until EOF(1)
  Close #1
  ADOCon.BeginTrans
  ADORS.UpdateBatch
  ADOCon.CommitTrans
  ADORS.Close
  ADOCon.Close
  OpdaterDB = True
Exit Function
DBErr:
  Select Case Err.Number
  Case 3021
    ADORS.AddNew
    Resume
  Case Else
    MDIForm1.StatusBar1.Panels(3).Text = Err.Description
  End Select
End Function



Sat, 12 Jul 2003 22:01:05 GMT  
 ADO-ODBC Update problem
I have not used Find but the docs
imply that Find searches "from the current record".  Is it
possible that you are not finding your key, because it is "above"
the current record??

Especially so after your  first AddNew
which definitely puts you at the bottom????

And, of course, if your key is to be unique, Update will complain
if you are adding keys that are already in the recordset, above.

Quote:

> Hello all

> I receive a textfile from a customer with 6000 records and I want to put

> these data into an access97 database. I use the code shown below to do
> that.
> My problem is that I can put in the data if the database is empty. If I
> then take the same textfile to update the database the ADORS.Find
> returns an error saying that a VareNr doesn't exist even though it does.

> This error first occur from lets say record 231. This in itself isn't a
> big problem, but then the loop has finished, the UpdataBatch returns an
> error saying that an updata isn't possibel due to multiple VareNr.
> Why is that so?

> Another thing, do you have any suggestions to speed up the code?

> Public ADOCon As New ADODB.Connection
> Public ADOCmd As New ADODB.Command
> Public ADORS As New ADODB.Recordset

> Function OpdaterDB() As Boolean
>   Dim CDData As String
>   Dim ArrOfData() As String

>   On Error GoTo DBErr

>   OpdaterDB = False
>   ADOCon.Open "DSN=" & ODBCNavn & ";uid=sa;pwd=;database="
>   Set ADOCmd.ActiveConnection = ADOCon
>   ADOCmd.CommandText = "Select * from VareData"
>   ADORS.CursorLocation = adUseClient
>   ADORS.Open ADOCmd, , adOpenStatic, adLockBatchOptimistic
>   Open CDDataFil For Input As #1
>   Do
>     ADORS.MoveFirst
>     Line Input #1, CDData
>     If InStr(CDData, ";") > 0 Then
>       ArrOfData = Split(CDData, ";")
>       ADORS.Find "VareNr=" & Val(ArrOfData(0))
>       ADORS!VareNr = Val(ArrOfData(0))
>       ADORS!VareTxt = ArrOfData(1)
>       ADORS!EnhedsTxt = ArrOfData(2)
>       ADORS!EDI = Left$(ArrOfData(3), 12)
>       ADORS!EAN = Left$(ArrOfData(4), 12)
>       ADORS!LabelPrPalle = ArrOfData(5)
>       ADORS!EnhedPrPalle = Right$(ArrOfData(6), 3)
>       ADORS!MHT = ArrOfData(7)
>       ADORS!BatchNr = ArrOfData(8)
>       ADORS.Update
>     End If
>     MDIForm1.StatusBar1.Panels(3).Text = Loc(1)
>   Loop Until EOF(1)
>   Close #1
>   ADOCon.BeginTrans
>   ADORS.UpdateBatch
>   ADOCon.CommitTrans
>   ADORS.Close
>   ADOCon.Close
>   OpdaterDB = True
> Exit Function
> DBErr:
>   Select Case Err.Number
>   Case 3021
>     ADORS.AddNew
>     Resume
>   Case Else
>     MDIForm1.StatusBar1.Panels(3).Text = Err.Description
>   End Select
> End Function



Mon, 14 Jul 2003 04:13:33 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Unable to update records in Visual FoxPro Table with ADO-ODBC

2. ADO Update problems MAJOR PROBLEMS !!

3. VB3 ODBC DB Update problem

4. ODBC update problem...... Help Please

5. Delete and Update-problems with ODBC

6. Problem with recordSet.update using DAO and ODBC Direct

7. ODBC - Visual Basic : updating problem/Dynaset

8. A problem about ODBC API cursor positioned update and delete

9. VB3 ODBC DB Update problem

10. SQL ODBC Update Problem in VB5 via SQL7

11. VB3 ODBC DB Update problem

12. Problem on Update ODBC-Data thru a Data-Control in VB 5

 

 
Powered by phpBB® Forum Software