vba - 调用带有大量参数的存储过程

Question

嘿，我在这段代码中遇到了这个错误，我不知道为什么。这将是一个很大的帮助，因为我试图让我的代码更早阅读

Public Function SaveProperty() As Boolean
'** Save Current Personal Data Record

' Error Checking
On Error GoTo Err_SaveProperty

' Dimension Local Variables
Dim uRecSnap As ADODB.Recordset
Dim uPar As ADODB.Parameter

' Check For Open Connection
If uDBase Is Nothing Then
    OpenConnection()
    bConnection = True
End If

' Run Stored Procedure - Save Property Record
uCommand = New ADODB.Command
With uCommand
    .ActiveConnection = uDBase
    .CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
    .CommandTimeout = 0
    .Parameters.Append.CreateParameter("@PropertyID", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, 50, Val(lblPropertyIDValue.Text))
    .Parameters.Append.CreateParameter("@PropertyManager", ADODB.DataTypeEnum.adLongVarChar, ADODB.ParameterDirectionEnum.adParamInput, 60, cmbPropertyManager.Text)
    .Parameters.Append.CreateParameter("@AddressLine1", ADODB.DataTypeEnum.adLongVarChar, ADODB.ParameterDirectionEnum.adParamInput, 30, txtAddress1.Text)
    .Parameters.Append.CreateParameter("@AddressLine2", ADODB.DataTypeEnum.adLongVarChar, ADODB.ParameterDirectionEnum.adParamInput, 30, txtAddress2.Text
'...ETC

.CommandText = "PropertyMaster_SaveRecord"

    .Execute()
End With

' Close Connection
uRecSnap = Nothing
uCommand = Nothing
If bConnection Then CloseConnection()
SaveProperty = True

Err_SaveProperty:
If Err.Number <> 0 Then
    sErrDescription = Err.Description
    WriteAuditLogRecord("clsProperty", "SaveProperty", "Error", sErrDescription)
    SaveProperty = False
End If

结束函数

我已经减少了这样的代码行数

 uPar = .CreateParameter("@LandlordID", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput)
    .Parameters.Append(uPar)
    .Parameters("@LandlordID").Value = Val(lblLandlordID.Text)

Answer 1

欢迎来到精彩的注意事项 With陈述!

正如@RubberDuck 暗示的那样，您的问题在这里:

.Parameters.Append.CreateParameter(...)

应该:

.Parameters.Append .CreateParameter(...)

没有 With块，你会有这个:

uCommand.Parameters.Append uCommand.CreateParameter(...)

Append和 CreateParameter都是 uCommand的成员 With 的对象块正在使用。