Indexed MySQL field not saving value when creating/editing record through ADO
I was able to recreate your issue with the 64-bit version of the MySQL ODBC 5.3 Unicode Driver (5.03.04.00). It appears to be an issue with ADO Recordset updates when the last column in the table is of type TEXT
. I did not even have an index on userid
and I got the same results.
One possible workaround would be to use an ADODB.Command
with parameters to perform the insert using code similar to this:
Dim oConn As ADODB.ConnectionDim cmd As ADODB.CommandSet oConn = New ADODB.ConnectionoConn.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.Commandcmd.ActiveConnection = oConncmd.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.ExecuteSet cmd = NothingoConn.CloseSet oConn = Nothing
I tested it from an Access 2010 database and it worked fine for me.
I could be wrong but it appears that the ADO is expecting the table to generate the key, so while it 'keeps' your keep around until it sends it to the database, it is only using it as a place holder. And since the table isn't generating the key it defaults to 0.
Does the data definition for the IPhpList_AddHistory in the ADO have an AutoIncrement property set to true? If so it should be false.