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.Fo rm("buy_book1") )
strBuyBook2 = Trim(Request.Fo rm("buy_book2") )
strBuyBook3 = Trim(Request.Fo rm("buy_book3") )
strBuyBook4 = Trim(Request.Fo rm("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 http://www.devbuilder.org * * *
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" <re*******@yaho o.com> wrote in message
news:OI******** **********@tk2m sftngp13.phx.gb l...

<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="Bookstor e">
<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("b uy_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.Fo rm("buy_book1") )
strBuyBook2 = Trim(Request.Fo rm("buy_book2") )
strBuyBook3 = Trim(Request.Fo rm("buy_book3") )
strBuyBook4 = Trim(Request.Fo rm("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" <re*******@yaho o.com> wrote in message
news:eh******** ******@TK2MSFTN GP10.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" <re*******@yaho o.com> wrote in message
news:OI******** **********@tk2m sftngp13.phx.gb l...

<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="Bookstor e">
<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("b uy_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.Fo rm("buy_book1") )
strBuyBook2 = Trim(Request.Fo rm("buy_book2") )
strBuyBook3 = Trim(Request.Fo rm("buy_book3") )
strBuyBook4 = Trim(Request.Fo rm("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="Bookstor e">
<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("b uy_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...

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

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 http://www.devbuilder.org * * *
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" <re*******@yaho o.com> wrote in message
news:OI******** **********@tk2m sftngp13.phx.gb l...

<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="Bookstor e">
<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("b uy_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.Fo rm("buy_book1") )
strBuyBook2 = Trim(Request.Fo rm("buy_book2") )
strBuyBook3 = Trim(Request.Fo rm("buy_book3") )
strBuyBook4 = Trim(Request.Fo rm("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" <re*******@yaho o.com> wrote in message
news:OI******** **********@tk2m sftngp13.phx.gb l...

<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="Bookstor e">
<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("b uy_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.Fo rm("buy_book1") )
strBuyBook2 = Trim(Request.Fo rm("buy_book2") )
strBuyBook3 = Trim(Request.Fo rm("buy_book3") )
strBuyBook4 = Trim(Request.Fo rm("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" <re*******@yaho o.com> wrote in message
news:ex******** ******@TK2MSFTN GP11.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" <re*******@yaho o.com> wrote in message
news:OI******** **********@tk2m sftngp13.phx.gb l...

<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="Bookstor e">
<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("b uy_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.Fo rm("buy_book1") )
strBuyBook2 = Trim(Request.Fo rm("buy_book2") )
strBuyBook3 = Trim(Request.Fo rm("buy_book3") )
strBuyBook4 = Trim(Request.Fo rm("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