When to do DB Insert

Discussion in 'ASP General' started by Luis, May 26, 2004.

  1. Luis

    Luis Guest

    Application X has three screens. The user captures information on
    screen one and then clicks a navigation button to go onto screen two.
    He does the same on screen three. At the bottom of screen three is a
    submit button. When this button is clicked the system does some
    calculations with the information that is supplied and then uploads
    all the info that was captured on the screens to the database.

    My question is: Should the data be uploaded to the database all on one
    go when the user clicks the submit button or should it be uploaded
    after each screen - ie when the user clicks the button on Screen 1 the
    data is uploaded to the database and then screen two is displayed.

    Which is the better approach?

    My problem with doing the upload after each screen is that the user
    might click the back button and change the info that was captured on
    one of the previous screens. I would have to add code to each screen
    to check if any of the data that the user captured has changed and
    then update the database appropriately.

    Which approach should I take?

    Thanks
    Luis, May 26, 2004
    #1
    1. Advertising

  2. Luis

    Alan Howard Guest

    Horses for courses. A database is just another persistence mechanism - like
    a form post, querystring, session variables or text file. You might want to
    insert into a database to avoid problems with, say, your session timing out
    when your user knocks off from morning tea, or to avoid really long URLs
    from being presented to the user in the browser's address bar. To manage the
    use of a back button, you can do your initial insert and return the ID of a
    record that marks a user's submission - whatever you decide that is. Your
    insert scripts can then check to see if data already exists for this
    submission, and do an update instead of an insert. You pass this
    SubmissionID from page to page in your workflow, and at the end of your
    workflow, mark the submission as complete so that you and your application
    know that row of data is complete and valid.

    You'll need to consider how you're going to identify incomplete submissions
    (session timeouts for e.g., users that don't complete all the pages, etc.)
    and how robust your forms need to be. Be careful to consider what your users
    expect to see - if they click 'Next' on page two, and then *your
    application's* 'Back' button on page three do they expect to see the data
    they just submitted on page two - probably yes - and you can retrieve this
    from the database. If they click *the browser's* back button on page three
    to return to page two, their expectations may be different so make sure that
    your code accommodates this.

    Some things to think about.

    Alan

    "Luis" <> wrote in message
    news:...
    > Application X has three screens. The user captures information on
    > screen one and then clicks a navigation button to go onto screen two.
    > He does the same on screen three. At the bottom of screen three is a
    > submit button. When this button is clicked the system does some
    > calculations with the information that is supplied and then uploads
    > all the info that was captured on the screens to the database.
    >
    > My question is: Should the data be uploaded to the database all on one
    > go when the user clicks the submit button or should it be uploaded
    > after each screen - ie when the user clicks the button on Screen 1 the
    > data is uploaded to the database and then screen two is displayed.
    >
    > Which is the better approach?
    >
    > My problem with doing the upload after each screen is that the user
    > might click the back button and change the info that was captured on
    > one of the previous screens. I would have to add code to each screen
    > to check if any of the data that the user captured has changed and
    > then update the database appropriately.
    >
    > Which approach should I take?
    >
    > Thanks
    Alan Howard, May 26, 2004
    #2
    1. Advertising

  3. Luis

    Luis Guest

    "Alan Howard" <> wrote in message news:<>...
    > You might want to
    > insert into a database to avoid problems with, say, your session timing out
    > when your user knocks off from morning tea <snip>


    Those are the problems I want to prevent.

    <snip>

    > You'll need to consider how you're going to identify incomplete submissions
    > (session timeouts for e.g., users that don't complete all the pages, etc.)


    This is my biggest concern. I don't want the database to end up with a
    whole bunch of incomplete records because the user did not complete
    all the screens and click the submit button on the final screen.

    How do I handle this?
    Luis, May 27, 2004
    #3
  4. Luis

    Alan Howard Guest

    "Luis" <> wrote in message
    news:...
    > "Alan Howard" <> wrote in message

    news:<>...
    > > You might want to
    > > insert into a database to avoid problems with, say, your session timing

    out
    > > when your user knocks off from morning tea <snip>

    >
    > Those are the problems I want to prevent.
    >
    > <snip>
    >
    > > You'll need to consider how you're going to identify incomplete

    submissions
    > > (session timeouts for e.g., users that don't complete all the pages,

    etc.)
    >
    > This is my biggest concern. I don't want the database to end up with a
    > whole bunch of incomplete records because the user did not complete
    > all the screens and click the submit button on the final screen.
    >
    > How do I handle this?


    I generally avoid using the Session to cache variables unless it's stuff
    that really isn't expected to change for the duration of the session - a
    username perhaps. I'm also not a fan of carrying a whole bunch of variables
    around in a form post or querystring - variables that were defined on the
    previous pages of a workflow.

    When I need a multi-page workflow I opt for the save-to-database approach.
    When the first form is submitted the insert into the database returns a
    ResponseHeaderID - a ResponseHeader record groups all Responses from one
    pass through the workflow. You pass the ResponseHeaderID from form to form,
    and you code enough logic into your procs to determine whether new data from
    each from needs to be inserted, or whether what's already there needs to be
    updated (user may have backtracked). On the final submission you mark the
    ResponseHeader as 'complete' (IsComplete BIT NOT NULL DEFAULT(0)) - set the
    IsComplete column to 1. By filtering on the value of this column when
    looking at your results you can ignore all of the incomplete ResponseHeader
    records, and associated Responses.

    Alan
    Alan Howard, May 27, 2004
    #4
  5. Luis

    Luis Guest

    "Alan Howard" <> wrote in message news:<uKNp$>...

    > When I need a multi-page workflow I opt for the save-to-database approach.
    > When the first form is submitted the insert into the database returns a
    > ResponseHeaderID - a ResponseHeader record groups all Responses from one
    > pass through the workflow. You pass the ResponseHeaderID from form to form,
    > and you code enough logic into your procs to determine whether new data from
    > each from needs to be inserted, or whether what's already there needs to be
    > updated (user may have backtracked).


    That's what I've done.

    > On the final submission you mark the
    > ResponseHeader as 'complete' (IsComplete BIT NOT NULL DEFAULT(0)) - set the
    > IsComplete column to 1. By filtering on the value of this column when
    > looking at your results you can ignore all of the incomplete ResponseHeader
    > records, and associated Responses.


    So you would fileter out the "incomplete" submissions by doing
    something like:
    SELECT x,y,z from TableName where IsComplete = "1".

    With this approach the user would only ever have access to the
    "complete" records on the database - but it would still "leave the
    dirty laundry under the bed" (and my mother would love that!). I want
    to be able to remove those incomplete records.

    I thought of a slightly different approach after reading your post -
    maybe it will make sense?

    I would create a seperate table on the database where all the data
    from the various screens is stored while the user is working. When the
    user clicks the Submit button I do the calculations that I need to do
    onSubmit. Then I move all the data from the "temporary" table to the
    main tables and delete the record in the temporary table. The result
    is that the main tables would only contain complete records.
    Incomplete records would remain in the "temporary" table (maybe I
    could provide a "Resume previous session" function).

    Then I just set up a job to truncate the temporary table on a regular
    basis. End of problem! (?)

    Is this a good approach?
    Luis, May 28, 2004
    #5
  6. Luis

    Alan Howard Guest

    "Luis" <> wrote in message
    news:...
    > "Alan Howard" <> wrote in message

    news:<uKNp$>...

    > I would create a seperate table on the database where all the data
    > from the various screens is stored while the user is working. When the
    > user clicks the Submit button I do the calculations that I need to do
    > onSubmit. Then I move all the data from the "temporary" table to the
    > main tables and delete the record in the temporary table. The result
    > is that the main tables would only contain complete records.
    > Incomplete records would remain in the "temporary" table (maybe I
    > could provide a "Resume previous session" function).
    >
    > Then I just set up a job to truncate the temporary table on a regular
    > basis. End of problem! (?)
    >
    > Is this a good approach?


    It depends how important it is to you to clean out those temporary records.
    If you can quantify how long you need to persist a user's incomplete
    responses (the resume previous session functionality you mention) then just
    add a smalldatetime column to store the creation date of a response, and
    then schedule a job to delete those records where IsComplete = 0 that are
    older than a given age. You don't need to complicate the design of your
    database by adding additional tables - you're still going to have partial
    records floating around somewhere.
    Alan Howard, May 29, 2004
    #6
  7. Luis

    Luis Guest

    On Sat, 29 May 2004 13:49:41 +1200, "Alan Howard" wrote:

    >It depends how important it is to you to clean out those temporary records.
    >If you can quantify how long you need to persist a user's incomplete
    >responses (the resume previous session functionality you mention) then just
    >add a smalldatetime column to store the creation date of a response, and
    >then schedule a job to delete those records where IsComplete = 0 that are
    >older than a given age.


    EXCELLENT idea! Thanks...

    >You don't need to complicate the design of your
    >database by adding additional tables


    Very true...

    I've converted almost all of the asp pages so that they maintain state
    on the database using the ideas you suggested (previously using a
    whole bunch of session variables - which I really wasn't happy with).

    It all seems to be going well so far...

    Thanks
    Luis, May 31, 2004
    #7
  8. Luis

    Alan Howard Guest

    Good-o. Let us know how it goes.

    Alan


    "Luis" <> wrote in message
    news:...
    > On Sat, 29 May 2004 13:49:41 +1200, "Alan Howard" wrote:
    >
    > >It depends how important it is to you to clean out those temporary

    records.
    > >If you can quantify how long you need to persist a user's incomplete
    > >responses (the resume previous session functionality you mention) then

    just
    > >add a smalldatetime column to store the creation date of a response, and
    > >then schedule a job to delete those records where IsComplete = 0 that are
    > >older than a given age.

    >
    > EXCELLENT idea! Thanks...
    >
    > >You don't need to complicate the design of your
    > >database by adding additional tables

    >
    > Very true...
    >
    > I've converted almost all of the asp pages so that they maintain state
    > on the database using the ideas you suggested (previously using a
    > whole bunch of session variables - which I really wasn't happy with).
    >
    > It all seems to be going well so far...
    >
    > Thanks
    Alan Howard, May 31, 2004
    #8
    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:
    698
    cannontrodder
    Jul 25, 2006
  2. Replies:
    1
    Views:
    431
  3. Replies:
    1
    Views:
    1,758
    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,028
    eagle
    Jul 12, 2007
  5. Feege
    Replies:
    0
    Views:
    417
    Feege
    Dec 20, 2005
Loading...

Share This Page