Firebird stored procedure, fired from C#, inserts two or more rows, want only one

Question

My problem is that when i run C# method that fires firebird stored procedure, it adds more that one row, mostly it's 2, but 3 or more happend. I want only one row to be added per use of this code. When i run SP manually from firebird everything is ok. It seems that C# code causes problem somwehere but i have no idea where.

During debugging rows are inserted ofcourse after I pass ft.Commit();

I hope i made it clear, if not the please ask, i'll try to explain better. Sorry if code is not right formatted, i'm writing first time here.

Here is C# method:

public int AddKursowka(string nrLinii, int typLinii, string kodLinii, string nazwaKwi, string txtKwi, DateTime dateStart, DateTime dateStop,
        int idLinii,int idKursowki, int idTxtKursowka)
    {
        FbTransaction ft = null;
        int _result = -1;
        try
        {
            Connect();
            {
                ft = mFbConn.BeginTransaction();

                using (FbCommand com = new FbCommand(
                    "EXECUTE PROCEDURE ADD_KURSOWKA(@NRLINII, @TYPLINII, @KODLINII, @NAZWAKWI, @TXTKWI, @DATASTART, @DATASTOP, " +
                    "@IDLINII, @IDKURSOWKI, @IDTXTKURSOWKA, @PRJ);", mFbConn, ft))
                {
                    com.CommandType = CommandType.StoredProcedure;
                    com.Parameters.Add("@NRLINII", FbDbType.VarChar, 100).Value = nrLinii;
                    com.Parameters.Add("@TYPLINII", FbDbType.BigInt).Value = typLinii;
                    com.Parameters.Add("@KODLINII", FbDbType.VarChar, 50).Value = kodLinii;
                    com.Parameters.Add("@NAZWAKWI", FbDbType.VarChar, 10).Value = nazwaKwi;
                    com.Parameters.Add("@TXTKWI", FbDbType.VarChar, 12).Value = txtKwi;
                    com.Parameters.Add("@DATASTART", FbDbType.Date).Value = dateStart;//.Date;
                    com.Parameters.Add("@DATASTOP", FbDbType.Date).Value = dateStop;//.Date;
                    com.Parameters.Add("@IDLINII", FbDbType.BigInt).Value = idLinii;
                    com.Parameters.Add("@IDKURSOWKI", FbDbType.BigInt).Value = idKursowki;
                    com.Parameters.Add("@IDTXTKURSOWKA", FbDbType.BigInt).Value = idTxtKursowka;
                    com.Parameters.Add("@PRJ", FbDbType.VarChar, 100).Value = Variables.Instance.LastPRJ;
                    // 1-3 - update / 4-6  - insert
                    com.ExecuteScalar();// _result = (int)
                }
                ft.Commit();
            }
            Disconect();
        }
        catch (Exception ex) { Functionalities.WriteLog(ex); ft.Rollback(); }
        finally
        {
            if (ft != null) ft.Dispose();
        }
        return _result;
    }

And here is procedure:

    ALTER PROCEDURE ADD_KURSOWKA (
NRLINII Varchar(100),
TYPLINII Bigint,
KODLINII Varchar(50),
NAZWAKWI Varchar(10),
TXTKWI Varchar(12),
DATASTART Date,
DATASTOP Date,
IDLINII Bigint,
IDKURSOWKI Bigint,
IDTXTKURSOWKA Bigint,
PRJ Varchar(100) )
   RETURNS (
RESULT Integer )
   AS
   DECLARE VARIABLE idKursowka BIGINT; 
   DECLARE VARIABLE aResult INTEGER;
   DECLARE VARIABLE bResult INTEGER;
   DECLARE VARIABLE txtRes BIGINT;
   DECLARE VARIABLE lineRes BIGINT;
   BEGIN

   if(:IDKURSOWKI = -1)
   then
   begin
      INSERT INTO KURSOWKA (TYPDNIA, KODLINII, NRBRYGADY, LICZBAKURSOW, NAZWAKWI,
      NRZAJEZDNI, REZERWA, WYKLUCZENIADNIATYGODNIA, WYKLUCZENIAOKRESOWE, IDPRZEWOZNIKA,
      PRJ_NAME)
      VALUES(1, :KODLINII, 1, 0, :NAZWAKWI, 0, 0, 0, 0, 0, :PRJ);
      idKursowka = (select max(k.IDKURSOWKA) from KURSOWKA k where k.PRJ_NAME = :PRJ);
      bResult = 4;

      execute procedure ADD_TXT_KURSOWKA 
      :IDTXTKURSOWKA, :TXTKWI, :idKursowka, :DATASTART, :DATASTOP, :PRJ 
      returning_values :txtRes;
      RESULT = RESULT + txtRes;

      execute procedure ADD_LINE_V2 
      :IDLINII, :NRLINII, :TYPLINII, :PRJ, :KODLINII 
      returning_values :lineRes;
      RESULT = RESULT + lineRes;

   end
   else
   begin
      UPDATE KURSOWKA k SET k.KODLINII = :KODLINII, k.NAZWAKWI = :NAZWAKWI
      WHERE k.IDKURSOWKA = :IDKURSOWKI and k.PRJ_NAME = :PRJ;
      idKursowka = :IDKURSOWKI;
      aResult = 1;

      execute procedure ADD_TXT_KURSOWKA 
      :IDTXTKURSOWKA, :TXTKWI, :idKursowka, :DATASTART, :DATASTOP, :PRJ 
      returning_values :txtRes;
      RESULT = RESULT + txtRes;

      execute procedure ADD_LINE_V2 
      :IDLINII, :NRLINII, :TYPLINII, :PRJ, :KODLINII 
      returning_values :lineRes;
      RESULT = RESULT + lineRes;

   end
  END



   ALTER PROCEDURE ADD_LINE_V2 (
IDLINII Bigint,
NRLINII Varchar(100),
TYPLINII Bigint,
PRJ_NAME Varchar(100),
KOD_LINII Varchar(50) )
   RETURNS (
RESULT Integer )
  AS
  BEGIN
  if(:IDLINII = -1)
  then
  begin
     INSERT INTO LINIE(NRLINII, TYPLINII, PRJ_NAME, KOD_LINII)
     VALUES(:NRLINII, :TYPLINII, :PRJ_NAME, :KOD_LINII);
     RESULT =  1;
  end
  else
  begin
     update LINIE l set l.KOD_LINII = :KOD_LINII, 
     l.NRLINII = :NRLINII, l.TYPLINII = :TYPLINII
     where l.PRJ_NAME = :PRJ_NAME and l.IDLINII = :IDLINII;
     RESULT = 2;
  end
END



  ALTER PROCEDURE ADD_TXT_KURSOWKA (
IDTXTKURSOWKA Bigint,
TXTKWI Varchar(12),
IDKURSOWKA Bigint,
DATASTART Date,
DATASTOP Date,
PRJ Varchar(100) )
  RETURNS (
RESULT Integer )
  AS
  BEGIN
  if(:IDTXTKURSOWKA = -1)
  then
  begin
     INSERT INTO TXT_KURSOWKA(TXTKWI, WSKKURSOWKI, DATAPSTART,
     DATAPSTOP, PRJ_NAME)
     VALUES(:TXTKWI, :idKursowka, :DATASTART, :DATASTOP, :PRJ);
     RESULT = 1;
  end
  else
  begin
     update TXT_KURSOWKA txt set txt.DATAPSTART = :DATASTART, 
     txt.DATAPSTOP = :DATASTOP,
     txt.TXTKWI = :TXTKWI, txt.WSKKURSOWKI = :idKursowka
     where txt.ID_TXT_KURSOWKA = :idKursowka;
     RESULT = 2;
  end
END

Answer 1

Ok. It turns out i have made mistakes in stored procedures and I found that by manually adding more rows and somehow i found where values didnt match to which i gave as parameters. C# code is fine i guess. As far as i'm checking now it runs fine.

This is proper code. Two sub procedures had mistakes:

SET TERM ^ ;
ALTER PROCEDURE ADD_TXT_KURSOWKA (
IDTXTKURSOWKA Bigint,
TXTKWI Varchar(12),
IDKURSOWKA Bigint,
DATASTART Date,
DATASTOP Date,
PRJ Varchar(100) )
RETURNS (
RESULT Integer )
AS
BEGIN
if(IDTXTKURSOWKA = -1)
then
begin
    INSERT INTO TXT_KURSOWKA(TXTKWI, WSKKURSOWKI, DATAPSTART,
    DATAPSTOP, PRJ_NAME)
    VALUES(:TXTKWI, :IDKURSOWKA, :DATASTART, :DATASTOP, :PRJ);
    RESULT = 1;
end
else
begin
    update TXT_KURSOWKA txt set txt.DATAPSTART = :DATASTART, 
    txt.DATAPSTOP = :DATASTOP,
    txt.TXTKWI = :TXTKWI, txt.WSKKURSOWKI = :IDKURSOWKA
    where txt.ID_TXT_KURSOWKA = :IDTXTKURSOWKA;
    RESULT = 2;
end



ALTER PROCEDURE ADD_LINE_V2 (
IDLINII Bigint,
NRLINII Varchar(100),
TYPLINII Bigint,
PRJ_NAME Varchar(100),
KOD_LINII Varchar(50) 
)
RETURNS 
(
RESULT Integer 
)
AS
BEGIN

if(IDLINII = -1)
then
begin
    INSERT INTO LINIE(NRLINII, TYPLINII, PRJ_NAME, KOD_LINII)
    VALUES(:NRLINII, :TYPLINII, :PRJ_NAME, :KOD_LINII);
    RESULT =  1;
end
else
begin
    update LINIE l set l.KOD_LINII = :KOD_LINII, 
    l.NRLINII = :NRLINII, l.TYPLINII = :TYPLINII
    where l.PRJ_NAME = :PRJ_NAME and l.IDLINII = :IDLINII;
    RESULT = 2;
end
END