Unexpected behaviour

[Terry Murray]

Thanks for your quick response. I thought I should also let you know that I
am using Access as the database on the backend. That probably means that
stored procedures are out for me. Also, I am not too sure what
SCOPE_IDENTITY() is and does. Is that something that will only work if
cookies are being used? Or is it the stored procedure that you suggest that
I use. An explanation of how the "database PK for a new ResponseHeader
record" could be used would be very useful as well. As I have no idea about
that means or involves.

Thanks in advance, Alex

Terry

"Alan" <SPAMMENOTalan.howard@inspire.net.nz> wrote in message
news:uZVDhPsXDHA.736@TK2MSFTNGP09.phx.gbl...

> Think of it from your users' perspectives - if they hit the back button

then

> they'll be expecting to see whatever they entered on the previous page -
> period. Now find a way to code it. This has nothing to do with one or many
> scripts, your method of persistence, etc. Quite often it's about user
> (project sponsor) requirements.
>
> If cookies are disabled and you must still be able to complete a survey
> (requirement?) then cookies and the session are out. You could persist the
> data to the database as you go, passing some identifying ID from form to
> form - the database PK for a new ResponseHeader record perhaps. Regardless
> of how you get to a page, browser Back/Forward buttons, form
> Next/Back/Submit (whatever) buttons, you do a lookup to see if there are
> results in the database for that respondent at that point in the survey.

If

> there are you retrieve them and use them to set the default state of the
> controls on that form.
>
> Looking at your particular questions, are you required to support
> respondents with cookies off? If so then persist to the database, hidden
> form fields, querystring as appropriate. If not then put a cookie

detection

> routine on the default page of your app. Redirect to an error page if
> cookies aren't supported, or to the registration page if they are.
> Alternatively code for the minimum permissible spec (no cookies) and

forget

> the detection routine.
>
> Your second question - what data is being reinserted? If it's the
> registration data then do a lookup of that data to see if it's already in
> the database. It's quite common to see stored procs that are in effect:
>
> if exists (select * from ... where <my current criteria here>)
> begin
>
> -- yep - in the db, select and return the ID
>
> end
> else
> begin
>
> -- nope - not in the db. Insert the new data, get the new ID using
> SCOPE_IDENTITY() and return it.
>
> end
>
> which allow them to insert a new record and return its ID, or just to

return

> the ID if the record is already found based on some other unique data.
>
> This way your user will hit back and, after detecting a response for that
> person, you can gracefully redirect to an error page. If no record is

found

> then you know their details are new and you can continue to the survey

page.

>
> You can also use this approach to handle the retrieval/resubmission of
> complete or incomplete survey data too. If your users are anything like

mine

> the next thing they'll want are multiple page surveys/questionnaires and

the

> ability to save their work in progress.
>
> I hope this gives you a few ideas.
>
> Regards,
>
> Alan
>
>
>
> "Terry Murray" <tgmurray@rogers.com> wrote in message
> news:2OdZa.134694$hOa.105759@news02.bloor.is.net.c able.rogers.com...

> > I think I will try to give you an idea of what I am trying to accomplish

> and

> > the way in which I am going.
> >
> > Basically, I am creating a registration system that allows the user upon
> > successful registration to complete a survey.
> >
> > I have decided to accomplish everything within one script.
> >
> > Was that a bad idea?
> >
> > I would have liked to have used Session variables, but as pointed out to

> me

> > earlier, if the user machine is not accepting cookies then I will have
> > problems.
> >
> > There a few issues that I am hoping you can provide direction for me.
> >
> > 1) Maintaining state
> > What I mean by this is as soon as the user has entered all the

> information

> > that is required to become "registered" I would like him/her to then
> > complete
> > the survey. Because the registration section is contained in one

form,

> I

> > was thinking of simply passing the username
> > over to the other form as a hidden field. Is this idea kosher? (i.e.

> are

> > then any pitfalls with this approach). Keep in mind
> > that by this time the information for registration would have already

> been

> > inserted into the database.
> >
> > 2) Not allowing an attempt to re-insert the same information into the
> > database.
> > Currently, if the user presses the back button of the browser, after

she

> > has finished completing
> > the registration form and is now completing the survey section, an

> attempt

> > to insert the same
> > data into the database occurs thereby causing an ugly error page to

> appear

> > stating that the insert could not occur.
> > Is there a simple way to make this situation impossible. I was

thinking

> of

> > disabling the back button but I now know that
> > is not possible and would not really solve the problem. I was also
> > thinking that perhaps because of my decision to contain
> > all the logic in one script (one asp.file) I am actually making things
> > more difficult.
> >
> >
> > I am hoping that the suggestions that you provide are not difficult as I
> > rather new to this.
> >
> > I have noticed that many books discuss the use the "Global.asa" file.

> Does

> > not the use of this require that the client's browsers accept cookies
> >
> > Once again, thanks in advance
> >
> > Terry
> >
> >

>
>

[Alan]

Gidday mate,

SCOPE_IDENTITY() is a SQL Server function that returns the identity value
for the record just inserted into the database. It allows you to find out
what that value is (and return it to the client if required) without having
to do another SELECT based on a unique combination of the data you just
inserted. If you're using Access you might just have to do the INSERT and
then do a separate SELECT to get the new ID.

Some time ago I was called in to audit (and then fix) a questionnaire app
that had been knocked together by a project team at a local university. They
had one denormalised table - one row per respondent, a column for each item
of respondent data, and then one column for each response to a survey
question. They hadn't anticipated users' use of the back button, and soon
had some naaaasty data in the database - they couldn't tell whether a row
represented a complete submission or whether the user had banged-out halfway
through, whether a user had backed up to choose a new jobtitle (for example)
and in going forward again had created a duplicate submission. The data was
a mess, and taking the application down a day after going live to 1000 users
isn't a good look.

I don't want to make your app more complicated than it needs to be, but I
commonly store respondent details in one table, which I might call
ResponseHeaders. Each individual question response is stored as a row, not a
column, in a table called Responses, and each response has a FK column
containing the ID of the associated ResponseHeader.

This gives you a bit more flexibility in your application and database
design. If your users suddenly want a new question added to your survey, at
worst you're just changing your form submission processor to store another
value - not having to add a new column to your database.

When I mention "database PK for a new ResponseHeader record", what I'm
implying is that you persist the respondent details to the ResponseHeaders
table in the database once they submit the first form, and retrieve the ID
for this new record - you now have one ID that you can pass around to
represent the respondent instead of a first name, last name, job title,
address, etc. When a respondent then submits the survey page, you write one
row for each question on the form into the Responses table, along with the
ID for the ResponseHeader - this keeps allows you to keep responses from the
same respondent together - via the ResponseHeaders record.

I hope this helps a bit. Let me know if you have any other questions.

Regards,

Alan




"Terry Murray" <tgmurray@rogers.com> wrote in message
news:9IeZa.135048$hOa.132777@news02.bloor.is.net.c able.rogers.com...

> Thanks for your quick response. I thought I should also let you know that

I

> am using Access as the database on the backend. That probably means that
> stored procedures are out for me. Also, I am not too sure what
> SCOPE_IDENTITY() is and does. Is that something that will only work if
> cookies are being used? Or is it the stored procedure that you suggest

that

> I use. An explanation of how the "database PK for a new ResponseHeader
> record" could be used would be very useful as well. As I have no idea

about

> that means or involves.
>
> Thanks in advance, Alex
>
> Terry
>
> "Alan" <SPAMMENOTalan.howard@inspire.net.nz> wrote in message
> news:uZVDhPsXDHA.736@TK2MSFTNGP09.phx.gbl...

> > Think of it from your users' perspectives - if they hit the back button

> then

> > they'll be expecting to see whatever they entered on the previous page -
> > period. Now find a way to code it. This has nothing to do with one or

many

> > scripts, your method of persistence, etc. Quite often it's about user
> > (project sponsor) requirements.
> >
> > If cookies are disabled and you must still be able to complete a survey
> > (requirement?) then cookies and the session are out. You could persist

the

> > data to the database as you go, passing some identifying ID from form to
> > form - the database PK for a new ResponseHeader record perhaps.

Regardless

> > of how you get to a page, browser Back/Forward buttons, form
> > Next/Back/Submit (whatever) buttons, you do a lookup to see if there are
> > results in the database for that respondent at that point in the survey.

> If

> > there are you retrieve them and use them to set the default state of the
> > controls on that form.
> >
> > Looking at your particular questions, are you required to support
> > respondents with cookies off? If so then persist to the database, hidden
> > form fields, querystring as appropriate. If not then put a cookie

> detection

> > routine on the default page of your app. Redirect to an error page if
> > cookies aren't supported, or to the registration page if they are.
> > Alternatively code for the minimum permissible spec (no cookies) and

> forget

> > the detection routine.
> >
> > Your second question - what data is being reinserted? If it's the
> > registration data then do a lookup of that data to see if it's already

in

> > the database. It's quite common to see stored procs that are in effect:
> >
> > if exists (select * from ... where <my current criteria here>)
> > begin
> >
> > -- yep - in the db, select and return the ID
> >
> > end
> > else
> > begin
> >
> > -- nope - not in the db. Insert the new data, get the new ID using
> > SCOPE_IDENTITY() and return it.
> >
> > end
> >
> > which allow them to insert a new record and return its ID, or just to

> return

> > the ID if the record is already found based on some other unique data.
> >
> > This way your user will hit back and, after detecting a response for

that

> > person, you can gracefully redirect to an error page. If no record is

> found

> > then you know their details are new and you can continue to the survey

> page.

> >
> > You can also use this approach to handle the retrieval/resubmission of
> > complete or incomplete survey data too. If your users are anything like

> mine

> > the next thing they'll want are multiple page surveys/questionnaires and

> the

> > ability to save their work in progress.
> >
> > I hope this gives you a few ideas.
> >
> > Regards,
> >
> > Alan
> >
> >
> >
> > "Terry Murray" <tgmurray@rogers.com> wrote in message
> > news:2OdZa.134694$hOa.105759@news02.bloor.is.net.c able.rogers.com...

> > > I think I will try to give you an idea of what I am trying to

accomplish

> > and

> > > the way in which I am going.
> > >
> > > Basically, I am creating a registration system that allows the user

upon

> > > successful registration to complete a survey.
> > >
> > > I have decided to accomplish everything within one script.
> > >
> > > Was that a bad idea?
> > >
> > > I would have liked to have used Session variables, but as pointed out

to

> > me

> > > earlier, if the user machine is not accepting cookies then I will have
> > > problems.
> > >
> > > There a few issues that I am hoping you can provide direction for me.
> > >
> > > 1) Maintaining state
> > > What I mean by this is as soon as the user has entered all the

> > information

> > > that is required to become "registered" I would like him/her to then
> > > complete
> > > the survey. Because the registration section is contained in one

> form,

> > I

> > > was thinking of simply passing the username
> > > over to the other form as a hidden field. Is this idea kosher?

(i.e.

> > are

> > > then any pitfalls with this approach). Keep in mind
> > > that by this time the information for registration would have

already

> > been

> > > inserted into the database.
> > >
> > > 2) Not allowing an attempt to re-insert the same information into the
> > > database.
> > > Currently, if the user presses the back button of the browser, after

> she

> > > has finished completing
> > > the registration form and is now completing the survey section, an

> > attempt

> > > to insert the same
> > > data into the database occurs thereby causing an ugly error page to

> > appear

> > > stating that the insert could not occur.
> > > Is there a simple way to make this situation impossible. I was

> thinking

> > of

> > > disabling the back button but I now know that
> > > is not possible and would not really solve the problem. I was also
> > > thinking that perhaps because of my decision to contain
> > > all the logic in one script (one asp.file) I am actually making

things

> > > more difficult.
> > >
> > >
> > > I am hoping that the suggestions that you provide are not difficult as

I

> > > rather new to this.
> > >
> > > I have noticed that many books discuss the use the "Global.asa" file.

> > Does

> > > not the use of this require that the client's browsers accept cookies
> > >
> > > Once again, thanks in advance
> > >
> > > Terry
> > >
> > >

> >
> >

>
>

[Terry Murray]

Hi again Alan. Thanks a ton for the suggestions and direction that you have
provided me. I was considering the multi-page approach for the survey but
thought that the only way to accomplish it was to pass each of the responses
to the next form as hidden fields or store the responses in the database as
I go from question to question. Is there another approach that I have not
considered that you would suggest that I employ? Keep in mind that I am
using Access as the backend. Perhaps it is even possible to have the whole
survey only require one form with each question appearing on its own page.
I am not sure how this would be done though. I thought about storing each
response in that database as I go along but thought that this might be
taxing on the server. There are about 30 questions in total. What about
writing to a file and then grabbing the results from the file when need
arise (i.e moving back to a previous question or actually eventually storing
all the results into the database)? Is that less taxing then going to the
database each time a response is made or the person moves back or forward
within the survey?

Thanks again

Terry

"Alan" <SPAMMENOTalan.howard@inspire.net.nz> wrote in message
news:uWwUSitXDHA.2648@TK2MSFTNGP09.phx.gbl...

> Gidday mate,
>
> SCOPE_IDENTITY() is a SQL Server function that returns the identity value
> for the record just inserted into the database. It allows you to find out
> what that value is (and return it to the client if required) without

having

> to do another SELECT based on a unique combination of the data you just
> inserted. If you're using Access you might just have to do the INSERT and
> then do a separate SELECT to get the new ID.
>
> Some time ago I was called in to audit (and then fix) a questionnaire app
> that had been knocked together by a project team at a local university.

They

> had one denormalised table - one row per respondent, a column for each

item

> of respondent data, and then one column for each response to a survey
> question. They hadn't anticipated users' use of the back button, and soon
> had some naaaasty data in the database - they couldn't tell whether a row
> represented a complete submission or whether the user had banged-out

halfway

> through, whether a user had backed up to choose a new jobtitle (for

example)

> and in going forward again had created a duplicate submission. The data

was

> a mess, and taking the application down a day after going live to 1000

users

> isn't a good look.
>
> I don't want to make your app more complicated than it needs to be, but I
> commonly store respondent details in one table, which I might call
> ResponseHeaders. Each individual question response is stored as a row, not

a

> column, in a table called Responses, and each response has a FK column
> containing the ID of the associated ResponseHeader.
>
> This gives you a bit more flexibility in your application and database
> design. If your users suddenly want a new question added to your survey,

at

> worst you're just changing your form submission processor to store another
> value - not having to add a new column to your database.
>
> When I mention "database PK for a new ResponseHeader record", what I'm
> implying is that you persist the respondent details to the ResponseHeaders
> table in the database once they submit the first form, and retrieve the ID
> for this new record - you now have one ID that you can pass around to
> represent the respondent instead of a first name, last name, job title,
> address, etc. When a respondent then submits the survey page, you write

one

> row for each question on the form into the Responses table, along with the
> ID for the ResponseHeader - this keeps allows you to keep responses from

the

> same respondent together - via the ResponseHeaders record.
>
> I hope this helps a bit. Let me know if you have any other questions.
>
> Regards,
>
> Alan
>
>
>
>
> "Terry Murray" <tgmurray@rogers.com> wrote in message
> news:9IeZa.135048$hOa.132777@news02.bloor.is.net.c able.rogers.com...

> > Thanks for your quick response. I thought I should also let you know

that

> I

> > am using Access as the database on the backend. That probably means

that

> > stored procedures are out for me. Also, I am not too sure what
> > SCOPE_IDENTITY() is and does. Is that something that will only work if
> > cookies are being used? Or is it the stored procedure that you suggest

> that

> > I use. An explanation of how the "database PK for a new ResponseHeader
> > record" could be used would be very useful as well. As I have no idea

> about

> > that means or involves.
> >
> > Thanks in advance, Alex
> >
> > Terry
> >
> > "Alan" <SPAMMENOTalan.howard@inspire.net.nz> wrote in message
> > news:uZVDhPsXDHA.736@TK2MSFTNGP09.phx.gbl...

> > > Think of it from your users' perspectives - if they hit the back

button

> > then

> > > they'll be expecting to see whatever they entered on the previous

page -

> > > period. Now find a way to code it. This has nothing to do with one or

> many

> > > scripts, your method of persistence, etc. Quite often it's about user
> > > (project sponsor) requirements.
> > >
> > > If cookies are disabled and you must still be able to complete a

survey

> > > (requirement?) then cookies and the session are out. You could persist

> the

> > > data to the database as you go, passing some identifying ID from form

to

> > > form - the database PK for a new ResponseHeader record perhaps.

> Regardless

> > > of how you get to a page, browser Back/Forward buttons, form
> > > Next/Back/Submit (whatever) buttons, you do a lookup to see if there

are

> > > results in the database for that respondent at that point in the

survey.

> > If

> > > there are you retrieve them and use them to set the default state of

the

> > > controls on that form.
> > >
> > > Looking at your particular questions, are you required to support
> > > respondents with cookies off? If so then persist to the database,

hidden

> > > form fields, querystring as appropriate. If not then put a cookie

> > detection

> > > routine on the default page of your app. Redirect to an error page if
> > > cookies aren't supported, or to the registration page if they are.
> > > Alternatively code for the minimum permissible spec (no cookies) and

> > forget

> > > the detection routine.
> > >
> > > Your second question - what data is being reinserted? If it's the
> > > registration data then do a lookup of that data to see if it's already

> in

> > > the database. It's quite common to see stored procs that are in

effect:

> > >
> > > if exists (select * from ... where <my current criteria here>)
> > > begin
> > >
> > > -- yep - in the db, select and return the ID
> > >
> > > end
> > > else
> > > begin
> > >
> > > -- nope - not in the db. Insert the new data, get the new ID using
> > > SCOPE_IDENTITY() and return it.
> > >
> > > end
> > >
> > > which allow them to insert a new record and return its ID, or just to

> > return

> > > the ID if the record is already found based on some other unique data.
> > >
> > > This way your user will hit back and, after detecting a response for

> that

> > > person, you can gracefully redirect to an error page. If no record is

> > found

> > > then you know their details are new and you can continue to the survey

> > page.

> > >
> > > You can also use this approach to handle the retrieval/resubmission of
> > > complete or incomplete survey data too. If your users are anything

like

> > mine

> > > the next thing they'll want are multiple page surveys/questionnaires

and

> > the

> > > ability to save their work in progress.
> > >
> > > I hope this gives you a few ideas.
> > >
> > > Regards,
> > >
> > > Alan
> > >
> > >
> > >
> > > "Terry Murray" <tgmurray@rogers.com> wrote in message
> > > news:2OdZa.134694$hOa.105759@news02.bloor.is.net.c able.rogers.com...
> > > > I think I will try to give you an idea of what I am trying to

> accomplish

> > > and
> > > > the way in which I am going.
> > > >
> > > > Basically, I am creating a registration system that allows the user

> upon

> > > > successful registration to complete a survey.
> > > >
> > > > I have decided to accomplish everything within one script.
> > > >
> > > > Was that a bad idea?
> > > >
> > > > I would have liked to have used Session variables, but as pointed

out

> to

> > > me
> > > > earlier, if the user machine is not accepting cookies then I will

have

> > > > problems.
> > > >
> > > > There a few issues that I am hoping you can provide direction for

me.

> > > >
> > > > 1) Maintaining state
> > > > What I mean by this is as soon as the user has entered all the
> > > information
> > > > that is required to become "registered" I would like him/her to

then

> > > > complete
> > > > the survey. Because the registration section is contained in one

> > form,

> > > I
> > > > was thinking of simply passing the username
> > > > over to the other form as a hidden field. Is this idea kosher?

> (i.e.

> > > are
> > > > then any pitfalls with this approach). Keep in mind
> > > > that by this time the information for registration would have

> already

> > > been
> > > > inserted into the database.
> > > >
> > > > 2) Not allowing an attempt to re-insert the same information into

the

> > > > database.
> > > > Currently, if the user presses the back button of the browser,

after

> > she

> > > > has finished completing
> > > > the registration form and is now completing the survey section, an
> > > attempt
> > > > to insert the same
> > > > data into the database occurs thereby causing an ugly error page

to

> > > appear
> > > > stating that the insert could not occur.
> > > > Is there a simple way to make this situation impossible. I was

> > thinking

> > > of
> > > > disabling the back button but I now know that
> > > > is not possible and would not really solve the problem. I was also
> > > > thinking that perhaps because of my decision to contain
> > > > all the logic in one script (one asp.file) I am actually making

> things

> > > > more difficult.
> > > >
> > > >
> > > > I am hoping that the suggestions that you provide are not difficult

as

> I

> > > > rather new to this.
> > > >
> > > > I have noticed that many books discuss the use the "Global.asa"

file.

> > > Does
> > > > not the use of this require that the client's browsers accept

cookies

> > > >
> > > > Once again, thanks in advance
> > > >
> > > > Terry
> > > >
> > > >
> > >
> > >

> >
> >

>
>