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:
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!