时间戳

首页 > 解决方案 > 使用 VBA 运行存储过程

excel - 使用 VBA 运行存储过程

问题描述

我正在尝试制作 vba 代码来运行存储过程，但我在最后一行不断收到错误消息。错误是

“对象或列名丢失或为空。对于 SELECT INTO 语句，请验证每个列都有名称。对于其他语句，请查找空别名。不允许定义为“”或 [] 的别名。将别名更改为有效名称。”

看起来这告诉我我在最后一行有语法错误。我该如何解决这个错误？

 Sub Button_Update()
    'TRUSTED CONNECTION
        On Error GoTo errH
        Dim con As New ADODB.Connection
        Dim rs As New ADODB.Recordset
        Dim strPath As String
        Dim intImportRow As Integer
        Dim CID, LastID As String
        Dim RDate As Variant
        Dim strRecordID As Integer
        Dim server, username, Password, table, database As String


    If MsgBox("Do you want to update this line?", vbYesNo) = vbNo Then Exit Sub

        With Sheets("User Input")

                server = "wesrtednt-prod"
                table = "pr_Load"
                database = "Prod"



                If con.State <> 1 Then

                   con.Open "Provider=SQLOLEDB;Data Source=" & server & ";Initial Catalog=" & database & ";Integrated Security=SSPI;"
                    'con.Open

                End If
                'this is the TRUSTED connection string

                Set rs.ActiveConnection = con


                'set first row with records to import
                intImportRow = 2

                Do Until .Cells(intImportRow, 1) = ""
                CID= .Cells(intImportRow, 1)
                    RDate = .Cells(intImportRow, 2)
                    LastID= .Cells(intImportRow, 3)

    If RDate = "" Then RDate = Null



    con.Execute "pr_Load'" & CID& "','" & RDate & "'"""

标签： excelvbastored-procedures

解决方案

过程名称和第一个参数“pr_Load”和CID之间应该有一个空格。整数参数不需要引号，NULL 也不需要，并且日期可能是最佳格式 yyyy-mm-dd。例如

With Sheets("User Input")
    Do Until .Cells(intImportRow, 1) = ""

        CID = .Cells(intImportRow, 1)
        rdate = .Cells(intImportRow, 2)

        sql = "pr_load " & CID
        If rdate = "" Then
            sql = sql & ",NULL"
        Else
            sql = sql & ",'" & Format(rdate, "yyyy-mm-dd") & "'"
        End If
        Debug.Print intImportRow, sql
        con.Execute sql
        intImportRow = intImportRow + 1
    Loop
End With

如果要使用参数，请尝试

Dim cmd As Object
Set cmd = CreateObject("ADODB.Command")
With cmd
    .ActiveConnection = con
    .CommandType = adCmdStoredProc
    .CommandText = "pr_load"
    .Parameters.Append .CreateParameter("P1", adInteger, adParamInput)
    .Parameters.Append .CreateParameter("P2", adDate, adParamInput)
End With

Dim intImportRow As Long, CID As Integer, rdate As Date, sqldate As String
intImportRow = 2
With Sheets("User Input")
    Do Until .Cells(intImportRow, 1) = ""

        CID = .Cells(intImportRow, 1)
        rdate = .Cells(intImportRow, 2)

        cmd.Parameters(0).Value = CID
        If rdate = 0 Then
            cmd.Parameters(1).Value = Null
            sqldate = "NULL"
        Else
            cmd.Parameters(1).Value = rdate
            sqldate = Format(rdate, "YYYY-YY-DD")
        End If

        Debug.Print intImportRow, CID, sqldate
        cmd.Execute

        intImportRow = intImportRow + 1
    Loop
End With

推荐阅读