问题描述
我正在尝试将一些数据从电子表格上传到SQL数据库。我正在使用中的代码基本,必须进行一些修改才能使其正常工作。
I'm trying to upload some data into a SQL database from a spreadsheet. I'm Using the code from here as a base, had to make a few mods in order to get it to work.
Sub newtest()
Dim conn As ADODB.Connection
Dim cmd As New ADODB.Command
Dim cs As String
Dim C1, C2, C3, C4 As String
Dim RowNo As Long
Dim wbk As Workbook
Dim strFile As Variant
Dim shtname As String
ChDir "xxxxxx"
strFile = Application.GetOpenFilename
If strFile = False Then
Exit Sub
Else: End If
Application.ScreenUpdating = False
Set conn = New ADODB.Connection
cs = "Provider=SQLOLEDB;Data Source=xxxxx;Initial Catalog=xxxx;User ID=xxx; Password=xxxxxx; "
conn.ConnectionString = cs
conn.Open
' *** Open workbooks first ***
Set wbk = Workbooks.Open(strFile)
shtname = ActiveWorkbook.Worksheets(1).Name
lastrow = wbk.Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row + 1
With Sheets(shtname)
cmd.ActiveConnection = conn
cmd.CommandText = "insert into EcommerceXRefMapping_C_Copy values (@EquivalentPartNumber_C, @Manufacturer_C, @CatamacPartNumber_C, @Notes_C)"
cmd.NamedParameters = True
'Skip the header row
RowNo = 2
'Loop until empty cell in Col 1
Do Until .Cells(RowNo, 1) = ""
i = i + 1
C1 = .Cells(RowNo, 1)
C2 = .Cells(RowNo, 2)
C3 = .Cells(RowNo, 3)
C4 = .Cells(RowNo, 4)
'Generate and execute sql statement to import the excel rows to SQL Server table
cmd.Parameters.Append cmd.CreateParameter("@EquivalentPartNumber_C", adVarChar, adParamInput, 256, C1)
cmd.Parameters.Append cmd.CreateParameter("@Manufacturer_C", adVarChar, adParamInput, 256, C2)
cmd.Parameters.Append cmd.CreateParameter("@CatamacPartNumber_C", adVarChar, adParamInput, 256, C3)
cmd.Parameters.Append cmd.CreateParameter("@Notes_C", adVarChar, adParamInput, 256, C4)
cmd.Execute
RowNo = RowNo + 1
Loop
End With
wbk.Close
Application.ScreenUpdating = True
End Sub
尝试执行时遇到错误
cmd.execute
错误消息是
Run-time error'-2147217800 (80040e14)':
Must declare the scalar variable "@EquivalentPartNumber_C".
任何想法如何解决?我以为声明变量是用 @字符完成的?抱歉,我是VBA的新手,请多多包涵。
Any ideas how to fix? I thought that the declaring of the variables was done with the "@" character? Sorry, I'm quite new to VBA so please bear with me.
谢谢!
推荐答案
在我看来,您混合了存储过程
和 text
命令类型的表示法。
In my opinion you mix notation for stored procedure
and text
type of command but...
- 您可以在传递参数时省略
@
- 您应该在
cmdCommandText
行 $ b $之后添加 - 我会将SQL查询更改为:
cmd.CommandType = adCmdText
b - you can omit
@
when passing parameters - you should add
cmd.CommandType = adCmdText
right after yourcmdCommandText
line - I would change SQL query into:
插入EcommerceXRefMapping_C_Copy值(?,?, ?,?)
以及通过这种方式的下一遍参数(在正确的顺序很重要的情况下):
and next pass parameters in this way (where correct order is important):
cmd.Parameters(0) = c1
cmd.Parameters(1) = c2
cmd.Parameters(2) = c3
cmd.Parameters(3) = c4
这篇关于ADODB连接以使用绑定变量将数据上传到SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!