How do I retrieve the key value when I INSERT a record into an Access DB
using a a Dynamic SQL statement (via ADO)?
In SQL Server, I would use a Stored Procedure and return SCOPE_IDENTITY..
However, this scenario is using dynamic SQL and an access db, so I'm
stumped...
Thanks
Chris
CJM wrote:
> How do I retrieve the key value when I INSERT a record into an Access
> DB using a a Dynamic SQL statement (via ADO)?
>
> In SQL Server, I would use a Stored Procedure and return
> SCOPE_IDENTITY..
> However, this scenario is using dynamic SQL and an access db, so I'm
> stumped...
>
Assuming you're using the Jet 4.0 OLE DB Provider, you can use SELECT
@@IDENTITY immediately after doing your INSERT
http://www.aspfaq.com/show.asp?id=2174
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Thanks Bob,
This is a temporary solution so it doesnt matter much, and it's unlikely
that @@identity will return the wrong ID (low-usage system), but obviously
it's not ideal.
On the other hand, I hate Aaron's suggested solution of using rs.insert.
Out of intersest, do you know if/how this could be achieved with MySQL?
Thanks
CJM wrote:
> Thanks Bob,
>
> This is a temporary solution so it doesnt matter much, and it's
> unlikely that @@identity will return the wrong ID (low-usage system),
> but obviously it's not ideal.
Actually, it IS ideal. It is much better than using SELECT max(id). Even
with a high-usage system, it should be bulletproof. From
http://support.microsoft.com/?kbid=232144:
************************************************************ ******
SUMMARY
The Jet OLE DB version 4.0 provider supports the SELECT @@Identity query
that allows you to retrieve the value of the auto-increment field generated
on your connection. Auto-increment values used on other connections to your
database do not affect the results of this specialized query. This feature
works with Jet 4.0 databases but not with older formats.
************************************************************ **********
>
> On the other hand, I hate Aaron's suggested solution of using
> rs.insert.
I think he does too. He was simply trying to be complete. In earlier
versions of Access (pre-Jet 4.0), @@IDENTITY did not exist, so using a
server-side cursor was the only way to dependable achieve this objective
>
> Out of intersest, do you know if/how this could be achieved with
> MySQL?
Not a clue, and not really interested in finding out. :-)
I assume MySQL documentation is freely available somewhere. :-)
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
"Bob Barrows [MVP]"
news:unP%23%23vw9EHA.824@TK2MSFTNGP11.phx.gbl...
> CJM wrote:
>> Thanks Bob,
>>
>> This is a temporary solution so it doesnt matter much, and it's
>> unlikely that @@identity will return the wrong ID (low-usage system),
>> but obviously it's not ideal.
>
> Actually, it IS ideal. It is much better than using SELECT max(id). Even
> with a high-usage system, it should be bulletproof. From
> http://support.microsoft.com/?kbid=232144:
>
> ************************************************************ ******
> SUMMARY
> The Jet OLE DB version 4.0 provider supports the SELECT @@Identity query
> that allows you to retrieve the value of the auto-increment field
> generated on your connection. Auto-increment values used on other
> connections to your database do not affect the results of this specialized
> query. This feature works with Jet 4.0 databases but not with older
> formats.
> ************************************************************ **********
>>
Re-reading it again, it does suggest that if I use the same connection I am
OK. In those conditions, it seams like @@identity behaves effectively like
Scope_Identy() in SQL Server. Initially, I thought that connection pooling
would leave me vulnerable, but obviously if I dont relinquish my connection,
I'm OK.
>>
>> Out of intersest, do you know if/how this could be achieved with
>> MySQL?
>
> Not a clue, and not really interested in finding out. :-)
> I assume MySQL documentation is freely available somewhere. :-)
Not a fan? This whole thing is for a personal project, and unfortunately I'm
too tight-fisted to pay for SQL Server hosting... so it's either Access or
MySQL.
Yes, I've got other places to go for MySQL info, so if I get stuck, I'll
post a question there..
Thanks for the help.
Chris