ADODB连接以使用绑定变量将数据上传到SQL [英] ADODB Connection to upload data to SQL using bind variables

问题描述

我正在尝试将一些数据从电子表格上传到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...

1. 您可以在传递参数时省略 @


2. 您应该在 cmdCommandText 行
$ b $之后添加 cmd.CommandType = adCmdText b
3. 我会将SQL查询更改为：

1. you can omit @ when passing parameters
2. you should add cmd.CommandType = adCmdText right after your cmdCommandText line
3. 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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！