tedtzirimis
asked on
Dynamic questionnaire in ASP.NET with SQL Server Database
I have a SQL table (questions) with two columns - QuestionID and Question where the answer to the question will ALWAYS be yes or no. So it might look like:
QuestionID Question
1 Are you tall?
2 Are you over 50?
I have a second table (answers) to store user responses. It has a few columns - UserID, QuestionID, Answer, Date. So that the composite primary key is UserID/QuestionID/Date. So it might look like:
UserID QuestionID Answer Date
101 1 1 3/1/2010
101 2 0 3/1/2010
I want this vertical table structure to ensure I can always add questions without having to edit the table and add columns as I would in a horizontal structure.
I'm fairly new to ASP.NET 3.5x and struggling with coming up with the best approach to implement a single form that users complete and then have submitted to the database. The user would enter UserID once at top of form (or somewhere on page), select date of the survey only once (again at top of form or somewhere on page) and then answer all questions in a single shot and click submit once to send to database. I've toyed with GridView, FormView, DetailsView, and ListView. ListView seems to be what I want but I'm open to anything.
The SELECT query I've used to generate the look of the form is:
SELECT QuestionID, Question, NULL as UserID, NULL as Answer, NULL as Date FROM questions
Obviously, my INSERT statement would be different since it needs to loop through each question displayed and answer to capture the QuestionID and Answer and then append the Date and UserID to insert.
I'd greatly appreciate if anyone can point me to any code (preferably in VB but I can convert from C#) to get me where I need to be or at a minimum point me to any concepts I'm overlooking or should consider for this design. The form would be dynamic since all questions are always Yes/No, so if I added a new question to the database the display form would automatically add an additional line and it would be included in the loop/Insert. This is high priority for me. Thank you!
QuestionID Question
1 Are you tall?
2 Are you over 50?
I have a second table (answers) to store user responses. It has a few columns - UserID, QuestionID, Answer, Date. So that the composite primary key is UserID/QuestionID/Date. So it might look like:
UserID QuestionID Answer Date
101 1 1 3/1/2010
101 2 0 3/1/2010
I want this vertical table structure to ensure I can always add questions without having to edit the table and add columns as I would in a horizontal structure.
I'm fairly new to ASP.NET 3.5x and struggling with coming up with the best approach to implement a single form that users complete and then have submitted to the database. The user would enter UserID once at top of form (or somewhere on page), select date of the survey only once (again at top of form or somewhere on page) and then answer all questions in a single shot and click submit once to send to database. I've toyed with GridView, FormView, DetailsView, and ListView. ListView seems to be what I want but I'm open to anything.
The SELECT query I've used to generate the look of the form is:
SELECT QuestionID, Question, NULL as UserID, NULL as Answer, NULL as Date FROM questions
Obviously, my INSERT statement would be different since it needs to loop through each question displayed and answer to capture the QuestionID and Answer and then append the Date and UserID to insert.
I'd greatly appreciate if anyone can point me to any code (preferably in VB but I can convert from C#) to get me where I need to be or at a minimum point me to any concepts I'm overlooking or should consider for this design. The form would be dynamic since all questions are always Yes/No, so if I added a new question to the database the display form would automatically add an additional line and it would be included in the loop/Insert. This is high priority for me. Thank you!
ASKER
tickett - Thanks for the tips but might you have or could you point me to an example of this type of gridview that doesn't use the standard edit/update template? I'm more comfortable writing the looping code, though I've never done it for a gridview where I need to capture multiple rows (only selectedrow).
> the composite primary key is UserID/QuestionID/Date
Do you want the same user to answer your questionnaire multiple times? Because only then including the Date in the primary key makes sense.
> SELECT QuestionID, Question, NULL as UserID, NULL as Answer, NULL as Date FROM questions
If "answer" and "date" are always Null, there seems to be no need to include them in the query.
If you use checkboxes, you can insert all answers in 2 statements. You have to give the checkboxes all the same name ("checkboxname") and a value that is theQuestionID.
sSQL= "INSERT INTO [Answer] "
sSQL &= "SELECT " & userid & " AS UserID, QuestionID, 1 AS AnswerID "
sSQL &= "FROM [Question] WHERE QuestionID IN (0,0" & Request.Form("checboxname"
sSQL= "INSERT INTO [Answer] "
sSQL &= "SELECT " & userid & " AS UserID, QuestionID, 0 AS AnswerID "
sSQL &= "FROM [Question] WHERE QuestionID NOT IN (0,0" & Request.Form("checboxname"
That skips inserting the Date, but I'd probably solve that by giving that column default value getdate().
Do you want the same user to answer your questionnaire multiple times? Because only then including the Date in the primary key makes sense.
> SELECT QuestionID, Question, NULL as UserID, NULL as Answer, NULL as Date FROM questions
If "answer" and "date" are always Null, there seems to be no need to include them in the query.
If you use checkboxes, you can insert all answers in 2 statements. You have to give the checkboxes all the same name ("checkboxname") and a value that is theQuestionID.
sSQL= "INSERT INTO [Answer] "
sSQL &= "SELECT " & userid & " AS UserID, QuestionID, 1 AS AnswerID "
sSQL &= "FROM [Question] WHERE QuestionID IN (0,0" & Request.Form("checboxname"
sSQL= "INSERT INTO [Answer] "
sSQL &= "SELECT " & userid & " AS UserID, QuestionID, 0 AS AnswerID "
sSQL &= "FROM [Question] WHERE QuestionID NOT IN (0,0" & Request.Form("checboxname"
That skips inserting the Date, but I'd probably solve that by giving that column default value getdate().
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I utilized the GridView approach per tickett's suggestion and added the necessary code behind - declaring the parameters from the GridView, establishing a SQL connection and inserting using the following basic loop:
For Each row As GridViewRow In GridView2.Rows
If row.RowType = DataControlRowType.DataRow
Dim qidv As String = row.DataItemIndex.ToString
qid.Value = qidv
Dim answerRB As RadioButtonList = TryCast(row.FindControl("a
answer.Value = answerRB.SelectedItem.Text
cmd.ExecuteNonQuery()
End If
Next
For Each row As GridViewRow In GridView2.Rows
If row.RowType = DataControlRowType.DataRow
Dim qidv As String = row.DataItemIndex.ToString
qid.Value = qidv
Dim answerRB As RadioButtonList = TryCast(row.FindControl("a
answer.Value = answerRB.SelectedItem.Text
cmd.ExecuteNonQuery()
End If
Next
For your submit button simply loop through the rows in the gridview building the insert statements on the fly. Pop a few requiredfield/regularexpre
L