DB insert question

  • Thread starter Thread starter A Lonely Programmer
  • Start date Start date
A

A Lonely Programmer

Ok i know that using an access db is a corporate nono but i am not about to
buy sql for anybody (well maybe myself someday) and an approved msde update
must come AFTER i get certain things up and going. With that in mind, i have
a problem and i was wondering if anyone could maybe confirm my hunch.

I have an asp.net app that inserts a form into a db. The user inserts the
info then a parametized query runs updates the db. After the code runs the
db update code it grabs the current identity from the database then
redirects to a display page that allows the user to see the newly inserted
data.

Sometimes, the redirect page displays data not for the record that was just
inserted but rather the one inserted before that. So if i were using
autoincrement for my pk and had just inserted record number 15, the details
page will show record 14 instead. I suspect what is happening is that
somehow the page is getting the @@identity back before the record was
inserted. Is this possible? if so are there work arounds?

thanks steve
 
I haven't experienced that problem myself, but I think it is possible. Have you tried to wrap the insert in a transaction

Tu-Thac

----- A Lonely Programmer wrote: ----

Ok i know that using an access db is a corporate nono but i am not about t
buy sql for anybody (well maybe myself someday) and an approved msde updat
must come AFTER i get certain things up and going. With that in mind, i hav
a problem and i was wondering if anyone could maybe confirm my hunch

I have an asp.net app that inserts a form into a db. The user inserts th
info then a parametized query runs updates the db. After the code runs th
db update code it grabs the current identity from the database the
redirects to a display page that allows the user to see the newly inserte
data

Sometimes, the redirect page displays data not for the record that was jus
inserted but rather the one inserted before that. So if i were usin
autoincrement for my pk and had just inserted record number 15, the detail
page will show record 14 instead. I suspect what is happening is tha
somehow the page is getting the @@identity back before the record wa
inserted. Is this possible? if so are there work arounds

thanks stev
 
I haven't. i was under the impression that transactions and access are like
radios and a bathtub. I'll look into this tomorrow morning, thanks a ton

steve

Tu-Thach said:
I haven't experienced that problem myself, but I think it is possible.
Click to expand...
Have you tried to wrap the insert in a transaction?
 
I assume that you are talking to a sql server... do the following:

-- begin sql script

set nocount on

insert into -- yadda yadda yadda... do your insert here

select scope_identity() as theRowID

-- end sql script...

then in your asp.net code, execute the statement, and get the dataset/sqldatareader and get the value of "theRowID" column.

Don't use @@identity. Always use scope_identity(). See the TSql for more info. @@identity is subject to alteration by triggers, etc. Basically, when you want to find out the row id after an insert, always use scope_identity().

Good Luck!







----- A Lonely Programmer wrote: -----

Ok i know that using an access db is a corporate nono but i am not about to
buy sql for anybody (well maybe myself someday) and an approved msde update
must come AFTER i get certain things up and going. With that in mind, i have
a problem and i was wondering if anyone could maybe confirm my hunch.

I have an asp.net app that inserts a form into a db. The user inserts the
info then a parametized query runs updates the db. After the code runs the
db update code it grabs the current identity from the database then
redirects to a display page that allows the user to see the newly inserted
data.

Sometimes, the redirect page displays data not for the record that was just
inserted but rather the one inserted before that. So if i were using
autoincrement for my pk and had just inserted record number 15, the details
page will show record 14 instead. I suspect what is happening is that
somehow the page is getting the @@identity back before the record was
inserted. Is this possible? if so are there work arounds?

thanks steve
 
Back
Top