good tutorial for asp......

[SeGamysa]

well basically what i need is to count how many times each item is in the database.
i.e.
a has been checked 20 times
b has been checked 10 times
c has been checked 4 times

but my page has over 20 questions with an avearge of 7 choices per question.

I don't want to keep opening a new record set for each item in each question.
any good links that would help keep my code under 100000 (give or take 50000) lines would be apprieated or any sample code.
Just lazy.
thank you in advance.


:rolleyes: :eek:

[SeGamysa]

basically I have a request to make a survey.
The survey consisits of 16 questions with approximatly 7 radio or checkboxes each question.
They also request that they can access a web page and display a tally of how many times question 1a 1b 1c etc, etc, have been selected.
I have completed the said requests but my current code looks somthing like this
Set rs = server.CreateObject("ADODB.RecordSet")

Set rs1a = server.CreateObject("ADODB.RecordSet")
Set rs1b = server.CreateObject("ADODB.RecordSet")
Set rs1c = server.CreateObject("ADODB.RecordSet")
.....
set rs1a.ActiveConnection = DBconn
set rs1b.ActiveConnection = DBconn
set rs1c.ActiveConnection = DBconn
.....
sqlStr1a = "Select * from questionaire where question1 = 'More than once per day'"
sqlStr1b = "Select * from questionaire where question1 = 'NEVER'"
sqlStr1c = "Select * from questionaire where question1 = 'Once per day'"
rs1a.Open sqlStr1a
rs1b.Open sqlStr1b
rs1c.Open sqlStr1c
....
Do Until rs1a Is Nothing
Do While Not rs1a.EOF
dim q1acount
q1acount = q1acount + 1
RS1a.MoveNext
Loop
Set RS1a = RS1a.NextRecordset
Loop
Response.Write ("More than once per day Survey says: " & q1acount & " Never:
.........
etc...
cut and paste skills need some work.
But essentially other then writing all this would there be another way that I may elimanate all these lines of code?

[DaiWelsh]

Yes, you could do something like (untested)

For QuestionNumber = 1 to 16
sql = "SELECT question" & QuestionNumber & " AS Answer,count(*) AS NumAnswers FROM questionaire GROUP BY " & QuestionNumber
rs.Open sql
Response.Write ("Question " & QuestionNumber & "<br>")
While Not rs.EOF
Response.Write (rs("Answer") & " : " & rs("NumAnswers")& "<br>"
Wend
Next

Let the database do the totalling for you and put it in a loop so that you dont have to repeat te code 16 times (assuming your fields are called question1,question2,.....question16.

Incidentally if it is not too late to change your database structure, you might be better off storing the possible answers in their own table and probably also having one row per answer in your questionnaire table rather than one row per questionnaire. This will allow you to change the questions, ansers, number of questions etc. much easier later if necessary.

e.g.

Questions

QID,QText
1,'My first question'
2,'My second question'
.........

Answers

AID,AQuestion,ASequence,AText
1,1,1,'First answer for first question'
2,1,2,'Second answer for first question'
3,1,3,'Third answer for first question'
4,2,1,'First answer for second question'
.......

Questionnaire

User,Question,Answer
1,1,3
1,2,1
1,3,2
2,1,4
.......

The last table means user 1 (I assume you have some way odf identifying which user gave which ansers) gave answer 3 for question 1, answer 1 for question 2 etc.

If this is just confusing you ignore it, at the end of the day if you get it working how you want that is priority #1 ;)

HTH,

Dai

[SeGamysa]

lol thanks
and just to add I did name all the fields Question1 Question2 andso on...
lol
I'm just able to follow my code better that way.
any wyas thanx i'll give those ideas of urs a try.
ciao