Strange error when adding a record...

[Andrew Herrmann]

I'm currently using VB 6.0 and MySQL server. I'm trying to add a record to a table and getting a strange error. I'm using the following code:

selStmt = "SELECT * FROM customer;"
recset1.Open selStmt, cnn1, adOpenKeyset, adLockPessimistic
With recset1
.AddNew
.Fields("cust_name") = CustomerName
.Fields("contact_name") = ContactName
.Update
.Requery
.MoveLast
.Close
End With

If the data for either field exceeds 14 characters, I get the following error:

------
Run-time error '-2147217887 (80040e21)':

Errors occurred
------

If I debug, it places me on the ".fields" line for the field in which I exceeded 14 characters.

Both fields are set up as varchar(40). If I try to add the same data to the table using Access, I don't have any problems at all.

I'm out of ideas on this, so if anyone has any ideas....

Thanks!

[MattyBoy]

I found that this error occured when I tried to import a duplicate value into my primary key! DOH!

[lychew]

May be you have add some empty space behind your variable without noticing it?? Try to trim it or write out the variable into a text file. Then use wordpad or others to check if there's any unattended empty space added to your variable.

[Clunietp]

loop thru the errors collection to find out the error

like this:
for each error in connection.errors
msgbox error.name
next

[lychew]

I think if he loops thru the error object, he'll still get the error occur.
I tried this before. To get the specific error, what i did was to use the query analyser. The last time i got this error in VB, i got another error from query analyser saying my update to the master table failed because of a collision key in the child table.

[michelle]

Hello Clunietp,

I am a VB5 user, but the description of the error you get on the next way

Err.Number
Err.Description

Michelle.

[LG]

Try this code, it might give you a detailed message of your error.

   Dim MyError As Error
   MsgBox Errors.Count
   For Each MyError In DBEngine.Errors
     With MyError
       MsgBox .Number & " " & .Description
     End With
   Next MyError