Calling stored procedures with many parameters

Question

Hey, I get this error with this piece of code, and I'm not sure why. That would be a big help as I try to make my code a little older to read

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

Final function

I cut the lines of code because of something like this

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

Answer 1

Welcome to the reservation of a wonderful statement With!

As @RubberDuck suggests, your problem is here:

.Parameters.Append.CreateParameter(...)

Must be:

.Parameters.Append .CreateParameter(...)

Without a block With, you will have the following:

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

Appendand CreateParameterare members of the object uCommandwith which the block works With.

Answer 2

, , :

' Run Stored Procedure - Save Property Record
Set uCommand = New ADODB.Command  'Note use of Set keyword
With uCommand
    .ActiveConnection = uDBase
    .CommandType = adCmdStoredProc 'Don't need the entire object hierarchy here
    .CommandTimeout = 0
    .CommandText = "PropertyMaster_SaveRecord"
    .Parameters.Refresh
    .Parameters(1) = cmbPropertyManager.Text 'Collections are usually 1-based in VB6
    .Parameters(2) = txtAddress1.Text
    .Parameters(3) = txtAddress2.Text
    '...ETC
    .Execute()
End With

. , , . , , ; , , .

, ; . , .

, Set .

, :

If uDBase Is Nothing Then
    OpenConnection()
    bConnection = True
End If

:

If uDBase.State = adStateClosed Then
    OpenConnection()
    bConnection = True '?? Probably don't need this, see below
End If

, , , , - uDBase . . , , bConnection, , , , State.

OpenConnection GetConnection .. :

Public Function GetConnection() As ADODB.Connection
    Static myConn As ADODB.Connection
    If myConn.State = adStateClosed Then
        Set myConn = New ADODB.Connection
        myConn.Open "myConnectionString"
    End If
    GetConnection = myConn
End Function

, . uDBase. , , , .