# Calling Stored Procedures with Lots of Parameters

I have this piece of code for running a Stored Procedure, and I was wondering if there is a way of cutting code like:

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


As I have a lot of stored procedures in my system this takes a lot of time, my boss seems to think there could be a way to possible cut this down to one line of code and make it a lot easier to read and write, though I'm not sure what way to go about this.

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

' Error Checking
On Error GoTo Err_SaveProperty

' Dimension Local Variables

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

' Run Stored Procedure - Save Property Record
With uCommand
.ActiveConnection = uDBase
.CommandTimeout = 0
.Parameters.Append(uPar)
.Parameters("@PropertyID").Value = Val(lblPropertyIDValue.Text)
.Parameters.Append(uPar)
.Parameters("@PropertyManager").Value = cmbPropertyManager.Text
.Parameters.Append(uPar)
.Parameters.Append(uPar)
.Parameters.Append(uPar)
.Parameters.Append(uPar)
.Parameters("@Town").Value = txtTown.Text
.Parameters.Append(uPar)
.Parameters("@PostCode").Value = txtPostCode.Text
.Parameters.Append(uPar)
.Parameters("@Availabilty").Value = chkAvailable.Checked
.Parameters.Append(uPar)
.Parameters("@Available").Value = dtpAvailable.Text
.Parameters.Append(uPar)
.Parameters("@Factored").Value = -chkFactored.Checked
.Parameters.Append(uPar)
.Parameters("@FactorsName").Value = txtFactorName.Text
.Parameters.Append(uPar)
.Parameters("@FactorsEmail").Value = txtFactorsEmail.Text
.Parameters.Append(uPar)
.Parameters("@PropertyBuilt").Value = dtpPropertyBuilt.Text
.Parameters.Append(uPar)
.Parameters("@PropertyValue").Value = txtPropertyValue.Text
.Parameters.Append(uPar)
.Parameters("@MimimumFee").Value = txtMinimumFee.Text
.Parameters.Append(uPar)
.Parameters("@Commission").Value = -txtCommision.Text
.Parameters.Append(uPar)
.Parameters("@CostSuthorisationAmount").Value = txtCostAuthorisationAmount.Text
.Parameters.Append(uPar)
.Parameters("@Vacant").Value = chkVacant.Checked
.Parameters.Append(uPar)
.Parameters("@VacantDate").Value = dtpVacant.Text
.Parameters.Append(uPar)
.Parameters("@StartingRent").Value = txtStartingRent.Text

.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

End Function


First, your question, then something else I noticed. You have a few options to clean this up. There's the easy way and the robust way. Let's look at the easy way first so you can make your boss happy.

# The Question

## Option 1: Instant Gratification

1. You can set the value of the parameter at the same you create it. This will remove one line of code for every parameter you have. For example:

uPar = .CreateParameter("@PropertyManager", ADODB.DataTypeEnum.adLongVarChar, ADODB.ParameterDirectionEnum.adParamInput, 60)
.Parameters.Append(uPar)
.Parameters("@PropertyManager").Value = cmbPropertyManager.Text


Becomes:

uPar = .CreateParameter("@PropertyManager", ADODB.DataTypeEnum.adLongVarChar, ADODB.ParameterDirectionEnum.adParamInput, 60, cmbPropertyManager.Text)
.Parameters.Append(uPar)

2. You can one-line the creation and appending of each parameter. This will improve readability and do away with the uPar variable entirely. It comes along with slightly more difficult debugging though. Like all things, it's a trade off.

.Parameters.Append .CreateParameter("@PropertyID", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, , Val(lblPropertyIDValue.Text))
' etc....


## Option 2: Create A Custom Class

Whenever we work with a class that has a terrible API, some times it makes a lot of sense to wrap that class in one of our own making. Then we get to define the API that we would like to work with ourselves. I won't go into detail about how I would go about this, but I will refer you to several other people who have experienced the same problem you're coming up against. Perhaps their work will inspire your own, or maybe you can down right steal use their code. (It's just a joke, the code I'm linking to is licensed CC-BY-SA. You're totally free to use it.)

# Review

This is Code Review after all, so I'd be remiss not to actually review your code a bit.

• Drop the Hungarian notation. The IDE will tell you what type a variable is. Perhaps it's a company standard, but unless you're working with , this kind of notation is ill advised. The biggest reason is because if the type of a variable changes, you either have to change the variable's name or let the notation lie to you. It's unnecessary maintenance and confusion.
• If you just have to keep using this notation, at least do it right. It seems that you use u as a prefix for all objects. Don't do that. Be specific and explicit. cmd is proper for a command, prm for a parameter etc. Doing this would force you away from the generic uPar and uCommand names and into something more descriptive. Win-win.
• I'm a little concerned about how you're keeping track of the state of the connection.

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

' lots of code

If bConnection Then CloseConnection()


This boolean doesn't actually tell you if the connection is open or not. Something could go wrong with the OpenConnection() sub. It would be better to actually return a reference to the connection and check its state explicitly.

• Why is the .Parameters.Append come with error – Richard Glass Jan 23 '15 at 15:14
• no accessible append accepts this number of arguments – Richard Glass Jan 23 '15 at 15:15
• You have to define a length for this line. .Parameters.Append .CreateParameter("@PropertyID", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, , Val(lblPropertyIDValue.Text)) should be something like .Parameters.Append .CreateParameter("@PropertyID", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput, 50, Val(lblPropertyIDValue.Text)) – RubberDuck Jan 23 '15 at 15:20
• I copied your code, so I'm surprised you're just now seeing this error. – RubberDuck Jan 23 '15 at 15:20
• yeah i Spotted that but it does not seem to fix the problem I am getting the error on the the .Parameters.Append saying no accessible append accepts this number of arguments, im fairly new to this so i appreciate the advice – Richard Glass Jan 23 '15 at 15:27