sql - SQL 表更新有效,但仅从一行中获取值
问题描述
我正在创建一个自动文件,以在单击按钮时更新 CurrencyTable (dB) 中的货币汇率。我已经参考了这篇文章中的示例。我的Excel 数据如下:
CsName Rate
AUD 1.23
BHD 2.23
BND 3.23
EUR 4.23
IDR 5.23
INR 6.23
MYR 7.23
OMR 8.23
SGD 9.23
THB 10.23
USD 11.23
该代码似乎工作正常,但是它没有根据 Excel 数据进行更新,相反,它只为所有内容获取了第 4 行的值。 这是更新的结果。
以下是我的代码:
Sub UpdateCR()
Dim con As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim i As Long
Dim vDB As Variant
Dim Ws As Worksheet
con.ConnectionString = "Provider=SQLOLEDB.1;Password=1234;Persist Security Info=False;User ID=Guest;Initial Catalog=ABC;Data Source=XYZ"
con.Open
Set cmd.ActiveConnection = con
Set Ws = ActiveSheet
vDB = Ws.Range("a1").CurrentRegion
For i = 2 To UBound(vDB, 1)
cmd.CommandText = "UPDATE CurrencyTable SET Rate='" & vDB(i, 2) & "' WHERE CsName ='" & vDB(i, 1) & "' "
cmd.Execute
Next i
con.Close
MsgBox "Exchange rates successfully updated"
Set con = Nothing
End Sub
有谁知道为什么会这样?它与UBound函数有关吗?
对此的任何帮助将不胜感激!
解决方案
首先,这不是答案,我在这里发布以解释故障排除,因为评论无济于事。
我所说的是打印另一列中正在执行的字符串。 例如: 一旦你尝试这个,你能显示第三列的图像吗?
Sub UpdateCR()
Dim con As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim i As Long
Dim vDB As Variant
Dim Ws As Worksheet
con.ConnectionString = "Provider=SQLOLEDB.1;Password=1234;Persist Security Info=False;User ID=Guest;Initial Catalog=ABC;Data Source=XYZ"
con.Open
Set cmd.ActiveConnection = con
Set Ws = ActiveSheet
vDB = Ws.Range("a1").CurrentRegion
For i = 2 To UBound(vDB, 1)
cmd.CommandText = "UPDATE CurrencyTable SET Rate='" & vDB(i, 2) & "' WHERE CsName ='" & vDB(i, 1) & "' "
cmd.Execute
Cells(i, 3) = "UPDATE CurrencyTable SET Rate='" & vDB(i, 2) & "' WHERE CsName ='" & vDB(i, 1) & "' "
Next i
con.Close
MsgBox "Exchange rates successfully updated"
Set con = Nothing
End Sub