One Form - Multiple Inserts - Help Please

[Chamark]

I have a 10 question questionnaire with multiple 5 radio button responses to each question in one form that I need to insert each question as a separate row in a SQL 2000 database. Any help on how to do this is greatly appreciated. Thanks

[bubbisthedog]

You need to be more specific. It is unknown whether you're

a) Wanting to know the SQL syntax for inserting rows into a table,
b) Wanting to know the C#, VB, etc. (i.e. the logic) for inserting rows into a table,
c) Wanting information on how to build your table/DB structure...

Let us know how far you've gotten, and where you've hit a roadblock; perhaps then you can be assisted.

[Chamark]

Thanks for your response.
I am attempting to create a Web form with 10 questions on it that can be answered by selecting one radio button out five. Example:

1. The coach that was assigned to you was on time.

Strongly agree
Agree
Neutral
Disgree
Strongly disagree

All the questions need to appear on the form. I would like to be able to submit the form with one submit button, but have each question be a separate row in the database (SQL2000). This would provide me with the ability to report as:

Question SA A N D SD
1 1 2 4 2 1
2 2 1 3 1 2

I need help in how to set up the database and how to do a multi-row insert from the Web page. I obviously am stretching my experience. May be some direction on using an Array and doing the inserts using a loop from the array?

Thanks again

[bubbisthedog]

First of all, a normalized database structure would be as follows:

ANSWERS

A_ID | AT_DESC
--------------------------------
1 | Strongly agree
2 | Agree
3 | Neutral
4 | Disagree
5 | Strongly disagree

QUESTIONS

Q_ID | Q_DESC
--------------------------------
1 | Question 1
2 | Question 2
. . .
10 | Question 10

QUESTION_ANSWERS

QA_ID | A_ID | Q_ID
--------------------------------
1 | 4 | 8
2 | 2 | 6
. . .

You would then SQL query the results in QUESTION_ANSWERS for reporting. When you've determined how to store your data (perhaps using the approach above, or something similar to it), I'll tell you how to insert it using SQL. Then you can let me know what information you'd like to retrieve after storing it, and I'll help you create the SQL to do that as well.

[Chamark]

I've taken your advice in creating three tables (Questions, Answers, results) with results having the primkey and the two other foreign keys. I still am confused on how to tie this together in Web page with one insertion, but multiple rows for each question. I do appreciate your help and am continuing to struggle through this.


First of all, a normalized database structure would be as follows:

ANSWERS

A_ID | AT_DESC
--------------------------------
1 | Strongly agree
2 | Agree
3 | Neutral
4 | Disagree
5 | Strongly disagree

QUESTIONS

Q_ID | Q_DESC
--------------------------------
1 | Question 1
2 | Question 2
. . .
10 | Question 10

QUESTION_ANSWERS

QA_ID | A_ID | Q_ID
--------------------------------
1 | 4 | 8
2 | 2 | 6
. . .

You would then SQL query the results in QUESTION_ANSWERS for reporting. When you've determined how to store your data (perhaps using the approach above, or something similar to it), I'll tell you how to insert it using SQL. Then you can let me know what information you'd like to retrieve after storing it, and I'll help you create the SQL to do that as well.

[bubbisthedog]

Post the HTML for you radio buttons, and we can move forward from there. What you will essentially do is get the value of the radio button selected for each question, and then use an SQL 'INSERT' statement to enter the results into QUESTIONS_ANSWERS for each question. Your QA_ID needs to auto increment.

Process:

Get A_ID for question 1, INSERT Q_ID and A_ID into QUESTIONS_ANSWERS;
Get next A_ID for next question, INSERT Q_ID and A_ID into QUESTIONS_ANSWERS;
Get next A_ID for next question, INSERT Q_ID and A_ID into QUESTIONS_ANSWERS...

That's literally all there is to it.

[Chamark]

OK Thanks. I'll work out the rest. I appreciate your time and patience.

[bubbisthedog]

You're welcome. I'll keep my eyes open in case you have any questions along the way.