gpt4 book ai didi

sql - 仅从 Excel 插入几行后出现溢出错误

转载 作者:行者123 更新时间:2023-12-04 20:39:30 25 4
gpt4 key购买 nike

我正在尝试将数据库的副本设置到一个 Excel 文档中,销售代表可以在他们在现场时对其进行更新。我已经为列表行运行了插入语句的代码,但是仅在 20 行之后出现了溢出错误。这是我的代码:

Sub ConnectTODB()

Dim CustomersConn As ADODB.Connection
Dim CustomersCmd As ADODB.Command
Dim lo As Excel.ListObject
Dim ws As Excel.Worksheet
Dim lr As Excel.ListRow

Set ws = ThisWorkbook.Worksheets(8)
Set lo = ws.ListObjects("TCustomers")

Set CustomersConn = New ADODB.Connection
Set CustomersCmd = New ADODB.Command

CustomersConn.ConnectionString = SQLConStr
CustomersConn.Open
CustomersCmd.ActiveConnection = CustomersConn

For Each lr In lo.ListRows
CustomersCmd.CommandText = _
GetInsertText( _
Intersect(lr.Range, lo.ListColumns("Type").Range).Value, _
Intersect(lr.Range, lo.ListColumns("Customer").Range).Value, _
Intersect(lr.Range, lo.ListColumns("Name").Range).Value, _
Intersect(lr.Range, lo.ListColumns("Contact").Range).Value, _
Intersect(lr.Range, lo.ListColumns("Email").Range).Value, _
Intersect(lr.Range, lo.ListColumns("Phone").Range).Value, _
Intersect(lr.Range, lo.ListColumns("Corp").Range).Value)

CustomersCmd.Execute
Next lr

CustomersConn.Close

Set CustomersConn = Nothing

End Sub

Function GetInsertText(CType As String, Customer As Integer, Name As String, Contact As String, Email As String, Phone As String, Corp As String) As String
Dim SQLStr As String

SQLStr = _
"INSERT INTO Customer (" & _
"Type, Customer,Name,Contact,Email,Phone,Corp)" & _
"VALUES (" & _
"'" & CType & "'," & _
"'" & Customer & "'," & _
"'" & Name & "'," & _
"'" & Contact & "'," & _
"'" & Email & "'," & _
"'" & Phone & "'," & _
"'" & Corp & "')"

GetInsertText = SQLStr

End Function

总表大小只有 1600 行,所以我没想到会出现溢出错误。
任何帮助,将不胜感激!

最佳答案

您收到溢出错误,因为其中一个变量无法处理给它的输入。我强烈怀疑罪魁祸首是Customer As Integer
Function GetInsertText(CType As String, Customer As Integer, Name As String, Contact As String, Email As String, Phone As String, Corp As String) As String
更改Customer As IntegerCustomer As Long

关于sql - 仅从 Excel 插入几行后出现溢出错误,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/36581084/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com