MySQL adding error [Resolved]

[manavo11]

I connect to a remote MySQL database. But sometimes when I go to add a new record, i get this error :

ERROR : -2147217887

Multiple-step operation generated errors. Check each status value.

Any ideas? What I do when I connect to the DB is :

With Rs
        .AddNew 'adding new record
        .Fields("Username") = sUsername 
        .Fields("Password") = sPassword 
        .Update 'this updates the recordset etc.
    End With

Thanks

[manavo11]

OK. This only happens when the length is >6 Even if the datatype of the field is Varchar(20) or even 40. Still problems with over 6. With 6, it works. Any ideas?

Thanks

[manavo11]

This seems to be a problem with MySQL 3.x . I tried with MySQL 4.x and I don't have this problem.... Oh well

[manavo11]

Well, that wasn't it.... I tried with another DB again that was MySQL 4.x and I had the same error again So is this something that just depends on the server providing the database?

Thanks

[manavo11]

Bump?

[AtomSoft]

Can you post FULL code. Modify your personal info out tho.

[AtomSoft]

ok this works using VBMySQLDirect its easy to use. But you must distribute the dll's with the program. Here is the site link:
Download Page
Home Page

    Dim pCnn As MYSQL_CONNECTION
    Dim pRs As MYSQL_RS

    Set pCnn = New MYSQL_CONNECTION

    ' User/pass that connect to mysql
    pCnn.OpenConnection "127.0.0.1", "Username", "Password", "atomsoftweb"

 Set pRs = New MYSQL_RS
 Dim SQL1 As String
 
 Dim Var As String, Var2 As String, var3 As String
 Var = "theUsername" ' This is the username you want to enter into a new record.
 Var2 = "ThePassword" ' This is the password encryted.
 var3 = Chr(34) & Var & Chr(34) & ", " & Chr(34) & Var2 & Chr(34)
 
 SQL1 = "INSERT into users (username, password) VALUES (" & var3 & ");"
 
 pRs.OpenRs SQL1, pCnn
    
'Put some error handling here
 pCnn.CloseConnection
 Set pCnn = Nothing

[AtomSoft]

or if you want try this ok.
Note: pCnn is the connection to mysql ok

    With rs
        .OpenRs "select * from users", pCnn
        .AddNew 'adding new record
        .Fields("Username") = sUsername 
        .Fields("Password") = sPassword 
        .Update 'this updates the recordset etc.
    End With

[manavo11]

Well, it was the connection string It seems I had to use the OPTION=1 to not "Optimize column width". The magic number was 6 because the first username I put was 6 characters long. So after that everything was trying to get optimized (was getting limited) to 6 characters.

Well, at least that is my theory... Can anyone back it up (or crush it)?

[AtomSoft]

What is your current Connection String ? If you want to be help supply some code. Maybe thats why no one is helping they have yet to see what you got done so far. Even if something is wrong show the code so we can try to help.

[manavo11]

Never mind... That was the problem. I tested it and now it works fine Thanks