1

I have the following issue. I have a sequence and trigger in the database and they work. This means that my insert function in C# doesn't insert the ID but the rest of the columns. The problem is that now I can't bind the ID to a parameter. I have another function that selects everything from the table that hits after insert as a sort of refresh and even though that function gets the correct ID it still gets lost later I believe because it is not binded on insert to the parameter in the class.

So here is my Insert Into:

 transaction = oleCon.BeginTransaction();
                    oleComd = new OleDbCommand();
                    oleComd.Connection = oleCon;
                    oleComd.Transaction = transaction;
                    oleComd.CommandText = "Insert into OBJEKTI" +
                    "(NAZIV_OBJEKTA, SIFRA_MJESTA, SIFRA_REGION)" + "Values(:naziv, :mjesto, :region)";


                    oleComd.Parameters.AddWithValue(":naziv",  objekti.NazivObjekta);
                    oleComd.Parameters.AddWithValue(":mjesto", objekti.Sifra_Mjesta);
                    oleComd.Parameters.AddWithValue(":region", objekti.Sifra_Region);



                    oleComd.ExecuteNonQuery();
                    transaction.Commit();

                    oleCon.Close();

And here is my Trigger:

create or replace
TRIGGER "OBJEKTI_ID_TRIG"
BEFORE INSERT ON "OBJEKTI"

FOR EACH ROW
DECLARE

BEGIN 

SELECT OBJEKAT_ID_SEQ.NEXTVAL
INTO :NEW.OBJEKAT_ID
FROM DUAL;

END;

Anyway I really hope you guys can help me.

I also tried the RETURNING statement just now but even though it compiles and goes through with no error, it still gives me my key as 0 (even though in the database it isn't, I checked)

oleComd.CommandText = "Insert into OBJEKTI" +
                    "(NAZIV_OBJEKTA, SIFRA_MJESTA, SIFRA_REGION)" + "Values(:naziv, :mjesto, :region) RETURNING (OBJEKAT_ID) into (:id)";


                    oleComd.Parameters.AddWithValue(":naziv",  objekti.NazivObjekta);
                    oleComd.Parameters.AddWithValue(":mjesto", objekti.Sifra_Mjesta);
                    oleComd.Parameters.AddWithValue(":region", objekti.Sifra_Region);
                    oleComd.Parameters.AddWithValue(":id", objekti.ObjekatId);

EDIT:

I tested this bit of code:

set serveroutput ON;
DECLARE
varid NUMBER;

BEGIN

Insert into OBJEKTI (NAZIV_OBJEKTA, SIFRA_MJESTA, SIFRA_REGION) Values('dino', 1, 1)
RETURNING OBJEKAT_ID INTO varid;

dbms_output.put_line (varid);

END;

And this works, varid shows what it should which tells me that using returning in my insert into should work as well but it is not because it still puts a 0 in there.

So the problem has to be the 

OleComd.Parameters.AddWithValue

function but I don't know what other function to use to get this to work.

EDIT2:

So after some help from the Oracle forums I also tried this:

oleComd.CommandText = "Insert into OBJEKTI" +
                    "(NAZIV_OBJEKTA, SIFRA_MJESTA, SIFRA_REGION)" + " Values(:naziv, :mjesto, :region ) RETURNING OBJEKAT_ID INTO :id";


                    oleComd.Parameters.AddWithValue(":naziv",  objekti.NazivObjekta);
                    oleComd.Parameters.AddWithValue(":mjesto", objekti.Sifra_Mjesta);
                    oleComd.Parameters.AddWithValue(":region", objekti.Sifra_Region);
                    //oleComd.Parameters.Add(":id", OleDbType.).Direction = ParameterDirection.Output;
                    OleDbParameter id = new OleDbParameter();
                       id.ParameterName = "id";
            id.OleDbType = OleDbType.Integer;
            id.Direction = ParameterDirection.Output;
            oleComd.Parameters.Add(id);
            objekti.ObjekatId = Convert.ToInt32(id.Value);

But once again I got nowhere. It is reporting the value of parameter id as null. Which I don't get.

Hoping someone out there can help

EDIT3

My latest attempt, this fails with an error on the reader: "no data exists on the row/column"

 transaction = oleCon.BeginTransaction();
                    oleComd = new OleDbCommand();

                    oleComd.Connection = oleCon;

                    oleComd.Transaction = transaction;

                    oleComd.CommandText = "Insert into OBJEKTI" +
                    "(NAZIV_OBJEKTA, SIFRA_MJESTA, SIFRA_REGION)" + " Values(:naziv, :mjesto, :region )";


                    oleComd.Parameters.AddWithValue(":naziv",  objekti.NazivObjekta);
                    oleComd.Parameters.AddWithValue(":mjesto", objekti.Sifra_Mjesta);
                    oleComd.Parameters.AddWithValue(":region", objekti.Sifra_Region);

                    oleComd.ExecuteNonQuery();
                    transaction.Commit();
                    //oleCon2.Open();
                    transaction2 = oleCon.BeginTransaction();
                    oleComd2 = new OleDbCommand();
                    oleComd2.Connection = oleCon;
                    oleComd2.Transaction = transaction2;

                    oleComd2.CommandText = "select Objekat_ID from OBJEKTI where NAZIV_OBJEKTA=:naziv";
                    oleComd2.Parameters.AddWithValue(":naziv", objekti.NazivObjekta);

                    OleDbDataReader Reader = oleComd2.ExecuteReader();
                    objekti.ObjekatId = Convert.ToInt32(Reader["OBJEKAT_ID"]);
                    Reader.Close();
                    oleComd2.ExecuteNonQuery();
                    transaction2.Commit();
Dino Kantardzic
  • 97
  • 1
  • 4
  • 13

1 Answers1

0

The solution 

OleDbParameter id = new OleDbParameter();
                    id.ParameterName = "id";
                    id.OleDbType = OleDbType.Integer;
                    id.Direction = ParameterDirection.ReturnValue;
                    oleComd.Parameters.Add(id);
                    oleComd.ExecuteNonQuery();
                    transaction.Commit();
                    objekti.ObjekatId=Convert.ToInt32(oleComd.Parameters["id"].Value);

                    oleCon.Close();

Thank you everyone

Dino Kantardzic
  • 97
  • 1
  • 4
  • 13