Click here to Skip to main content
15,886,110 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I could not seem to find the error in my code. I counted all the variables over and over.

SQL:
SQL
ALTER PROCEDURE [dbo].[sp_AdminSite_ReferrerDAL_SaveUpdateReferrer] 
	@iReferrerID INT,
	@tFirstName varchar(50),
	@tLastName varchar(50),
	@tAddress varchar(50),
	@tCity varchar(50),
	@tState varchar(50),
	@tZipCode varchar(50),
	@tPhoneNo varchar(50),
	@tEmail varchar(50),
	@iRMID INT,
	@iCompID INT,
	@bReferreStatus Bit,
	@xmlADD XML,
	@retVal INT OUTPUT,
	@xmlDEL XML,
	@bIsEdit Bit
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	DECLARE @iReferrerID_ numeric(18, 0);

	SET NOCOUNT ON;

    -- Insert Referrer for procedure here
	-- Insert to Referrer
	IF(@bIsEdit = 0)
		BEGIN
			INSERT INTO tblReferrer 
			(
				tFirstName, 
				tLastName,
				tAddress,
				tCity,
				tState,
				tZipCode,
				tPhoneNumber,
				tEmail,
				dDateCreated,
				iRMID,
				iCompID,
				bReferrerStatus
			)
			VALUES(
				@tFirstName,
				@tLastName,
				@tAddress,
				@tCity,
				@tState,
				@tZipCode,
				@tPhoneNo,
				@tEmail,
				GETDATE(),
				@iRMID,
				@iCompID,
				@bReferreStatus
			);

			-- Insert to tblReferrerLocationJunction
			SET  @iReferrerID_ = (SELECT SCOPE_IDENTITY() );
			SET @retVal = @iReferrerID_;
			INSERT INTO tblReferrerLocationJunction
			(
				iReferrerID,
				iLocationID
			)
			SELECT
				@iReferrerID_,
				Locations.value('@Id','INT') AS iLocationID
			FROM
				@xmlADD.nodes('/locations/location') AS TEMPTABLE(Locations);
		 END
	ELSE --for saving edited
		BEGIN
			DECLARE @tmpRefID INT = 0;
			SELECT @tmpRefID = iReferrerID  FROM tblReferrer WHERE tFirstName=@tFirstName AND
					tLastName =@tLastName AND
					tAddress =@taddress AND
					tCity=@tCity AND
					tState=@tState AND
					tZipCode=@tZipCode AND
					tPhoneNumber=@tPhoneNo AND
					tEmail=@tEmail AND
					iRMID= @iRMID AND
					iCompID = @iCompID AND
					bReferrerStatus =@bReferreStatus AND
					iReferrerID = @iReferrerID;
			
			IF @tmpRefID = 0 
				BEGIN
				UPDATE tblReferrer 
				SET
					tFirstName=@tFirstName, 
					tLastName =@tLastName,
					tAddress =@taddress,
					tCity=@tCity,
					tState=@tState,
					tZipCode=@tZipCode,
					tPhoneNumber=@tPhoneNo,
					tEmail=@tEmail,
					iRMID= @iRMID,
					iCompID = @iCompID,
					bReferrerStatus =@bReferreStatus 
				WHERE
					iReferrerID = @iReferrerID;
				END

			IF OBJECT_ID('tempdb..#tmpReferrerLocation') IS NOT NULL
				DROP TABLE #tmpReferrerLocation

			CREATE TABLE #tmpReferrerLocation ( iLocationID INT);

			INSERT INTO #tmpReferrerLocation
			SELECT
				Locations.value('@Id','INT') AS iLocationID				
			FROM
				@xmlADD.nodes('/locations/location') AS TEMPTABLE(Locations)

			-- delete from junction
			--DELETE
			DELETE FROM tblReferrerLocationJunction
			WHERE iReferrerID = @iReferrerID AND iLocationID NOT IN(
				SELECT iLocationID FROM #tmpReferrerLocation);

			-- insert to junction
			INSERT INTO tblReferrerLocationJunction
			( iReferrerID, iLocationID)
			SELECT 
				@iReferrerID,
				T.iLocationID
			FROM
				#tmpReferrerLocation T
			WHERE
				T.iLocationID NOT IN
			(
				SELECT iLocationID FROM tblReferrerLocationJunction
					WHERE iReferrerID = @iReferrerID
			)
			
			DROP TABLE #tmpReferrerLocation;
			SET @retVal = @iReferrerID;
		 END
END

C# Code Behind:
C#
public static int SaveReferrer(int _referrerId, string _lastName, string _firstName, string _address, string _city, string _state, string _zipCode, string _phone, string _email,
                    int _adminID, int _companyID, int _referrerStatus, XmlDocument _xmlDoc, XmlDocument _xmlDel, int _isEdit)
        {
            int iReferrerId = 0;
            try
            {
                SqlParameter[] arParams = new SqlParameter[16];
                arParams[0] = new SqlParameter("@iReferrerID", _referrerId);
                arParams[1] = new SqlParameter("@tFirstName",  _firstName);
                arParams[2] = new SqlParameter("@tLastName", _lastName);
                arParams[3] = new SqlParameter("@tAddress", _address);
                arParams[4] = new SqlParameter("@tCity", _city);
                arParams[5] = new SqlParameter("@tState", _state);
                arParams[6] = new SqlParameter("@tZipCode", _zipCode);
                arParams[7] = new SqlParameter("@tPhoneNo", _phone);
                arParams[8] = new SqlParameter("@tEmail", _email);
                arParams[9] = new SqlParameter("@iRMID", _adminID);
                arParams[10] = new SqlParameter("@iCompID", _companyID);
                arParams[11] = new SqlParameter("@bReferreStatus", _referrerStatus);
                arParams[12] = new SqlParameter("@xmlADD", SqlDbType.Xml)
                {
                    Value = new SqlXml(new XmlTextReader(_xmlDoc.InnerXml, XmlNodeType.Document, null))
                };
                arParams[13] = new SqlParameter("@retVal", SqlDbType.Int, 11);
                arParams[13].Direction = ParameterDirection.Output;
                arParams[14] = new SqlParameter("@xmlDEL", SqlDbType.Xml)
                {
                    Value = new SqlXml(new XmlTextReader(_xmlDel.InnerXml, XmlNodeType.Document, null))
                };
                arParams[15] = new SqlParameter("@bIsEdit", _isEdit);
                SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, "[sp_AdminSite_ReferrerDAL_SaveUpdateReferrer]",
                    arParams
                    ); //The error always happens here.

                iReferrerId = Convert.ToInt32(arParams[13].Value.ToString());
            }
            catch
            {
                throw;
            }

            return iReferrerId;
        }

I tried searching for solutions but all I found were queries not stored procedures.

What I have tried:

sql - Procedure or function !!! has too many arguments specified - Stack Overflow[^]
c# - "Procedure or function has too many arguments specified" But It Doesn't - Stack Overflow[^]
[RESOLVED]Procedure or function has too many arguments specified.-VBForums[^]
Posted
Comments
Garth J Lancaster 1-Mar-17 1:59am    
well, so far I've likely done the same as you, counted them, 'tick', compared parameter name character by character, 'tick'....

this may not make a difference, but, nowhere in your stored procedure do I see you actually do a

return @retVal

you 'SET' it twice .... other than that, I'm still thinking
Karthik_Mahalingam 1-Mar-17 2:46am    
checked your code, seems fine..
might be some error in SqlHelper.ExecuteNonQuery() method..
post the method code..
Garth J Lancaster 1-Mar-17 2:58am    
there's an example here that doesnt have an major differences to your code that I can see https://www.codeproject.com/articles/748619/ado-net-how-to-call-a-stored-procedure-with-output
Karthik_Mahalingam 1-Mar-17 3:18am    
couldn't reproduce the error..
OP is missing something.
Garth J Lancaster 1-Mar-17 4:07am    
'ugh' - sometimes we're just flying blind !

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900