Getting Identity after Update method

I have a table recordset using rs.Open .....
I am adding records via
rs.AddNew
...
rs.Update
Is the new Identity value available immediately after the Update method, for
example
rs.Update
lngNewID = rs.RecordID
where RecordID is the Identity field. Thanks.
DavidDon't use AddNew, use an INSERT statement... or better yet, encapsulate it
in a stored procedure.
http://www.aspfaq.com/2174
http://www.aspfaq.com/2201
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:uElqca#KFHA.4092@.tk2msftngp13.phx.gbl...
> I have a table recordset using rs.Open .....
> I am adding records via
> rs.AddNew
> ...
> rs.Update
> Is the new Identity value available immediately after the Update method,
for
> example
> rs.Update
> lngNewID = rs.RecordID
> where RecordID is the Identity field. Thanks.
> David
>|||I agree and normally always use stored procs. However, I need to run
another function before I am done with this record that has to use the
new ID to populate one or more records in another table, return the
total and update the new record I am on.
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||> I agree and normally always use stored procs. However, I need to run
> another function before I am done with this record that has to use the
> new ID to populate one or more records in another table, return the
> total and update the new record I am on.
Why can't the stored procedure do this also? Or call another stored
procedure that does?
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||I don't understand why you can't do this with a proc but anyway you
should be able to retrieve the IDENTITY based on key value(s) for the
row you just added. IDENTITY should never be the only key of a table so
just use an alternate key:
SELECT id
FROM YourTable
WHERE key_col = <x>
Of course if you didn't declare an alternate key then you have other
problems too! :-)
David Portas
SQL Server MVP
--|||David,
You are correct, after update rs("ID_Col") will contain the newly inserted
id after Update is run.
-- Alex
"David C" wrote:

> I have a table recordset using rs.Open .....
> I am adding records via
> rs.AddNew
> ...
> rs.Update
> Is the new Identity value available immediately after the Update method, f
or
> example
> rs.Update
> lngNewID = rs.RecordID
> where RecordID is the Identity field. Thanks.
> David
>
>|||Mostly because the function that creates the related table records is
extremely complicated and conditional that it would take too long to
write and test it. This is an application that was ported from an
Access back end to SQL Server and I am under a time constraint so I have
to use as much existing code as possible. Hopefully, I'll have time to
analyze it later and maybe then I can convert to a stored proc(s), which
is my preference also.
p.s. The other function is a very complicated union dues calculation
that was set up for a government agency. Please try to understand.
Thanks
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||> Please try to understand.
I think I do understand. Reminds me of things like:
"Don't fix the leaking toilet. Just build a drain from your bathroom to the
reverse osmosis tank in the basement."|||That's a bit of a harsh analogy, don't you think ;-).
The ADO method has its advantages:
> quicker to develop & implement
> doesn't require create_proc permission in db (ie, doesn't involve getting
someone to grant those)
How about, "Wrap a bunch of ducttape around the pipe from the leaking
toilet" instead?
I'm sure it'll work just as well, but it isn't pretty and it isn't the right
way to do it ... but it's a heck of a lot cheaper.
-- Alex Papadimoulis
"Aaron [SQL Server MVP]" wrote:

> I think I do understand. Reminds me of things like:
> "Don't fix the leaking toilet. Just build a drain from your bathroom to t
he
> reverse osmosis tank in the basement."
>
>