VB6 and MS Access - why can I only add 12 records? 1

[lbaker78]

Hi all,

I am a n00bi3 VB6 programmer and have written (and/or borrowed!) the following code, it runs fine for the first 12 records it writes to the DB, but after that I get an error saying:

Run-Time error '-2147217887 (80040e21)':
Multiple-step operation genreated errors. Check each status value.

What does thsi mean and how do I fix it? code follows.. (the app is reading MP3 file tags and writing them to an access DB call MediaLib.MDB) - I havent included the whole app, just the bit that is erroring!

Option Explicit

Private Type ID3V1Tag 'will look into using a class instead
   Album       As String * 30
   Artist      As String * 30
   Comment     As String * 30
   Genre       As Byte
   Identifier  As String * 3
   Title       As String * 30
   Year        As String * 4
End Type

Private Sub Command2_Click()
    Dim i                As Integer
    Dim curfile          As String
    Dim curArtist        As String
    Dim curTitle         As String
    Dim curAlbum         As String
    Dim numfiles         As Integer
    Dim conConnection    As New ADODB.Connection
    Dim cmdCommand       As New ADODB.Command
    Dim rstRecordSet     As New ADODB.Recordset
    Const ID3V1TagSize   As Integer = 127
    Dim ID3Tag           As ID3V1Tag
    Dim lFileHandle      As Long
    Dim lMp3FileLength   As Long

  
        numfiles = File1.ListCount
        

   lFileHandle = FreeFile()
   
   
   
  
    For i = 0 To (numfiles - 1)
    File1.ListIndex = i
    curfile = Dir1.Path & "\" & File1.FileName
    Text1.Text = curfile
    
    'READING MP3 TAG info here
    
    Open curfile For Binary As #lFileHandle

   lMp3FileLength = LOF(lFileHandle) 'Get the length of mp3 file

   Get #lFileHandle, lMp3FileLength - ID3V1TagSize, ID3Tag.Identifier

   With ID3Tag
      If .Identifier = "TAG" Then
         Get #lFileHandle, , .Title   '30 chars
         Get #lFileHandle, , .Artist  '30 chars
         Get #lFileHandle, , .Album   '30 chars

         curTitle = RTrim(.Title)
         curArtist = RTrim(.Artist)
         curAlbum = RTrim(.Album)
    Else: Close
            curTitle = "No Tag Data"
            curAlbum = "No Tag Data"
            curArtist = "No Tag Data"
    
      End If
      Close
   End With

   
       
    'Finished reading MP3 Tags
    

 
  conConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
      App.Path & "\" & "MediaLib.mdb;Mode=Read|Write"

  conConnection.CursorLocation = adUseClient

  conConnection.Open
  
  With cmdCommand
    .ActiveConnection = conConnection
    .CommandText = "SELECT * FROM library;"
    .CommandType = adCmdText
  End With
    
  With rstRecordSet
  
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .Open cmdCommand
  End With
    
    
    'DB addition starts here
  
  With rstRecordSet
      .AddNew
      rstRecordSet!URL = curfile
      rstRecordSet!Artist = curArtist
      rstRecordSet!Title = curTitle
      rstRecordSet!Album = curAlbum
      rstRecordSet.Update
   
  End With
    
    'DB addition ends here
    

   rstRecordSet.Close

  'Close the connection

  conConnection.Close
  
  curArtist = ""
  curTitle = ""
  curAlbum = ""
    
    Next i



  'Release your variable references

  Set conConnection = Nothing
  Set cmdCommand = Nothing
  Set rstRecordSet = Nothing

End Sub

TIA!

 

[bjd4jc]

My guess is that the application is giving you an error on one of these three lines:

  With rstRecordSet
      .AddNew
      rstRecordSet!URL = curfile
      rstRecordSet!Artist = curArtist
      rstRecordSet!Title = curTitle
      rstRecordSet!Album = curAlbum
      rstRecordSet.Update
   
  End With

Usually what this means is that there is a data type or data lenght issue. Either you are assigning more data to the field than what the field is defined for (based on your table definition) or you are assigning a different data type that won't convert (for example, trying to put a string into an integer field).

If it is not giving an error on one of these lines please tell us specifically which line it is

 

[lbaker78]

Yes you are quite right Bjd4jc, it is the line:

rstRecordSet!URL = curfile

That is giving the error!

 

[bjd4jc]

Did you check out the length of what you were putting into it?

In the command window when you debug do a

?rstRecordset("URL").DefinedSize

and then

?Len(curfile)

and see if the latter is longer then the former...

 

[lbaker78]

Thankyou Bjd4Jc! you have solved it... I am going to stand in the corner with the dunce hat on! :$

The URL field was too short in the DB, I had assumed Access would allow it to be 255 (as that's the maximum), but completely forgot I had to actually define that value myself! doh!

Thanks again!