By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,936 Members | 2,023 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,936 IT Pros & Developers. It's quick & easy.

When to do DB Insert

P: n/a
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
Jul 19 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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" <an****@webmail.co.za> wrote in message
news:69**************************@posting.google.c om...
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

Jul 19 '05 #2

P: n/a
"Alan Howard" <Xa***********@Xparadise.net.nzX> wrote in message news:<eG*************@TK2MSFTNGP10.phx.gbl>...
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?
Jul 19 '05 #3

P: n/a
"Luis" <an****@webmail.co.za> wrote in message
news:69**************************@posting.google.c om...
"Alan Howard" <Xa***********@Xparadise.net.nzX> wrote in message news:<eG*************@TK2MSFTNGP10.phx.gbl>...
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
Jul 19 '05 #4

P: n/a
"Alan Howard" <Xa***********@Xparadise.net.nzX> wrote in message news:<uK**************@TK2MSFTNGP12.phx.gbl>...
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?
Jul 19 '05 #5

P: n/a

"Luis" <an****@webmail.co.za> wrote in message
news:69**************************@posting.google.c om...
"Alan Howard" <Xa***********@Xparadise.net.nzX> wrote in message news:<uK**************@TK2MSFTNGP12.phx.gbl>...
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.
Jul 19 '05 #6

P: n/a
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
Jul 19 '05 #7

P: n/a
Good-o. Let us know how it goes.

Alan
"Luis" <an****@webmail.co.za> wrote in message
news:6b********************************@4ax.com...
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 justadd 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

Jul 19 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.