Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL C#3.5
i am trying to run a sql server 2008 stored procedure but recieving error
 
Procedure or function PopAllotedEmail has too many arguments specified.
 
c# code:
public static void PopAllotedEmail(SqlConnection con, SqlTransaction trans, int jobId,
            out int subscriberId, out string email, out int validationCode)
        {
            using (SqlCommand cmd = new SqlCommand("[dbo].[PopAllotedEmail]", con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
 
                cmd.Parameters.Add("@jobId", SqlDbType.Int).Value = jobId;
                cmd.Parameters.Add("@subscriberId", SqlDbType.Int).Direction = ParameterDirection.Output;
                cmd.Parameters.Add("@email", SqlDbType.VarChar, 50).Direction = ParameterDirection.Output;
                cmd.Parameters.Add("@validationCode", SqlDbType.Int).Direction = ParameterDirection.Output;
                cmd.Parameters.Add("ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
 
                cmd.Parameters["@subscriberId"].IsNullable = true;
                cmd.Parameters["@email"].IsNullable = true;
                cmd.Parameters["@validationCode"].IsNullable = true;
 
                if (trans != null)
                    cmd.Transaction = trans;
 
                cmd.ExecuteNonQuery();
 
                if (cmd.Parameters["@subscriberId"].Value == DBNull.Value)
                    subscriberId = -1;
                else
                    subscriberId = Convert.ToInt32(cmd.Parameters["@subscriberId"].Value);
                if (cmd.Parameters["@email"].Value == DBNull.Value)
                    email = null;
                else
                    email = Convert.ToString(cmd.Parameters["@email"].Value);
 
                if (cmd.Parameters["@validationCode"].Value == DBNull.Value)
                    validationCode = -1;
                else
                    validationCode = Convert.ToInt32(cmd.Parameters["@validationCode"].Value);
 
                int retVal = Convert.ToInt32(cmd.Parameters["ReturnValue"].Value);
 
                if (retVal != 0)
                    throw new Exception("Could not pop email.");
            }
        }
 

 

 
sql procedure:
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects
	WHERE object_id =
	OBJECT_ID(N'[dbo].[PopAllotedEmail]')
	AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[PopAllotedEmail]
	(
	@jobId int,
	@email varchar(50) = NULL OUTPUT
	
	)
AS
BEGIN
	SET NOCOUNT ON
 
	DECLARE @subscriberId int
 
	SELECT TOP 1 @subscriberId =
		s.[SubscriberID], @email = s.[Email]
		FROM [Subscribers] s INNER JOIN
		[JobEmails] je ON s.[SubscriberID] =
		je.[SubscriberID] WHERE je.[JobID] =
		@jobId ORDER BY s.[SubscriberID] ASC
 
	IF @@ROWCOUNT = 0
		RETURN 0
 
	BEGIN TRAN -- Transaction starts
	DELETE [JobEmails] WHERE [JobID] = @jobId AND
		[SubscriberID] = @subscriberId
	
	IF @@ROWCOUNT = 0
	BEGIN
		ROLLBACK TRAN
		RETURN -1
	END
	
	UPDATE [Jobs] SET [EmailsSent] =
		[EmailsSent] + 1
		WHERE [JobID] = @jobId
	
	IF @@ERROR <> 0
	BEGIN
		ROLLBACK TRAN
		RETURN -2
	END
	
	COMMIT TRAN
	RETURN 0
END' 
END
GO
Posted 5-Apr-13 8:14am
Edited 5-Apr-13 8:18am
(no name)23.4K
v2
Comments
Sergey Alexandrovich Kryukov at 5-Apr-13 13:23pm
   
Please don't re-post. I removed your previous post.
—SA
AspDotNetDev at 5-Apr-13 14:01pm
   
The stored procedure accepts 2 parameters, but you are adding 5 parameters. Seems pretty obvious that won't work. Or, did you have another question?
ryanb31 at 5-Apr-13 14:21pm
   
As said previously, the error is very clear. Where are you stuck?
Ravi Bhavnani at 5-Apr-13 17:59pm
   
I'm no SQL expert (as a matter of fact I'm not an expert at anything), but aren't you specifying a slew of extra parameters when "jobId" and "email" are the only ones supported?
 
/ravi

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Maciej Los 295
1 OriginalGriff 273
2 Aajmot Sk 234
3 Marcin Kozub 205
4 Richard MacCutchan 200
0 OriginalGriff 7,903
1 Sergey Alexandrovich Kryukov 7,127
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,820


Advertise | Privacy | Mobile
Web02 | 2.8.1411023.1 | Last Updated 5 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100