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)
{
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();
}