I could not seem to find the error in my code. I counted all the variables over and over.
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
DECLARE @iReferrerID_ numeric(18, 0);
SET NOCOUNT ON;
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
);
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
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 tblReferrerLocationJunction
WHERE iReferrerID = @iReferrerID AND iLocationID NOT IN(
SELECT iLocationID FROM #tmpReferrerLocation);
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:
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
);
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[
^]