I am trying to get returned the autogenerated primary key value from Oracle
when I insert a row. I am using the Microsoft OracleConnection class. My code
is:

// cmd is an OracleCommand object.
    cmd.CommandText += "; returning " + column_name + " into :r1";
    OracleParameter pkParam = new OracleParameter();
    pkParam.Direction = ParameterDirection.ReturnValue;
    pkParam.DbType = DbType.Decimal;
    cmd.Parameters.Add(pkParam);
    object rtn = cmd.ExecuteScalar();

But ExecuteScalar() throws an exception. Any ideas?

In SQLServer, you use ExecuteNonQuery, not ExecuteScalar.

int RecordsAffected = cmd.ExecuteNonQuery();

Robin S.
---------------------------------------
Show quoteHide quote

On Tue, 13 Mar 2007 19:27:26 -0700, David Thielen <thielen@nospam.nospam> wrote:

¤
¤ I am trying to get returned the autogenerated primary key value from Oracle
¤ when I insert a row. I am using the Microsoft OracleConnection class. My code
¤ is:
¤
¤ // cmd is an OracleCommand object.
¤     cmd.CommandText += "; returning " + column_name + " into :r1";
¤     OracleParameter pkParam = new OracleParameter();
¤     pkParam.Direction = ParameterDirection.ReturnValue;
¤     pkParam.DbType = DbType.Decimal;
¤     cmd.Parameters.Add(pkParam);
¤     object rtn = cmd.ExecuteScalar();
¤
¤ But ExecuteScalar() throws an exception. Any ideas?

I don't understand your CommandText value. Shouldn't you be querying a sequence? I don't believe
that ExecuteScalar or ExecuteOracleScalar is useful in this instance.

Look at the Sequences section in the following MS article for an example.

http://msdn2.microsoft.com/en-us/library/ms971506.aspx


Paul
~~~~
Microsoft MVP (Visual Basic)

Found it - two problems:

1) No ';' at the beginning of what is added.
2) Need to add the line pkParam.ParameterName = ":r1";

Show quoteHide quote

Password protect .mdb file
Urgent Memory Leak Problem Using Type Dataset. Please help! :-(
Deployment + Vista
Eliminating CRUD procedures
Import text file with DATE column into MS Access Database with VB.NET or C#
How can I use connected mode instead of disconnected?
A search module in c#
Is it possible to fill a DataSet.DataTable with values from more than one database table?
I need an approach to storing/editing data amongs forms
DataGridView and Combobox columns