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(ByV al myActivity As Activity) As Integer

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

conTA.Connectio nString =
ConfigurationSe ttings.AppSetti ngs("Connection String")
Dim cmdActivity As New SqlCommand
cmdActivity.Com mandType = CommandType.Sto redProcedure
cmdActivity.Com mandText = "spAddActiv ity"
cmdActivity.Con nection = conTA
cmdActivity.Par ameters.Add("@o rgid", myActivity.OrgI D)
If myActivity.Indi vidualID = 0 Then
cmdActivity.Par ameters.Add("@i ndividualid", DBNull.Value)
Else
cmdActivity.Par ameters.Add("@i ndividualid", myActivity.Indi vidualID)
End If
cmdActivity.Par ameters.Add("@a pcauser", myActivity.APCA User)
cmdActivity.Par ameters.Add("@a ctivitydescript ion",
myActivity.Desc ription)cmdActi vity.Parameters .Add("@individu alid",
myActivity.Indi vidualID)
If myActivity.Acti vityDate = DateTime.MaxVal ue Then
cmdActivity.Par ameters.Add("@A ctivityDate", DBNull.Value)
Else
cmdActivity.Par ameters.Add("@A ctivityDate", myActivity.Acti vityDate)
End If
cmdActivity.Par ameters.Add("@t a", myActivity.TA)
If myActivity.Type TA = 0 Then
cmdActivity.Par ameters.Add("@t ypeta", DBNull.Value)
Else
cmdActivity.Par ameters.Add("@t ypeta", myActivity.Type TA)
End If
If myActivity.Eval Requested = DateTime.MaxVal ue Then
cmdActivity.Par ameters.Add("@e valrequested", DBNull.Value)
Else
cmdActivity.Par ameters.Add("@e valrequested", myActivity.Eval Requested)
End If
If myActivity.Eval Completed = DateTime.MaxVal ue Then
cmdActivity.Par ameters.Add("@e valcompleted", DBNull.Value)
Else
cmdActivity.Par ameters.Add("@e valcompleted", myActivity.Eval Completed)
End If

If myActivity.TACa tegory = 0 Then
cmdActivity.Par ameters.Add("@t acategory", DBNull.Value)
Else
cmdActivity.Par ameters.Add("@t acategory", myActivity.TACa tegory)
End If
conTA.Open()
AddActSuccess = True
Try
cmdActivity.Exe cuteNonQuery()
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),
@activitydescri ption 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], activitydescrip tion, activitydate,
technicalassist ance, typeta, evalrequested, evalcompleted, tacategory)
values
(@orgid, @individualid, @apcauser, @activitydescri ption, @activitydate,
@TA, @typeTA, @evalrequested, @evalcompleted, @tacategory)

If @@Error <> 0
BEGIN
ROLLBACK TRAN
RETURN

END
COMMIT TRANSACTION
GO

[Kevin Spencer]

Hi Child,

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

If myActivity.Indi vidualID = 0 Then
cmdActivity.Par ameters.Add("@i ndividualid", DBNull.Value)
Else
cmdActivity.Par ameters.Add("@i ndividualid", myActivity.Indi vidualID)
End If
....
myActivity.Desc ription)cmdActi vity.Parameters .Add("@individu alid",
myActivity.Indi vidualID)
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**********@a laska.com> wrote in message
news:11******** *****@corp.supe rnews.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(ByV al myActivity As Activity) As
Integer

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

conTA.Connectio nString =
ConfigurationSe ttings.AppSetti ngs("Connection String")
Dim cmdActivity As New SqlCommand
cmdActivity.Com mandType = CommandType.Sto redProcedure
cmdActivity.Com mandText = "spAddActiv ity"
cmdActivity.Con nection = conTA
cmdActivity.Par ameters.Add("@o rgid", myActivity.OrgI D)
If myActivity.Indi vidualID = 0 Then
cmdActivity.Par ameters.Add("@i ndividualid", DBNull.Value)
Else
cmdActivity.Par ameters.Add("@i ndividualid", myActivity.Indi vidualID)
End If
cmdActivity.Par ameters.Add("@a pcauser", myActivity.APCA User)
cmdActivity.Par ameters.Add("@a ctivitydescript ion",
myActivity.Desc ription)cmdActi vity.Parameters .Add("@individu alid",
myActivity.Indi vidualID)
If myActivity.Acti vityDate = DateTime.MaxVal ue Then
cmdActivity.Par ameters.Add("@A ctivityDate", DBNull.Value)
Else
cmdActivity.Par ameters.Add("@A ctivityDate", myActivity.Acti vityDate)
End If
cmdActivity.Par ameters.Add("@t a", myActivity.TA)
If myActivity.Type TA = 0 Then
cmdActivity.Par ameters.Add("@t ypeta", DBNull.Value)
Else
cmdActivity.Par ameters.Add("@t ypeta", myActivity.Type TA)
End If
If myActivity.Eval Requested = DateTime.MaxVal ue Then
cmdActivity.Par ameters.Add("@e valrequested", DBNull.Value)
Else
cmdActivity.Par ameters.Add("@e valrequested", myActivity.Eval Requested)
End If
If myActivity.Eval Completed = DateTime.MaxVal ue Then
cmdActivity.Par ameters.Add("@e valcompleted", DBNull.Value)
Else
cmdActivity.Par ameters.Add("@e valcompleted", myActivity.Eval Completed)
End If

If myActivity.TACa tegory = 0 Then
cmdActivity.Par ameters.Add("@t acategory", DBNull.Value)
Else
cmdActivity.Par ameters.Add("@t acategory", myActivity.TACa tegory)
End If
conTA.Open()
AddActSuccess = True
Try
cmdActivity.Exe cuteNonQuery()
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),
@activitydescri ption 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], activitydescrip tion, activitydate,
technicalassist ance, typeta, evalrequested, evalcompleted, tacategory)
values
(@orgid, @individualid, @apcauser, @activitydescri ption, @activitydate,
@TA, @typeTA, @evalrequested, @evalcompleted, @tacategory)

If @@Error <> 0
BEGIN
ROLLBACK TRAN
RETURN

END
COMMIT TRANSACTION
GO