Click here to Skip to main content
13,739,356 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.
‭011111100010‬ 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 | Cookies | Terms of Service
Web05-2016 | 2.8.180920.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