MySQL indexed field does not save value when creating / editing records via ADO

Question

I am using ADO to add a record to a MySQL table. The code works fine, and the data is sent to a new record except userid. I can’t understand why.

'---------------------------------------------------------------------------------------
' Procedure : IPhpList_AddHistory
' Author    : Adam
' Date      : 12/8/2014
' Purpose   : Add a history record to the phpList account.
'---------------------------------------------------------------------------------------
'
Private Sub IPhpList_AddHistory(cUser As clsUser, cHistory As clsHistory)

    Dim strSQL As String
    Dim rst As ADODB.Recordset

    Set rst = New ADODB.Recordset
    strSQL = "phplist_user_user_history limit 0,1"
    rst.Open strSQL, oConn, adOpenDynamic, adLockOptimistic

    With rst
        .AddNew
            !userid = cUser.PhpListID
            !ip = ""
            ![Date] = cHistory.AddDate
            !Summary = cHistory.Subject
            !Detail = cHistory.Body
            !systeminfo = "Automated syncronization process. " & VBE.ActiveVBProject.Description
        .Update
        .Close
    End With

    ' Clear reference
    Set rst = Nothing

End Sub

Seems pretty simple, but as a result of writing to MySQL there is no value userid.

There is an index in the column, but that doesn't seem to stop me from setting the value. (And no, there are no triggers in this table.)

When I look at the code, I see that the value for this field is set, and there are no errors when updating the record, but for some reason it just returns to zero when adding the record.

, .Execute(strSQL), , ADO .

, userid , . , ( ).

Update

... , ..... , . / userid. ( )

, , userid. ( ?)

, MySQL, ADO, . . ADO, .

INSERT INTO `test`.`phplist_user_user_history`(`id`,`userid`,`ip`,`date`,`summary`,`detail`,`systeminfo`) 
VALUES (DEFAULT,DEFAULT,'',_binary'2014-12-18 11:51:13','My Subject','New Details','IBLP automated syncronization process. Version 1.0')

INSERT INTO `test`.`phplist_user_user_history`(`id`,`userid`,`ip`,`date`,`summary`,`detail`,`systeminfo`,`testcol`) 
VALUES (DEFAULT,456,'',_binary'2014-12-18 13:22:44','My Subject','New Details','IBLP automated syncronization process. Version 1.0',1)

, insert DEFAULT I, ADO.

, , testcol. -, INT(11) ??

, , VBA ( userid):

. .Update , .

​​ ODBC? MySQL ODBC (5.3.4) :

"Driver=MySQL ODBC 5.3 Unicode Driver;SERVER=myserver;UID=myuser;PWD={mysecretpwd};DATABASE=test;PORT=3306;DFLT_BIGINT_BIND_STR=1"

Answer 1

64- MySQL Unicode Driver (5.03.04.00). , ADO Recordset, TEXT. userid, .

ADODB.Command , :

Dim oConn As ADODB.Connection
Dim cmd As ADODB.Command

Set oConn = New ADODB.Connection
oConn.Open _
        "Driver=MySQL ODBC 5.3 Unicode Driver;" & _
        "SERVER=localhost;" & _
        "UID=root;" & _
        "PWD=whatever;" & _
        "DATABASE=mydb;" & _
        "PORT=3306;" & _
        "DFLT_BIGINT_BIND_STR=1"

Set cmd = New ADODB.Command
cmd.ActiveConnection = oConn
cmd.CommandText = _
        "INSERT INTO phplist_user_user_history " & _
        "(`userid`, `ip`, `date`, `Summary`, `Detail`, `systeminfo`) " & _
        "VALUES (?,?,?,?,?,?)"
cmd.Parameters.Append cmd.CreateParameter("?", adInteger, adParamInput, , 456)
cmd.Parameters.Append cmd.CreateParameter("?", adVarWChar, adParamInput, 255, "")
cmd.Parameters.Append cmd.CreateParameter("?", adDBTimeStamp, adParamInput, 255, Now)
cmd.Parameters.Append cmd.CreateParameter("?", adVarWChar, adParamInput, 255, "cHistory.Subject")
cmd.Parameters.Append cmd.CreateParameter("?", adLongVarWChar, adParamInput, 2147483647, "cHistory.Body")
cmd.Parameters.Append cmd.CreateParameter("?", adLongVarWChar, adParamInput, 2147483647, "Automated syncronization process.")
cmd.Execute

Set cmd = Nothing
oConn.Close
Set oConn = Nothing

Access 2010, .

Answer 2

, , ADO , , , "" , , . , 0.

IPhpList_AddHistory ADO AutoIncrement, true? , .

http://blogs.msdn.com/b/vsdata/archive/2009/09/14/refresh-the-primary-key-identity-column-during-insert-operation.aspx

Answer 3

, ? , Access , , .