Procedure expects parameter which was not supplied

Anil Sharma

6y10.2k.NET

I try to through this code

try
{
SqlCommand cmd = new SqlCommand("dbo.generate_loan_entry", connect);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@farmer_code", party_code.Text);
cmd.Parameters.AddWithValue("@ticket_no", ticket_no.Text);
cmd.Parameters.AddWithValue("@total_loan", adj_loan);
connect.Open();
cmd.ExecuteNonQuery();
connect.Close();
}
catch (Exception ex)
{
Response.Write("");
}
finally
{ }

My procedure is

ALTER PROCEDURE [dbo].[generate_loan_entry](@farmer_cd nvarchar(6),
@ticket_no int,
@total_loan int)
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--DECLARE THE VARIABLES FOR HOLDING DATA.
DECLARE @v_account_code INT,
@vloan_amount numeric(8,2),
@vadj_loan numeric(8,2),
@vloan_deduct numeric(8,2),
@vdeducted_loan numeric(8,2);
--DECLARE AND SET COUNTER.
DECLARE @Counter INT
SET @Counter = 1
--DECLARE THE CURSOR FOR A QUERY.
DECLARE C1 CURSOR READ_ONLY
FOR
SELECT account_code,sum(DEBIT_AMOUNT-CREDIT_AMOUNT)
FROM FA_BALANCE_MASTER
WHERE ACCOUNT_CODE IN (211068,211069) AND PARTY_CODE = @farmer_cd
AND YYYYMM >= (Select year(start_date) from fa_company_detail )
group by account_code;
--OPEN CURSOR.
OPEN C1;
--FETCH THE RECORD INTO THE VARIABLES.
FETCH NEXT FROM C1 INTO @v_account_code,@vloan_amount;
--LOOP UNTIL RECORDS ARE AVAILABLE.
WHILE @@FETCH_STATUS = 0
Begin
-- Insert statements for procedure here
SELECT @vloan_deduct = SUM(adjusted_amount)
FROM CA_LOAN_DEDUCTION
WHERE ACCOUNT_CODE = @v_account_code
AND PARTY_CODE = @farmer_cd AND isnull(voc_auth_flag,'N') = 'N';
if (@vloan_deduct > 0.0 )
begin
select @vloan_amount = @vloan_amount - @vloan_deduct;
end ;
IF (@vloan_amount < isnull(@total_loan,0) - isnull(@vadj_loan,0))
begin
select @vdeducted_loan = @vloan_amount ;
end;
ELSE
begin
select @vdeducted_loan = isnull(@total_loan,0) - isnull(@vadj_loan,0);
end;
FETCH NEXT FROM C1 INTO @v_account_code,@vloan_amount;
IF @vdeducted_loan > 0
begin
INSERT INTO CA_LOAN_DEDUCTION VALUES(
@ticket_no,@v_account_code,@farmer_cd,@vloan_amount,@vdeducted_loan,null,'N');
end;
set @vadj_loan = @vadj_loan + @vdeducted_loan;
end;
close C1;
DEALLOCATE C1;
END;

please suggest what is the problem. this procedure running through execute procedure i check it

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar6y ago
Hi anil,

Your store procedure parameter name and code behind parameter name is mismatch. Both must be same, you need to make change at one place either in procedure or codebehind.
ALTER PROCEDURE [dbo].[generate_loan_entry](@farmer_code nvarchar(6),
@ticket_no int,
@total_loan int)
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--DECLARE THE VARIABLES FOR HOLDING DATA.
DECLARE @v_account_code INT,
@vloan_amount numeric(8,2),
@vadj_loan numeric(8,2),
@vloan_deduct numeric(8,2),
@vdeducted_loan numeric(8,2);
--DECLARE AND SET COUNTER.
DECLARE @Counter INT
SET @Counter = 1
--DECLARE THE CURSOR FOR A QUERY.
DECLARE C1 CURSOR READ_ONLY
FOR
SELECT account_code,sum(DEBIT_AMOUNT-CREDIT_AMOUNT)
FROM FA_BALANCE_MASTER
WHERE ACCOUNT_CODE IN (211068,211069) AND PARTY_CODE = @farmer_cd
AND YYYYMM >= (Select year(start_date) from fa_company_detail )
group by account_code;
--OPEN CURSOR.
OPEN C1;
--FETCH THE RECORD INTO THE VARIABLES.
FETCH NEXT FROM C1 INTO @v_account_code,@vloan_amount;
--LOOP UNTIL RECORDS ARE AVAILABLE.
WHILE @@FETCH_STATUS = 0
Begin
-- Insert statements for procedure here
SELECT @vloan_deduct = SUM(adjusted_amount)
FROM CA_LOAN_DEDUCTION
WHERE ACCOUNT_CODE = @v_account_code
AND PARTY_CODE = @farmer_cd AND isnull(voc_auth_flag,'N') = 'N';
if (@vloan_deduct > 0.0 )
begin
select @vloan_amount = @vloan_amount - @vloan_deduct;
end ;
IF (@vloan_amount < isnull(@total_loan,0) - isnull(@vadj_loan,0))
begin
select @vdeducted_loan = @vloan_amount ;
end;
ELSE
begin
select @vdeducted_loan = isnull(@total_loan,0) - isnull(@vadj_loan,0);
end;
FETCH NEXT FROM C1 INTO @v_account_code,@vloan_amount;
IF @vdeducted_loan > 0
begin
INSERT INTO CA_LOAN_DEDUCTION VALUES(
@ticket_no,@v_account_code,@farmer_cd,@vloan_amount,@vdeducted_loan,null,'N');
end;
set @vadj_loan = @vadj_loan + @vdeducted_loan;
end;
close C1;
DEALLOCATE C1;
END;
0
Ravi Patel6y ago
Hi,

Parameter name must be same in Stored procedure(@farmer_cd) and C# (@farmer_code) side, due to mismatch in parameter name its throwing error

change this line from

cmd.Parameters.AddWithValue("@farmer_code", party_code.Text);

to

cmd.Parameters.AddWithValue("@farmer_cd", party_code.Text);

it will owrk
0
Guest User6y ago
Your stored proc defines farmer_cd while your c# code adds farmer_code
0