Stored procedure error:

[Child]

Hi, I am getting the error: "Procedure or function spAddActivity has too
many arguments specified. " on a stored procedure insert. I compared the
number of parameters in the function and the SP and they match. I compared
the type of the parameters and they match. I then made sure that everything
I wasn't inserting allowed nulls. The error persists. What else could
this be? Code and SP below. Its possible that I am stupid and missing the
obvious (due to give birth any minute and brain not quite as adept as
normal).

Thanks in advance!

Error: Procedure or function spAddActivity has too many arguments
specified.

Code:
Public Shared Function AddActivity(ByVal myActivity As Activity) As Integer

Dim conTA As New SqlConnection
Dim SInsert As String
Dim AddActSuccess As Boolean

conTA.ConnectionString =
ConfigurationSettings.AppSettings("ConnectionStrin g")
Dim cmdActivity As New SqlCommand
cmdActivity.CommandType = CommandType.StoredProcedure
cmdActivity.CommandText = "spAddActivity"
cmdActivity.Connection = conTA
cmdActivity.Parameters.Add("@orgid", myActivity.OrgID)
If myActivity.IndividualID = 0 Then
cmdActivity.Parameters.Add("@individualid", DBNull.Value)
Else
cmdActivity.Parameters.Add("@individualid", myActivity.IndividualID)
End If
cmdActivity.Parameters.Add("@apcauser", myActivity.APCAUser)
cmdActivity.Parameters.Add("@activitydescription",
myActivity.Description)cmdActivity.Parameters.Add( "@individualid",
myActivity.IndividualID)
If myActivity.ActivityDate = DateTime.MaxValue Then
cmdActivity.Parameters.Add("@ActivityDate", DBNull.Value)
Else
cmdActivity.Parameters.Add("@ActivityDate", myActivity.ActivityDate)
End If
cmdActivity.Parameters.Add("@ta", myActivity.TA)
If myActivity.TypeTA = 0 Then
cmdActivity.Parameters.Add("@typeta", DBNull.Value)
Else
cmdActivity.Parameters.Add("@typeta", myActivity.TypeTA)
End If
If myActivity.EvalRequested = DateTime.MaxValue Then
cmdActivity.Parameters.Add("@evalrequested", DBNull.Value)
Else
cmdActivity.Parameters.Add("@evalrequested", myActivity.EvalRequested)
End If
If myActivity.EvalCompleted = DateTime.MaxValue Then
cmdActivity.Parameters.Add("@evalcompleted", DBNull.Value)
Else
cmdActivity.Parameters.Add("@evalcompleted", myActivity.EvalCompleted)
End If

If myActivity.TACategory = 0 Then
cmdActivity.Parameters.Add("@tacategory", DBNull.Value)
Else
cmdActivity.Parameters.Add("@tacategory", myActivity.TACategory)
End If
conTA.Open()
AddActSuccess = True
Try
cmdActivity.ExecuteNonQuery()
Catch ex As Exception
AddActSuccess = False
'End Try
conTA.Close()
Return AddActSuccess
End Function


Stored Procedure:
CREATE PROCEDURE spAddActivity

@orgid as integer,
@individualid as integer,
@apcauser as varchar(50),
@activitydescription as ntext,
@activitydate as datetime,
@TA as bit,
@typeTA as integer,
@evalrequested as datetime,
@evalcompleted as datetime,
@tacategory as integer

AS
begin transaction

insert into tblActivities
(org, person, [user], activitydescription, activitydate,
technicalassistance, typeta, evalrequested, evalcompleted, tacategory)
values
(@orgid, @individualid, @apcauser, @activitydescription, @activitydate,
@TA, @typeTA, @evalrequested, @evalcompleted, @tacategory)

If @@Error <> 0
BEGIN
ROLLBACK TRAN
RETURN

END
COMMIT TRANSACTION
GO

[Kevin Spencer]

Hi Child,

You've added "@individualid" twice. First, conditionally, second
unconditionally:

If myActivity.IndividualID = 0 Then
cmdActivity.Parameters.Add("@individualid", DBNull.Value)
Else
cmdActivity.Parameters.Add("@individualid", myActivity.IndividualID)
End If
....
myActivity.Description)cmdActivity.Parameters.Add( "@individualid",
myActivity.IndividualID)
An easy mistake to make, and a hard one to find, so don't be hard on
yourself.

BTW, here's how I found it (and it wasn't easy!):

First, I counted the number of parameters in your SP. Then I counted the
number of additions of parameters in your code. In the query I came up with
10, after counting a couple of times to make sure. In the code, which was a
bit harder due to the conditional statements, I counted 11. I then had to
read through the code several times to find the duplicate (which, because it
was a duplicate, was rather hard to identify).

One way to avoid this in the future would be to put the addition of the
parameters in your code in the same order as the parameter list in the
query. You came close, but the order was a bit mixed up near the beginning.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Ambiguity has a certain quality to it.

"Child" <Sp**********@alaska.com> wrote in message
news:11*************@corp.supernews.com... Hi, I am getting the error: "Procedure or function spAddActivity has too
many arguments specified. " on a stored procedure insert. I compared the
number of parameters in the function and the SP and they match. I
compared the type of the parameters and they match. I then made sure that
everything I wasn't inserting allowed nulls. The error persists. What
else could this be? Code and SP below. Its possible that I am stupid
and missing the obvious (due to give birth any minute and brain not quite
as adept as normal).

Thanks in advance!

Error: Procedure or function spAddActivity has too many arguments
specified.

Code:
Public Shared Function AddActivity(ByVal myActivity As Activity) As
Integer

Dim conTA As New SqlConnection
Dim SInsert As String
Dim AddActSuccess As Boolean

conTA.ConnectionString =
ConfigurationSettings.AppSettings("ConnectionStrin g")
Dim cmdActivity As New SqlCommand
cmdActivity.CommandType = CommandType.StoredProcedure
cmdActivity.CommandText = "spAddActivity"
cmdActivity.Connection = conTA
cmdActivity.Parameters.Add("@orgid", myActivity.OrgID)
If myActivity.IndividualID = 0 Then
cmdActivity.Parameters.Add("@individualid", DBNull.Value)
Else
cmdActivity.Parameters.Add("@individualid", myActivity.IndividualID)
End If
cmdActivity.Parameters.Add("@apcauser", myActivity.APCAUser)
cmdActivity.Parameters.Add("@activitydescription",
myActivity.Description)cmdActivity.Parameters.Add( "@individualid",
myActivity.IndividualID)
If myActivity.ActivityDate = DateTime.MaxValue Then
cmdActivity.Parameters.Add("@ActivityDate", DBNull.Value)
Else
cmdActivity.Parameters.Add("@ActivityDate", myActivity.ActivityDate)
End If
cmdActivity.Parameters.Add("@ta", myActivity.TA)
If myActivity.TypeTA = 0 Then
cmdActivity.Parameters.Add("@typeta", DBNull.Value)
Else
cmdActivity.Parameters.Add("@typeta", myActivity.TypeTA)
End If
If myActivity.EvalRequested = DateTime.MaxValue Then
cmdActivity.Parameters.Add("@evalrequested", DBNull.Value)
Else
cmdActivity.Parameters.Add("@evalrequested", myActivity.EvalRequested)
End If
If myActivity.EvalCompleted = DateTime.MaxValue Then
cmdActivity.Parameters.Add("@evalcompleted", DBNull.Value)
Else
cmdActivity.Parameters.Add("@evalcompleted", myActivity.EvalCompleted)
End If

If myActivity.TACategory = 0 Then
cmdActivity.Parameters.Add("@tacategory", DBNull.Value)
Else
cmdActivity.Parameters.Add("@tacategory", myActivity.TACategory)
End If
conTA.Open()
AddActSuccess = True
Try
cmdActivity.ExecuteNonQuery()
Catch ex As Exception
AddActSuccess = False
'End Try
conTA.Close()
Return AddActSuccess
End Function


Stored Procedure:
CREATE PROCEDURE spAddActivity

@orgid as integer,
@individualid as integer,
@apcauser as varchar(50),
@activitydescription as ntext,
@activitydate as datetime,
@TA as bit,
@typeTA as integer,
@evalrequested as datetime,
@evalcompleted as datetime,
@tacategory as integer

AS
begin transaction

insert into tblActivities
(org, person, [user], activitydescription, activitydate,
technicalassistance, typeta, evalrequested, evalcompleted, tacategory)
values
(@orgid, @individualid, @apcauser, @activitydescription, @activitydate,
@TA, @typeTA, @evalrequested, @evalcompleted, @tacategory)

If @@Error <> 0
BEGIN
ROLLBACK TRAN
RETURN

END
COMMIT TRANSACTION
GO