More form processing

[Simon]

Hey folks,

I need some of your expertiese again. I am creating a survey form and need
some help gathering the results of a question that has checkboxes. There
are four options for one of the questions for example

Where do you normally buy books?
o Bookstore
o Online
o Book club
o Other

I'm going to store the answers in a DB and I dont want to have have four
fields if only two of them are going to get used. Im trying to find a GOOD
way to collect the results perhaps in an array or comma seperated list so I
could store it in onefield.

Right now here is what I have

<%
strBuyBook1 = Trim(Request.Form("buy_book1"))
strBuyBook2 = Trim(Request.Form("buy_book2"))
strBuyBook3 = Trim(Request.Form("buy_book3"))
strBuyBook4 = Trim(Request.Form("buy_book4"))

Connect to the database
call OpenDB()

SQL = "INSERT INTO tblAnswers( BuyBook1, BuyBook2, BuyBook3, BuyBook4)" & _
"VALUES ('" & strBuyBook1 & "', '" & strBuyBook2 & "', '" &
strBuyBook3 & "', '" & strBuyBook4 & "')"

con.Execute SQL

%>


Thanks in anticipation
Simon

[Andrew Durstewitz]

I would create a second table with a list of every item that is
available and associate the text string with a unique key. Then place
the unique key in the users record seperated by commas.

hth,
Andrew

* * * Sent via DevBuilder [url]http://www.devbuilder.org[/url] * * *
Developer Resources for High End Developers.

[Bob Barrows]

Nah, you're still thinking in spreadsheet terms. There are good reasons for
database normalization.
Simon wrote:

> I agree I dont really want to store multiple values in a single
> field, I would like to have two fields in my database. I was
> thinking along the lines using the split function to create an array
> and inserting each array item into its own field. To do that I would
> have to prevent teh user form checking more than two checkboxes but
> Im not sure how to do that either :-\
>
>
> "Bob Barrows" <reb_01501@yahoo.com> wrote in message
> news:OICsO%23%23PDHA.3880@tk2msftngp13.phx.gbl...

>> <rant>
>> It is a bad idea to store multiple data values in a single field.
>> This is a database, not a spreadsheet. Someday you are going to need
>> to look at these values individually: think of the hell you are
>> going to go through to separate them out. Say you want to know what
>> percentage of people use a bookstore?
>> </rant>
>>
>> You should use a separate table called BookSources with two columns:
>> ResponseID and BookSource. ResponseID ties a booksource record back
>> to a response record. There will be a 1 to many relationship between
>> Responses and BookSources.
>>
>> If you give all four checkboxes the same name:
>> <INPUT type="checkbox" name="buy_book" value="Bookstore">
>> <INPUT type="checkbox" name="buy_book" value="Online">
>> etc.
>> , you can process the inputs as a collection:
>>
>> 'You will need to have assigned a ResponseID prior to this step
>> for each vData in request.form("buy_book")
>> SQL = "insert into BookSources Values (" & ResponseID & _
>> ", '" & vData & "')"
>> con.Execute SQL
>> next
>>
>> HTH,
>> Bob Barrows
>>
>> Simon wrote:

>>> Hey folks,
>>>
>>> I need some of your expertiese again. I am creating a survey form
>>> and need some help gathering the results of a question that has
>>> checkboxes. There are four options for one of the questions for
>>> example
>>>
>>> Where do you normally buy books?
>>> o Bookstore
>>> o Online
>>> o Book club
>>> o Other
>>>
>>> I'm going to store the answers in a DB and I dont want to have have
>>> four fields if only two of them are going to get used. Im trying to
>>> find a GOOD way to collect the results perhaps in an array or comma
>>> seperated list so I could store it in onefield.
>>>
>>> Right now here is what I have
>>>
>>> <%
>>> strBuyBook1 = Trim(Request.Form("buy_book1"))
>>> strBuyBook2 = Trim(Request.Form("buy_book2"))
>>> strBuyBook3 = Trim(Request.Form("buy_book3"))
>>> strBuyBook4 = Trim(Request.Form("buy_book4"))
>>>
>>> Connect to the database
>>> call OpenDB()
>>>
>>> SQL = "INSERT INTO tblAnswers( BuyBook1, BuyBook2, BuyBook3,
>>> BuyBook4)" & _ "VALUES ('" & strBuyBook1 & "', '" &
>>> strBuyBook2 & "', '" &
>>> strBuyBook3 & "', '" & strBuyBook4 & "')"
>>>
>>> con.Execute SQL
>>>
>>> %>
>>>
>>>
>>> Thanks in anticipation
>>> Simon

[Simon]

Im trying really hard not to! Im not pulling any of my questions/options
from a database I just want to save the results in a database.

Could I email you my code?

Thanks again
Simon


"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:ehAwkZ$PDHA.2476@TK2MSFTNGP10.phx.gbl...

> Nah, you're still thinking in spreadsheet terms. There are good reasons

for

> database normalization.
> Simon wrote:

> > I agree I dont really want to store multiple values in a single
> > field, I would like to have two fields in my database. I was
> > thinking along the lines using the split function to create an array
> > and inserting each array item into its own field. To do that I would
> > have to prevent teh user form checking more than two checkboxes but
> > Im not sure how to do that either :-\
> >
> >
> > "Bob Barrows" <reb_01501@yahoo.com> wrote in message
> > news:OICsO%23%23PDHA.3880@tk2msftngp13.phx.gbl...

> >> <rant>
> >> It is a bad idea to store multiple data values in a single field.
> >> This is a database, not a spreadsheet. Someday you are going to need
> >> to look at these values individually: think of the hell you are
> >> going to go through to separate them out. Say you want to know what
> >> percentage of people use a bookstore?
> >> </rant>
> >>
> >> You should use a separate table called BookSources with two columns:
> >> ResponseID and BookSource. ResponseID ties a booksource record back
> >> to a response record. There will be a 1 to many relationship between
> >> Responses and BookSources.
> >>
> >> If you give all four checkboxes the same name:
> >> <INPUT type="checkbox" name="buy_book" value="Bookstore">
> >> <INPUT type="checkbox" name="buy_book" value="Online">
> >> etc.
> >> , you can process the inputs as a collection:
> >>
> >> 'You will need to have assigned a ResponseID prior to this step
> >> for each vData in request.form("buy_book")
> >> SQL = "insert into BookSources Values (" & ResponseID & _
> >> ", '" & vData & "')"
> >> con.Execute SQL
> >> next
> >>
> >> HTH,
> >> Bob Barrows
> >>
> >> Simon wrote:
> >>> Hey folks,
> >>>
> >>> I need some of your expertiese again. I am creating a survey form
> >>> and need some help gathering the results of a question that has
> >>> checkboxes. There are four options for one of the questions for
> >>> example
> >>>
> >>> Where do you normally buy books?
> >>> o Bookstore
> >>> o Online
> >>> o Book club
> >>> o Other
> >>>
> >>> I'm going to store the answers in a DB and I dont want to have have
> >>> four fields if only two of them are going to get used. Im trying to
> >>> find a GOOD way to collect the results perhaps in an array or comma
> >>> seperated list so I could store it in onefield.
> >>>
> >>> Right now here is what I have
> >>>
> >>> <%
> >>> strBuyBook1 = Trim(Request.Form("buy_book1"))
> >>> strBuyBook2 = Trim(Request.Form("buy_book2"))
> >>> strBuyBook3 = Trim(Request.Form("buy_book3"))
> >>> strBuyBook4 = Trim(Request.Form("buy_book4"))
> >>>
> >>> Connect to the database
> >>> call OpenDB()
> >>>
> >>> SQL = "INSERT INTO tblAnswers( BuyBook1, BuyBook2, BuyBook3,
> >>> BuyBook4)" & _ "VALUES ('" & strBuyBook1 & "', '" &
> >>> strBuyBook2 & "', '" &
> >>> strBuyBook3 & "', '" & strBuyBook4 & "')"
> >>>
> >>> con.Execute SQL
> >>>
> >>> %>
> >>>
> >>>
> >>> Thanks in anticipation
> >>> Simon

>
>

[Bob Barrows]

I've already given you a solution. Please read it again and try to
understand it:
Simon wrote:

>>>>
>>>> You should use a separate table called BookSources with two
>>>> columns: ResponseID and BookSource. ResponseID ties a booksource
>>>> record back to a response record. There will be a 1 to many
>>>> relationship between Responses and BookSources.
>>>>
>>>> If you give all four checkboxes the same name:
>>>> <INPUT type="checkbox" name="buy_book" value="Bookstore">
>>>> <INPUT type="checkbox" name="buy_book" value="Online">
>>>> etc.
>>>> , you can process the inputs as a collection:
>>>>
>>>> 'You will need to have assigned a ResponseID prior to this step
>>>> for each vData in request.form("buy_book")
>>>> SQL = "insert into BookSources Values (" & ResponseID & _
>>>> ", '" & vData & "')"
>>>> con.Execute SQL
>>>> next
>>>>

[Andrew Durstewitz]

If you want to follow more of a standard like Bob mentioned then here is
a good article...

[url]http://www.devbuilder.org/asp/dev_article.asp?aspid=13[/url]

Bob, the question sounded more to me like he wanted a simple, quick
solution. That was my answere. Putting multiple returns in the same
feild isn't the best idea but you can break those out into a nice
multi-dimensional array for fast processing.

Whereas, if you had to go loop through a recordset it would take you
longer.
</rant> :oP

Andrew

* * * Sent via DevBuilder [url]http://www.devbuilder.org[/url] * * *
Developer Resources for High End Developers.

[Simon]

Bob,

I have been thinking about what you said here but being somewhat of a novice
when it comes to DB's Im having a hard time putting it together, I have
searched all over (ok so thats probably an exageration) the web looking for
examples on storing checkbox results in a datbase but to no avail.

Do you think you could find it in your heart to provide me with an example?
I know you have always been helpfull to me in the past.

Thanks again
Simon
"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:OICsO%23%23PDHA.3880@tk2msftngp13.phx.gbl...

> <rant>
> It is a bad idea to store multiple data values in a single field. This is

a

> database, not a spreadsheet. Someday you are going to need to look at

these

> values individually: think of the hell you are going to go through to
> separate them out. Say you want to know what percentage of people use a
> bookstore?
> </rant>
>
> You should use a separate table called BookSources with two columns:
> ResponseID and BookSource. ResponseID ties a booksource record back to a
> response record. There will be a 1 to many relationship between Responses
> and BookSources.
>
> If you give all four checkboxes the same name:
> <INPUT type="checkbox" name="buy_book" value="Bookstore">
> <INPUT type="checkbox" name="buy_book" value="Online">
> etc.
> , you can process the inputs as a collection:
>
> 'You will need to have assigned a ResponseID prior to this step
> for each vData in request.form("buy_book")
> SQL = "insert into BookSources Values (" & ResponseID & _
> ", '" & vData & "')"
> con.Execute SQL
> next
>
> HTH,
> Bob Barrows
>
> Simon wrote:

> > Hey folks,
> >
> > I need some of your expertiese again. I am creating a survey form
> > and need some help gathering the results of a question that has
> > checkboxes. There are four options for one of the questions for
> > example
> >
> > Where do you normally buy books?
> > o Bookstore
> > o Online
> > o Book club
> > o Other
> >
> > I'm going to store the answers in a DB and I dont want to have have
> > four fields if only two of them are going to get used. Im trying to
> > find a GOOD way to collect the results perhaps in an array or comma
> > seperated list so I could store it in onefield.
> >
> > Right now here is what I have
> >
> > <%
> > strBuyBook1 = Trim(Request.Form("buy_book1"))
> > strBuyBook2 = Trim(Request.Form("buy_book2"))
> > strBuyBook3 = Trim(Request.Form("buy_book3"))
> > strBuyBook4 = Trim(Request.Form("buy_book4"))
> >
> > Connect to the database
> > call OpenDB()
> >
> > SQL = "INSERT INTO tblAnswers( BuyBook1, BuyBook2, BuyBook3,
> > BuyBook4)" & _ "VALUES ('" & strBuyBook1 & "', '" &
> > strBuyBook2 & "', '" &
> > strBuyBook3 & "', '" & strBuyBook4 & "')"
> >
> > con.Execute SQL
> >
> > %>
> >
> >
> > Thanks in anticipation
> > Simon

>
>

[Bob Barrows]

This will take a while. I am really busy today. Hopefully someone else will
step in here. I'll check again later to see if you still need help.

Bob

Simon wrote:

> Bob,
>
> I have been thinking about what you said here but being somewhat of a
> novice when it comes to DB's Im having a hard time putting it
> together, I have searched all over (ok so thats probably an
> exageration) the web looking for examples on storing checkbox
> results in a datbase but to no avail.
>
> Do you think you could find it in your heart to provide me with an
> example? I know you have always been helpfull to me in the past.
>
> Thanks again
> Simon
> "Bob Barrows" <reb_01501@yahoo.com> wrote in message
> news:OICsO%23%23PDHA.3880@tk2msftngp13.phx.gbl...

>> <rant>
>> It is a bad idea to store multiple data values in a single field.
>> This is a database, not a spreadsheet. Someday you are going to need
>> to look at these values individually: think of the hell you are
>> going to go through to separate them out. Say you want to know what
>> percentage of people use a bookstore?
>> </rant>
>>
>> You should use a separate table called BookSources with two columns:
>> ResponseID and BookSource. ResponseID ties a booksource record back
>> to a response record. There will be a 1 to many relationship between
>> Responses and BookSources.
>>
>> If you give all four checkboxes the same name:
>> <INPUT type="checkbox" name="buy_book" value="Bookstore">
>> <INPUT type="checkbox" name="buy_book" value="Online">
>> etc.
>> , you can process the inputs as a collection:
>>
>> 'You will need to have assigned a ResponseID prior to this step
>> for each vData in request.form("buy_book")
>> SQL = "insert into BookSources Values (" & ResponseID & _
>> ", '" & vData & "')"
>> con.Execute SQL
>> next
>>
>> HTH,
>> Bob Barrows
>>
>> Simon wrote:

>>> Hey folks,
>>>
>>> I need some of your expertiese again. I am creating a survey form
>>> and need some help gathering the results of a question that has
>>> checkboxes. There are four options for one of the questions for
>>> example
>>>
>>> Where do you normally buy books?
>>> o Bookstore
>>> o Online
>>> o Book club
>>> o Other
>>>
>>> I'm going to store the answers in a DB and I dont want to have have
>>> four fields if only two of them are going to get used. Im trying to
>>> find a GOOD way to collect the results perhaps in an array or comma
>>> seperated list so I could store it in onefield.
>>>
>>> Right now here is what I have
>>>
>>> <%
>>> strBuyBook1 = Trim(Request.Form("buy_book1"))
>>> strBuyBook2 = Trim(Request.Form("buy_book2"))
>>> strBuyBook3 = Trim(Request.Form("buy_book3"))
>>> strBuyBook4 = Trim(Request.Form("buy_book4"))
>>>
>>> Connect to the database
>>> call OpenDB()
>>>
>>> SQL = "INSERT INTO tblAnswers( BuyBook1, BuyBook2, BuyBook3,
>>> BuyBook4)" & _ "VALUES ('" & strBuyBook1 & "', '" &
>>> strBuyBook2 & "', '" &
>>> strBuyBook3 & "', '" & strBuyBook4 & "')"
>>>
>>> con.Execute SQL
>>>
>>> %>
>>>
>>>
>>> Thanks in anticipation
>>> Simon

[Simon]

dont supose youhave had a chance to look at this Bob?

Sorry to be a pain.

simon
"Bob Barrows" <reb_01501@yahoo.com> wrote in message
news:exynUg4SDHA.2020@TK2MSFTNGP11.phx.gbl...

> This will take a while. I am really busy today. Hopefully someone else

will

> step in here. I'll check again later to see if you still need help.
>
> Bob
>
> Simon wrote:

> > Bob,
> >
> > I have been thinking about what you said here but being somewhat of a
> > novice when it comes to DB's Im having a hard time putting it
> > together, I have searched all over (ok so thats probably an
> > exageration) the web looking for examples on storing checkbox
> > results in a datbase but to no avail.
> >
> > Do you think you could find it in your heart to provide me with an
> > example? I know you have always been helpfull to me in the past.
> >
> > Thanks again
> > Simon
> > "Bob Barrows" <reb_01501@yahoo.com> wrote in message
> > news:OICsO%23%23PDHA.3880@tk2msftngp13.phx.gbl...

> >> <rant>
> >> It is a bad idea to store multiple data values in a single field.
> >> This is a database, not a spreadsheet. Someday you are going to need
> >> to look at these values individually: think of the hell you are
> >> going to go through to separate them out. Say you want to know what
> >> percentage of people use a bookstore?
> >> </rant>
> >>
> >> You should use a separate table called BookSources with two columns:
> >> ResponseID and BookSource. ResponseID ties a booksource record back
> >> to a response record. There will be a 1 to many relationship between
> >> Responses and BookSources.
> >>
> >> If you give all four checkboxes the same name:
> >> <INPUT type="checkbox" name="buy_book" value="Bookstore">
> >> <INPUT type="checkbox" name="buy_book" value="Online">
> >> etc.
> >> , you can process the inputs as a collection:
> >>
> >> 'You will need to have assigned a ResponseID prior to this step
> >> for each vData in request.form("buy_book")
> >> SQL = "insert into BookSources Values (" & ResponseID & _
> >> ", '" & vData & "')"
> >> con.Execute SQL
> >> next
> >>
> >> HTH,
> >> Bob Barrows
> >>
> >> Simon wrote:
> >>> Hey folks,
> >>>
> >>> I need some of your expertiese again. I am creating a survey form
> >>> and need some help gathering the results of a question that has
> >>> checkboxes. There are four options for one of the questions for
> >>> example
> >>>
> >>> Where do you normally buy books?
> >>> o Bookstore
> >>> o Online
> >>> o Book club
> >>> o Other
> >>>
> >>> I'm going to store the answers in a DB and I dont want to have have
> >>> four fields if only two of them are going to get used. Im trying to
> >>> find a GOOD way to collect the results perhaps in an array or comma
> >>> seperated list so I could store it in onefield.
> >>>
> >>> Right now here is what I have
> >>>
> >>> <%
> >>> strBuyBook1 = Trim(Request.Form("buy_book1"))
> >>> strBuyBook2 = Trim(Request.Form("buy_book2"))
> >>> strBuyBook3 = Trim(Request.Form("buy_book3"))
> >>> strBuyBook4 = Trim(Request.Form("buy_book4"))
> >>>
> >>> Connect to the database
> >>> call OpenDB()
> >>>
> >>> SQL = "INSERT INTO tblAnswers( BuyBook1, BuyBook2, BuyBook3,
> >>> BuyBook4)" & _ "VALUES ('" & strBuyBook1 & "', '" &
> >>> strBuyBook2 & "', '" &
> >>> strBuyBook3 & "', '" & strBuyBook4 & "')"
> >>>
> >>> con.Execute SQL
> >>>
> >>> %>
> >>>
> >>>
> >>> Thanks in anticipation
> >>> Simon

>
>
>