Retrieving Record after INSERT INTO statement

  • Thread starter Thread starter Joshua Ellul
  • Start date Start date
J

Joshua Ellul

Hi There,

I am using an OleDb.OleDbCommand to insert a new record using the
ExecuteNonQuery method with an SQL "INSERT INTO" statement. Is it possible
to somehow retrieve the record/s inserted without having to perform an
additional SQL statement?

If not, is there a different method to insert data which automatically
returns the record inserted?

Best Regards,

Joshua
 
Hi Joshua,

If you execute action query, like INSERT, then there is no way to get this
record back other than to use another SELECT statement. If you would like to
keep information on a client side as well, then add record to the DataTable
and then call Update method of the dataAdapter to insert record from the
DataTable into actual table in a database. Otherwise you would need to make
two calls - INSERT and SELECT
 
Once I call the update will the record in the database and the instance in
the datatable be synchronised? I.e. I have an Autonumber field in which the
database engine fills in the autonumber field and not my program, will my
program upon update have the value of the Autonumber field?

Josh
 
You can add an INSERT and then copy that number to a different table, this way you know what you did. Doing that and adding when it occurred helps a lot
 
Joshua Ellul said:
Hi There,

I am using an OleDb.OleDbCommand to insert a new record using the
ExecuteNonQuery method with an SQL "INSERT INTO" statement. Is it possible
to somehow retrieve the record/s inserted without having to perform an
additional SQL statement?

If not, is there a different method to insert data which automatically
returns the record inserted?
Click to expand...


OleDbCommand could be anything. This behavior depends on your database.

For Oracle you can do

insert into t(a,b,c) values(1,2,3) returning (a,b,c) into (:1,:2,:3)

for SqlServer and Oracle you can do

insert into t(a,b,c) values(1,2,3); select * from t where id = 1

and then use ExecuteReader instead of ExecuteNonQuery.

David
 
Back
Top