Click here to Skip to main content
13,259,427 members (53,799 online)
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 23:16pm
Updated 3-Aug-17 23: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.
RyanDev 4-Aug-17 16:37pm
   
It updates all records because you told it to. What exactly is your question?
Member 7969814 8-Aug-17 3:06am
   
yes you are right that is the problem thanks a lot

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 |
Web02 | 2.8.171114.1 | Last Updated 4 Aug 2017
Copyright © CodeProject, 1999-2017
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