Get PK back on an insert into Oracle

[Guest]

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]

In SQLServer, you use ExecuteNonQuery, not ExecuteScalar.

int RecordsAffected = cmd.ExecuteNonQuery();

Robin S.

 

[Paul Clement]

¤
¤ 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)

 

[Guest]

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