Archived

This topic is now archived and is closed to further replies.

ODBC SQLExecute insert - get current row data

This topic is 5155 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

I''ve written an insert statement and sending it through SQLExecute to an MS Access database. The row I''m sending has an (AutoNumber) field that I would like to return. One way I can do this is to run a select statement that searches with the very same data I just sent but none of the fields are unique except the primary key which is generated. A problem exists if another field exists which has the same data as this would return multiple rows. Is there a simple solution to this? Thanks! Microsoft Visual C++ 6.0 Windows XP Pro MS Access 2002

Share this post


Link to post
Share on other sites
An interesting problem.

I don''t know if the Access interface will return the new primary key. If so, that would be the best way.

If not, then consider why your data does not uniquely identify the row. Should it?

If not, then there are a couple of ways around, but with issues. You could do a select on the maximum value of the autonumbered column, and assume that the values increase with each insertion (I believe this is how they work; there is some seed stored in a separate table that gives the next value of the autonumber column). The row with the highest value is the new row you added.

Another way is to have a timestamp on the row, and then search for the row with the latest date.

Both the above suffer the obvious flaw that multiple ''writers'' to the database could mess the scheme up. Could you have two or more programs that write at the same time?

Note that surrounding the insert and subsequent select in a transaction is an option, but I don''t remember if you can ''see'' a newly inserted row inside a transaction. Even if you can, you have to worry about locking issues, since the insert will lock a portion of the table, and if the select is long, you could get a blockage in the database (again, assuming that you have multiple inserters). Also, readers are typically blocked while writers are modifying the data, so even if you have a single writer and multiple readers, there may be performance issues.

Share this post


Link to post
Share on other sites