ASP error "Key column information is insufficient or incorrect." when updating mySQL

Question

I have the following subroutine in a classic ASP class that calls the ADO UPDATE method. I'm using mySQL db using ODBC driver 5.1.

If I all the subroutine I get the following error:

Microsoft Cursor Engine error '80004005' Key column information is insufficient or incorrect. Too many rows were affected by update.

Public Sub Update(table,id_field,id,fields,values)
    Dim yy
    Dim strQuery
    Dim strFields
    Const adOpenDynamic = 2
    Const adLockOptimistic = 3
    Const adCmdText = 1
    strQuery = ""
    For yy = LBound(fields) to UBound(fields)
            strQuery = strQuery & fields(yy) & ", "
    Next
    strQuery = Left(strQuery, Len(strQuery) - 2)
    strQuery = "select " & strQuery & " from " & table & " where " & id_field & "=" & id
    i_objRS.CursorLocation = 3
    i_objRS.Open strQuery, i_objDataConn, adOpenDynamic, adLockOptimistic, adCmdText
    For yy = 0 To UBound(fields)
            i_objRS(fields(yy)) = values(yy)
    Next
    i_objRS.Update
    i_objRS.Close
End Sub

I've tried changing the cursorlocation property and the open parameters but still cannot get it to work. The table I'm updating has a unique auto id which is the primary key.

Answer 1

Hmm. Do you need to do this through ADO? Try running the query against a connection object and see what happens?

Answer 2

The first thing to do in diagnosing dynamically created SQL is to examine the SQL string actually created. However using a dynamic cursor to perform updates is not a good way to acheive an update for a row.

Use an ADODB.Command object instead and create an Update command. Note don't concatenate the values into the created SQL command. Instead use parameter place marks (usually ? but I can't remember if that varies my DBengine (I'm not a mySQL bod)) and add parameters to the ADODB.Command parameters collection.