Workaround to row cannot be located for updating

用ADO + ODBC操作Mysql database,用起来总不顺手。经常有莫名其妙的错误,其中不乏connector bugs。这些bugs不是我等能解决的,只有尽量不去触发bugs才是上策。

比如,我今天在使用recordset.update时被提示”Row cannot be located for updating. Some values may have been changed since it was last read.” 我排错了好久,最后总结出以下best practices:

  • 避免使用”SELECT *”来查询,用到哪些字段就SELECT那些字段;
  • 如果SELECT中的字段中,某些值没有改变,recordset.update 可能会出错(也就是说,所有字段值都被改变后才能update)
  • Connector/ODBC Application Specific Tips特别提到Access can’t always handle the MySQL DATE column properly. If you have a problem with these, change the columns to DATETIME.
  • Float或double类型字段值,避免用Float或Double运算后的值直接去更新,应该保留必要精度后的值去更新。例如,x和y都是double类型,total字段也是double类型,不要用x+y去更新total字段,若total只要保留两位小数,就用formatnumber(x+y, 2)去更新total。

综上,为了简单起见,如果要update a record,干脆先删除这条record,再recordset.addnew,赋予新值。对于新鲜记录的更新,以上四种可能的错误均没有出现。

Leave a comment

Your email address will not be published. Required fields are marked *