Insert records to two tables using oracle

Question

I'm going to Insert values to two table in Oracle.When i insert the record to first table and i need to get the Id of that table and it needs to enter the Second table.

Using my query It insert the values to first table.

  public bool AddNewNews(NEWS nws)
    {
        bool status = false;

        try
        {

                DatabaseProviderFactory factory = new DatabaseProviderFactory();
                Database db = factory.Create("CharikaConString");

                con.Open();

                string query = @"INSERT INTO NEWS_TBL(NewsID,NAME) 
                                 VALUES(newsid.nextval,:NAME)";


                cmd = db.GetSqlStringCommand(query);

                db.AddInParameter(cmd, "NAME", DbType.String, nws.NAME);

                db.ExecuteNonQuery(cmd);
// In the below commented lines are,I tried to insert second table but not success.
                  // String query2 = "Select newsid.currval";

                 //string query2 = @"INSERT INTO DTL_TBL(DtlId,desc) VALUES (newsid.currval,nws.Desc)";
                // cmd = db.GetSqlStringCommand(query);

                 //db.AddInParameter(cmd, "desc", DbType.String, nws.Desc);

                // db.ExecuteNonQuery(cmd);

        }
        catch (Exception ex)
        {

            throw;
        }
        finally
        {
            con.Close();
        }
        return status;
    }

Possible duplicate of [How to get last inserted id?](http://stackoverflow.com/questions/5228780/how-to-get-last-inserted-id) — Juan Carlos Oropeza, Mar 03 '16 at 17:05
have you tried a simple google search, check this previous posting - http://stackoverflow.com/questions/1336911/how-to-get-the-generated-id-from-an-inserted-row-using-executescalar — MethodMan, Mar 03 '16 at 17:07

score 2 · Accepted Answer · answered Mar 03 '16 at 17:07

2

There are ways however you are using an oracle sequence, you could grab that value in a preliminary statement

select newsid.nextval from dual

and then use that as a regular parameter in your subsequent inserts?

answered Mar 03 '16 at 17:07

Dominic Cotton

789
10
34

Not sure if there's a better way, but this is how I always do it! – Ageonix Mar 03 '16 at 17:09
Then how to insert it to a second table ? – TechGuy Mar 03 '16 at 17:10
You would say: double newId = db.executescalar(); on the select statement above then substitute the newId wherever you need it – Dominic Cotton Mar 03 '16 at 20:52

Insert records to two tables using oracle

1 Answers1