Stored Procedure Only allowing one parameter

[T.J. Bernard]

I am working on an ADP project in which I want to update
my table using a stored procedure. The update is written
as a stored procedure that has two input parameters. One
parameter determines the correct record (unique ID) the
other parameter is the value to update the specified field
to. These values are determined on a form, so when a
certain record is displayed and the update value is
entered, a button is clicked and I am currently running
the following code:

Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim Param1 As ADODB.Parameter
Dim Param2 As ADODB.Parameter
Dim UsageDec As String
Dim TapeNum As String

Usage.Setfocus
UsageDec = Usage.Text

Tape.Setfocus
TapeNum = Tape.Text

Set cnn = CurrentProject.Connection
Set cmd.ActiveConnection = cnn

Set Param1 = cmd.CreateParameter("Input", adInteger,
adParamInput)
cmd.Parameters.Append Param1
Param1.Value = UsageDec

Set Param2 = cmd.CreateParameter("Input2", adVarChar,
adParamInput)
cmd.Parameters.Append Param2
Param2.Value = TapeNum

cmd.CommandText = "qryUpdateUsageUnknown2"
cmd.CommandType = adCmdStoredProc

cmd.Execute

Everytime the code breaks at "cmd.Parameters.Append
Param2" with the error: "Run-time error 3708. Parameter
Object is improperly defined. Inconsistent or incomplete
information was provided."

I am not sure why I am getting this error. I am using ADO
2.6 library.

If anyone has any ideas let me know.

Thank you,

T.J.

 

[J. Clay]

T.J.

Four things. 1) You don't need to define a separate variable for each
parameter, you just reuse the Parameter over and over. 2) You need to
include the value within the CreateParameter call. 3) You need to define
the variable length in the CreateParameters call. 4) Make sure you clear out
the objects by setting them to nothing after use.


Example:
'********* Code Start
Dim cmd As New ADODB.Command
Dim prm As ADODB.Parameter
Dim UsageDec As Integer 'This variable you are passing should by
similar type you are passing to
Dim TapeNum As String

UsageDec = CInt(Me!Usage)
'Assuming you are getting this from the current form and field
called Usage
'and you need to convert it to Integer to match your SP.
'If the values can be large you may consider using Long instead of
Integer

TapeNum = Me!Tape 'same as above

set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
'No need to open separate connection using cnn,
'this just creates more objects to deal with
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "qryUpdateUsageUnknown2"
Set prm = cmd.CreateParameter("Input", adInteger, adParamInput, , UsageDec)
'The blank is length you will use below
'put the value right inside the call
cmd.Parameters.Append prm

Set prm = cmd.CreateParameter("Input2", adVarChar, adParamInput, 50, TapNum)
'Need to define the VarChar length
'This should match your stored procedure
cmd.Parameters.Append prm

cmd.Execute

'ALWAYS clear objects from memory once no longer needed
Set prm = Nothing
Set cmd = Nothing

'********** Code End

HTH,
J. Clay