将 Excel 连接到 Mysql 更新问题 [英] Connecting Excel to Mysql Update PROBLEM

问题描述

我正在通过 excel 中的 vba 连接到一个 mysql 表，我正在更新它:

i am connecting to a mysql table through vba in excel and i am updating it:

Set cn = New ADODB.Connection
cn.Open "DRIVER={MySQL ODBC 5.1 Driver};" & _
    "SERVER=localhost;" & _
    "DATABASE=employees;" & _
    "USER=root;" & _
    "PASSWORD=M1llen;" & _
    "Option=3"
'lets get the batch info
'
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "batchinfo", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table from Report 1
'Set wsSheet1 = wbBook.Worksheets(1)
' better refer by name
'Set wsSheet1 = wbBook.Worksheets.("Report 1")
Worksheets.Item("Report 1").Select
dpath = Range("B2").Text
atime = Trim(Range("B3").Text)
rtime = Trim(Range("B4").Text)
lcalib = Trim(Range("B5").Text)
aname = Trim(Range("B6").Text)
rname = Trim(Range("B7").Text)
bstate = Trim(Range("B8").Text)

instrument = GetInstrFromXML()

With rs
    .AddNew ' create a new record
    ' add values to each field in the record
    .Fields("datapath") = "abc"
    .Fields("analysistime") = atime
    .Fields("reporttime") = rtime
    .Fields("lastcalib") = lcalib
    .Fields("analystname") = aname
    .Fields("reportname") = rname
    .Fields("batchstate") = bstate
    .Fields("instrument") = instrument
    .Update ' stores the new record
End With

问题是唯一被更新的字段是仪器字段！！

the issue is that the only field that gets updated is the instrument field!!

这里是批处理信息表 mysql 的描述:

here i a desc of the batchinfo table mysql:

mysql> desc batchinfo;
+--------------+---------+------+-----+---------+----------------+
| Field        | Type    | Null | Key | Default | Extra          |
+--------------+---------+------+-----+---------+----------------+
| rowid        | int(11) | NO   | PRI | NULL    | auto_increment |
| datapath     | text    | YES  |     | NULL    |                |
| analysistime | text    | YES  |     | NULL    |                |
| reporttime   | text    | YES  |     | NULL    |                |
| lastcalib    | text    | YES  |     | NULL    |                |
| analystname  | text    | YES  |     | NULL    |                |
| reportname   | text    | YES  |     | NULL    |                |
| batchstate   | text    | YES  |     | NULL    |                |
| instrument   | text    | YES  |     | NULL    |                |
+--------------+---------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

有趣的是，当我在没有 auto_increment 的情况下重新创建表时，它工作正常

funny thing is that when i recreate the table without the auto_increment then it works fine

我真的需要有人来回答这个问题，我不在乎你是否有预感或不确定，我会尝试任何解决方案

i really need someone to answer this question, i dont care if you have a hunch or not exactly sure, i will try any solution

推荐答案

我不熟悉 MySQL 但 TEXT 看起来像一个 blob 类型?如果是这样，我很惊讶它完全可以工作，因为 ADO 需要对 BLOBS 进行特殊处理( http://dev.mysql.com/tech-resources/articles/vb-blob-handling.html )

I'm not familiar with MySQL but TEXT looks like a blob type? If so I'm surprised it works at all as ADO requires special handling for BLOBS ( http://dev.mysql.com/tech-resources/articles/vb-blob-handling.html )

尝试使用 VARCHAR 类型.

你也可以试试ADOCn.Execute "INSERT ..."

这篇关于将 Excel 连接到 Mysql 更新问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！