Click here to Skip to main content
13,630,856 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
Hi Friends,
My Sql Procedure update all rows in table instated of one row when i change in parameter name it return error
Parameter '_DetailId' not found in the collection.


this is my Store procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `Sp_DetailsUpdate`(
 in DetailId int(11),
 in FreightContractId int(11), 
 in LSource varchar(100), 
 in Destination varchar(100), 
 in Qty varchar(50), 
 in Rate decimal(10),
 in FreightBasis varchar(50), 
 in VehicleType varchar(50), 
 in Dist_In_Kms varchar(50), 
 in Lead_Hours varchar(30), 
 in Remarks varchar(100), 
 in S_ID int(11), 
 in D_ID int(11)

)
IF EXISTS (select 1=1 From freightcontractdetails where DetailId=DetailId and FreightContractId=FreightContractId) then
  begin
    Update freightcontractdetails set LSource=LSource, Destination=Destination, Qty=Qty, Rate=Rate, FreightBasis=FreightBasis, VehicleType=VehicleType, Dist_In_Kms=Dist_In_Kms, Lead_Hours=Lead_Hours, Remarks=Remarks, S_ID=S_ID, D_ID=D_ID where  DetailId=DetailId and  FreightContractId=FreightContractId;
  end;
else
  begin
  Insert into freightcontractdetails (FreightContractId, LSource, Destination, Qty, Rate, FreightBasis, VehicleType, Dist_In_Kms, Lead_Hours, Remarks, S_ID, D_ID) Values ( _FreightContractId, LSource, Destination, Qty, Rate, FreightBasis, VehicleType, Dist_In_Kms, Lead_Hours, Remarks, S_ID, D_ID );
  end;
End if


i am using mysql 5.7 and asp.net 4.0

What I have tried:

following changes give error Parameter '_DetailId' not found in the collection.

CREATE DEFINER=`root`@`localhost` PROCEDURE `Sp_DetailsUpdate`(
 in _DetailId int(11),
 in _FreightContractId int(11), 
 in LSource varchar(100), 
 in Destination varchar(100), 
 in Qty varchar(50), 
 in Rate decimal(10),
 in FreightBasis varchar(50), 
 in VehicleType varchar(50), 
 in Dist_In_Kms varchar(50), 
 in Lead_Hours varchar(30), 
 in Remarks varchar(100), 
 in S_ID int(11), 
 in D_ID int(11)

)
IF EXISTS (select 1=1 From freightcontractdetails where DetailId=_DetailId and FreightContractId=_FreightContractId) then
  begin
    Update freightcontractdetails set LSource=LSource, Destination=Destination, Qty=Qty, Rate=Rate, FreightBasis=FreightBasis, VehicleType=VehicleType, Dist_In_Kms=Dist_In_Kms, Lead_Hours=Lead_Hours, Remarks=Remarks, S_ID=S_ID, D_ID=D_ID where  DetailId=_DetailId and  FreightContractId=_FreightContractId;
  end;
else
  begin
  Insert into freightcontractdetails (FreightContractId, LSource, Destination, Qty, Rate, FreightBasis, VehicleType, Dist_In_Kms, Lead_Hours, Remarks, S_ID, D_ID) Values ( _FreightContractId, LSource, Destination, Qty, Rate, FreightBasis, VehicleType, Dist_In_Kms, Lead_Hours, Remarks, S_ID, D_ID );
  end;
End if


public void Update_FCD(int _FreightContractId, string LSource, string Destination, string Qty, decimal Rate, string FreightBasis, string VehicleType, string Dist_In_Kms, string Lead_Hours, string Remarks, int S_ID, int D_ID, int _DetailId)
       {
           //String Sql = "Update freightcontractdetails set LSource=@LSource, Destination=@Destination, Qty=@Qty, Rate=@Rate, FreightBasis=@FreightBasis, VehicleType=@VehicleType, Dist_In_Kms=@Dist_In_Kms, Lead_Hours=@Lead_Hours, Remarks=@Remarks, S_ID=@S_ID, D_ID=@D_ID where  DetailId=@DetailId and  FreightContractId=@FreightContractId";

           con.Open();
           Cmd = new MySqlCommand("Sp_DetailsUpdate", con);
           Cmd.CommandType = CommandType.StoredProcedure;
           Cmd.Parameters.AddWithValue("@FreightContractId", _FreightContractId);
           Cmd.Parameters.AddWithValue("@LSource", LSource);
           Cmd.Parameters.AddWithValue("@Destination", Destination);
           Cmd.Parameters.AddWithValue("@Qty", Qty);
           Cmd.Parameters.AddWithValue("@Rate", Rate);
           Cmd.Parameters.AddWithValue("@FreightBasis", FreightBasis);
           Cmd.Parameters.AddWithValue("@VehicleType", VehicleType);
           Cmd.Parameters.AddWithValue("@Dist_In_Kms", Dist_In_Kms);
           Cmd.Parameters.AddWithValue("@Lead_Hours", Lead_Hours);
           Cmd.Parameters.AddWithValue("@Remarks", Remarks);
           Cmd.Parameters.AddWithValue("@S_ID", S_ID);
           Cmd.Parameters.AddWithValue("@D_ID", D_ID);
           Cmd.Parameters.AddWithValue("@DetailId", _DetailId);

           Cmd.ExecuteNonQuery();
           con.Close();

       }
Posted 3-Aug-17 22:16pm
Updated 3-Aug-17 22:18pm
v2
Comments
Richard Deeming 4-Aug-17 10:08am
   
The parameter name in your C# code needs to match the parameter name in your stored procedure. If you change the stored procedure parameter to _DetailId, then you also need to change the name in your C# code.
Member 7969814 8-Aug-17 3:06am
   
yes you are right that is the problem thanks a lot
‭011111100010‬ 4-Aug-17 16:37pm
   
It updates all records because you told it to. What exactly is your question?

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web01 | 2.8.180712.1 | Last Updated 4 Aug 2017
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

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