DB insert question

Discussion in 'ASP .Net' started by A Lonely Programmer, Feb 17, 2004.

  1. 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
    A Lonely Programmer, Feb 17, 2004
    #1
    1. Advertising

  2. 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
    =?Utf-8?B?VHUtVGhhY2g=?=, Feb 17, 2004
    #2
    1. Advertising

  3. A Lonely Programmer

    Muckey Guest

    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" <> wrote in message
    news:...
    > I haven't experienced that problem myself, but I think it is possible.

    Have you tried to wrap the insert in a transaction?
    >
    > Tu-Thach
    >
    > ----- 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
    >
    >
    >
    Muckey, Feb 18, 2004
    #3
  4. 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
    =?Utf-8?B?U3RldmUgTWVudHplcg==?=, Feb 18, 2004
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. cannontrodder
    Replies:
    1
    Views:
    711
    cannontrodder
    Jul 25, 2006
  2. Replies:
    1
    Views:
    436
  3. Replies:
    1
    Views:
    1,769
    Albert Hopkins
    Dec 6, 2008
  4. eagle

    Insert & Insert/Repeat buttons in a detailsview

    eagle, Jul 12, 2007, in forum: ASP .Net Datagrid Control
    Replies:
    0
    Views:
    1,034
    eagle
    Jul 12, 2007
  5. Feege
    Replies:
    0
    Views:
    435
    Feege
    Dec 20, 2005
Loading...

Share This Page