Get PK back on an insert into Oracle

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?

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm

RobinS
Wed Mar 14 01:48:47 CDT 2007

In SQLServer, you use ExecuteNonQuery, not ExecuteScalar.

int RecordsAffected = cmd.ExecuteNonQuery();

Robin S.
---------------------------------------
"David Thielen" <thielen@nospam.nospam> wrote in message
news:B99509EF-A01C-4DF3-998E-CD6A94C02844@microsoft.com...
>
> 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?
>
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
>
> Cubicle Wars - http://www.windwardreports.com/film.htm
>
>

Paul
Wed Mar 14 08:03:55 CDT 2007

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)

thielen
Wed Mar 14 11:20:07 CDT 2007

Found it - two problems:

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

--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com

Cubicle Wars - http://www.windwardreports.com/film.htm




"David Thielen" 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?
>
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
>
> Cubicle Wars - http://www.windwardreports.com/film.htm
>
>