Can I get values from sequence objects?
I have a table with a serial data type field and I need to know which value was assigned to it just after inserting a new record.
I know there are PostgreSQL functions that work with sequence objects (currval, lastval, etc) but I have not been able to access them using dotConnect.
I am using dotConnect for PostgreSQL v4.0
Please advice.
Respectfully,
Jorge Maldonado
GETTING VALUES FROM SEQUENCE OBJECTS
Assuming that your table is like:
Please create your commands using the RETURNING clause:
This can be easily done with our PgSqlDataTable Editor (the Refreshing option in Command Generator).
As an alternative, you can execute the "select currval('withsequence_id_seq');" statement for the current session.
Code: Select all
CREATE TABLE withsequence
(
id serial NOT NULL,
"value" text
);
Code: Select all
pgSqlDataTable1.InsertCommand.CommandText="INSERT INTO withsequence (value) VALUES (:value) RETURNING id";
As an alternative, you can execute the "select currval('withsequence_id_seq');" statement for the current session.
Please help a little bit more.
If I have the following code where pss_clave is a serial type field:
String strQuery = "";
strQuery += "INSERT INTO cat_paises ";
strQuery += "(pss_clave, pss_nombre) ";
strQuery += "VALUES (DEFAULT, ";
strQuery += "'" + this.Nombre + "') ";
strQuery += "RETURNING pss_clave";
String strConn = System.Configuration.ConfigurationManager.ConnectionStrings["PgSqlConnection"].ToString();
PgSqlConnection pgConn = new PgSqlConnection(strConn);
PgSqlCommand pgCmd = new PgSqlCommand(strQuery, pgConn);
pgConn.Open();
pgCmd.ExecuteNonQuery();
How do I have access to the RETURNING value (pss_clave) so I can use it in fields of other tables?
Regards.
If I have the following code where pss_clave is a serial type field:
String strQuery = "";
strQuery += "INSERT INTO cat_paises ";
strQuery += "(pss_clave, pss_nombre) ";
strQuery += "VALUES (DEFAULT, ";
strQuery += "'" + this.Nombre + "') ";
strQuery += "RETURNING pss_clave";
String strConn = System.Configuration.ConfigurationManager.ConnectionStrings["PgSqlConnection"].ToString();
PgSqlConnection pgConn = new PgSqlConnection(strConn);
PgSqlCommand pgCmd = new PgSqlCommand(strQuery, pgConn);
pgConn.Open();
pgCmd.ExecuteNonQuery();
How do I have access to the RETURNING value (pss_clave) so I can use it in fields of other tables?
Regards.