[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.dotnet.framework.odbcnet

Returning Row ID

Marc Woolfson

12/6/2002 6:09:00 PM

I am attempting to add a new row to a PostgreSQL database table, but wish to
obtain the resulting (automatically incremented) row ID for use in a second
query. The code below is used to perform the insert, but does anyone know
how the output generated (ref
http://www.postgresql.org/idocs/index.php?sql-i...) from the INSERT
query can be obtained?

Thanks,

Marc Woolfson


/* string with which to connect to DB - if Data Source Name already
defined in ODBC admin, use that DSN */
string connectString = "DSN=website";

/* SQL string */
string myInsertQuery = "INSERT INTO sms_comps (sms_comp_name,
sms_comp_desc, sms_comp_active, sms_comp_prize, sms_comp_question) VALUES
(\'"+compName+ "\', \'"+compDesc+"\', true, \'"+compPrize+
"\', \'"+compQuestion+"\')";

OdbcConnection myConnection = new OdbcConnection(connectString);
OdbcCommand myCommand = new OdbcCommand(myInsertQuery,myConnection);

/* check database connection before use */
try
{
myConnection.Open();
myCommand.ExecuteNonQuery();
}
catch (OdbcException ex)
{
Console.WriteLine(ex.Message + "\n\n" + "StackTrace: \n\n" +
ex.StackTrace);
}

/* always close the connection */
myConnection.Close();
1 Answer

Marc Woolfson

12/6/2002 6:58:00 PM

0

I have worked it out using the PostgreSQL currval() function:

/* obtain the ID of the last inserted row -
http://www.postgresql.org/idocs/index.php?functions-seq... */
string mySelectQuery = "SELECT currval('sms_comps_sms_comp_id_seq') AS
comp_id";

and so comp_id can be read by the OdbcDataReader and placed into the next
appropriate insert query.

Marc