Solved

An output of a SQL Statement in ASP.

Posted on 2004-10-06
24
298 Views
Last Modified: 2008-02-01
Additional to my code below, I'm trying to have this output, but i'm receiving error on the writeln issue.
Are you able to provide me a help to have this output to the code below.


----------------------------------------   Analyse Report:
Question 3: . How would you describe yourself when it comes to using the Internet?  

--------------------------------------------------------
Response         |   Num Responses  |  Raw %   |
--------------------------------------------------------
Beginner          |          3                 |   60%        |
Intermediate    |          1                 |   20%        |
Advanced         |          1                 |  20%         |
------------------------------------------------------
Total Responses: 5  
Note: FYI. 60% is coming from 3/5.



sub OutputWriteSurvey()
dim adoRS, strSQL
    set adoRS = CreateObject("ADODB.Recordset")
   
    strSQL = "SELECT     QuestionNo, AnswerID, COUNT(AnswerID) AS Expr1 " &_
                     "FROM         SUR_ResponsesAnswers SUR_ResponsesAnswers " &_
                      "WHERE     (QuestionNo = 3) " &_
                      "GROUP BY questionno, answerid " &_
                       "ORDER BY questionno, answerid "
     
    adoRS.CursorLocation = 3 'adUseClient
    adoRS.Open strSQL, ConnectString
      
      strSQL = "SELECT     QuestionNo, AnswerID, COUNT(AnswerID) AS Expr1 " &_
                     "FROM         SUR_ResponsesAnswers SUR_ResponsesAnswers " &_
                      "Having (QuestionNo = 3) " &_
                      "GROUP BY questionno, answerid " &_
                       "ORDER BY questionno, answerid "
     
' The code should be here using writeln table i think.  

end sub
%>
0
Comment
Question by:elieazzi
  • 14
  • 10
24 Comments
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12240066
You want to use the second strSQL instead of the first rather than in addition to the first. So let's start with this:

sub OutputWriteSurvey()
dim adoRS, strSQL
    set adoRS = CreateObject("ADODB.Recordset")
   
      strSQL = "SELECT     QuestionNo, AnswerID, COUNT(AnswerID) AS Expr1 " &_
                     "FROM         SUR_ResponsesAnswers SUR_ResponsesAnswers " &_
                      "Having (QuestionNo = 3) " &_
                      "GROUP BY questionno, answerid " &_
                       "ORDER BY questionno, answerid "
     
    adoRS.CursorLocation = 3 'adUseClient
    adoRS.Open strSQL, ConnectString
     
response.write("<Table>" & VbCrLf)
do while not  adoRS    
    Response.write("<tr><td>" & adoRS("QuestionNo") & "</td><td>" & adoRS("AnswerID") & "</td><td>" & adoRS("Expr1") & "</td></tr>"  & VbCrLf)
    adoRS.MoveNext
Loop
response.write("</Table>)
end sub
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12240076
I know that this isn't exactly what you want, but let's start with this to make sure that the data looks correct.

FtB
0
 

Author Comment

by:elieazzi
ID: 12240164
Ftb, I've copied this code, as a result the page is not displaying... There is error.

sub OutputWriteSurvey()
dim adoRS, strSQL
    set adoRS = CreateObject("ADODB.Recordset")
   
      strSQL = "SELECT     QuestionNo, AnswerID, COUNT(AnswerID) AS Expr1 " &_
                     "FROM         SUR_ResponsesAnswers SUR_ResponsesAnswers " &_
                      "Having (QuestionNo = 3) " &_
                      "GROUP BY questionno, answerid " &_
                       "ORDER BY questionno, answerid "
     
    adoRS.CursorLocation = 3 'adUseClient
    adoRS.Open strSQL, ConnectString
     
response.write("<Table>" & VbCrLf)
do while not  adoRS    
    Response.write("<tr><td>" & adoRS("QuestionNo") & "</td><td>" & adoRS("AnswerID") & "</td><td>" & adoRS("Expr1") & "</td></tr>"  & VbCrLf)
    adoRS.MoveNext
Loop
response.write("</Table>")
end sub                                 

0
 

Author Comment

by:elieazzi
ID: 12240198
I'm sorry, here is the error;


Microsoft OLE DB Provider for SQL Server error '80040e14'

Incorrect syntax near the keyword 'GROUP'.

/en/survey/survey2.asp, line 146


    adoRS.Open strSQL, ConnectString

What do you think?
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12240261
I am sorry, let's try:

   strSQL = "SELECT QuestionNo, AnswerID, COUNT(AnswerID) AS Expr1 " &_
                     "FROM  SUR_ResponsesAnswers SUR_ResponsesAnswers " &_
                     "GROUP BY questionno, answerid " &_
                     "Having (QuestionNo = 3) " &_
                     "ORDER BY questionno, answerid "
0
 

Author Comment

by:elieazzi
ID: 12240320
HEre what i'm having:

Microsoft VBScript runtime error '800a000d'
Type mismatch

/en/survey/survey2.asp, line 150
 

FYI This is on line 150:
do while not  adoRS    

This is the full code:


sub OutputWriteSurvey()
dim adoRS, strSQL
    set adoRS = CreateObject("ADODB.Recordset")
   
                       strSQL = "SELECT QuestionNo, AnswerID, COUNT(AnswerID) AS Expr1 " &_
                     "FROM  SUR_ResponsesAnswers SUR_ResponsesAnswers " &_
                     "GROUP BY questionno, answerid " &_
                     "Having (QuestionNo = 3) " &_
                     "ORDER BY questionno, answerid "

     
    adoRS.CursorLocation = 3 'adUseClient
    adoRS.Open strSQL, ConnectString
     
response.write("<Table>" & VbCrLf)
do while not  adoRS    
    Response.write("<tr><td>" & adoRS("QuestionNo") & "</td><td>" & adoRS("AnswerID") & "</td><td>" & adoRS("Expr1") & "</td></tr>"  & VbCrLf)
    adoRS.MoveNext
Loop
response.write("</Table>")
end sub                                 

%>
............................................................................................
..................................................................................................................
....................................................................................................................................
.............................................................................................................................................
For your information this is full code i have... So my error in the part above but this is only if you want see something.
The first part it's only a view of my table...
.....................................................................................................................
.................................................................................................................................
...............................................................................................................................................
<%@ Language=VBScript %>
<%Option Explicit%>
<%
dim iTotalUsers 'used to store total number of users in the system
%>

<!--#include virtual="/globalincludes/database.inc"-->
<!--#include virtual="/globalincludes/global.routines.inc"-->

<HTML><HEAD>
<title>Survey Results</title>
<link href="/admin/admin.css" type="text/css" rel="stylesheet">
</HEAD>

<BODY>
<table  cellspacing=1 cellpadding=1 border=0 width="760px">
<tr><td>
    <p>
    <% call WriteSurvey() %>
      </p>
      
      <p>
    <% call OutputWriteSurvey() %>
      </p>

</td>
</tr>
</table>
</BODY>
</HTML>

<%
sub WriteSurvey()

    dim iCurrentRec
    dim adoRS, strSQL
    set adoRS = CreateObject("ADODB.Recordset")
   
    strSQL = "SELECT * FROM SUR_ResponsesAnswers "

    adoRS.CursorLocation = 3 'adUseClient
    adoRS.Open strSQL, ConnectString
   
    if not (adoRS.EOF or adoRS.bof) then
        'get total number of records for use in the section header
        iTotalUsers = adoRS.RecordCount
    else
        iTotalUsers = 0
    end if
     
    if iTotalUsers > 0 then
   
        writeln "<TABLE border=0 cellpadding=0 cellspacing=0 style=""table-layout: fixed;"" >"
        writeln "      <TR height=1px>"
        writeln "            <TD align=center style=""width: 90px;"" >&nbsp;</TD>"
        writeln "            <TD align=center style=""width: 90px;"" >&nbsp;</TD>"
        writeln "            <TD align=center style=""width: 120px;"" >&nbsp;</TD>"
        writeln "            <TD align=center style=""width: 120px;"" >&nbsp;</TD>"
        writeln "            <TD align=center style=""width: 300px;"" >&nbsp;</TD>"
        writeln "      </TR>"
        writeln "<TR>"
       
        writeln "            <TD class=""BorderMeBlack"" align=center  > ResponseID</TD>"
        writeln "            <TD class=""BorderMeBlack"" align=center  > Question No</TD>"
        writeln "            <TD class=""BorderMeBlack"" align=center  > Question Part No </TD>"
        writeln "            <TD class=""BorderMeBlack"" align=center  > Answer ID </TD>"
        writeln "            <TD class=""BorderMeBlack"" align=center  > Comments </TD>"
        writeln "      </TR>"
   
        iCurrentRec = 1
        while (not (adoRS.EOF or adoRS.BOF)) AND iCurrentRec <= 100
   
                  writeln "            <TD class=""BorderMeBlack"" NOWRAP>" & HTMLEncodeMe(adoRS("ResponseID")) & "</TD>"
            writeln "            <TD class=""BorderMeBlack"" NOWRAP >" & HTMLEncodeMe(replace(adoRS("QuestionNo"),"<br>"," ")) & "</TD>"
            writeln "            <TD class=""BorderMeBlack"" NOWRAP>" & HTMLEncodeMe(adoRS("QuestionPartNo")) & "</TD>"
            writeln "            <TD class=""BorderMeBlack"" NOWRAP >" & HTMLEncodeMe(replace(adoRS("AnswerID"),"<br>"," ")) & "</TD>"
                writeln "            <TD class=""BorderMeBlack"" NOWRAP>" & HTMLEncodeMe(adoRS("Comment")) & "</TD>"

            writeln "      </TR>"
            writeln "      <TR id=""Row" & adoRS("ResponseID") & """ style=""display: none;"" >"
            writeln "            <TD class=""BorderMeBlackLBR"" align=center  >&nbsp;</TD>"
            writeln "            <TD class=""BorderMeBlackLBR"" align=center  >&nbsp;</TD>"
            writeln "            <TD class=""BorderMeBlackLBR"" >&nbsp;</TD>"
            writeln "            <TD class=""BorderMeBlack""  colspan=""4"" > " & replace(HTMLEncodeMe(adoRS("ResponseID")),vbcrlf, "<br>") & "</TD>"

            writeln "      </TR>"

            iCurrentRec = iCurrentRec + 1
            adoRS.MoveNext
        wend
           
        writeln "            <TD class=""BorderMeBlack"" align=""center"" colspan=5>&nbsp;</TD>"
        writeln "</TABLE>"
   
   end if
   writeln "</div>"
   
   if adoRS.State = 1 then
      adoRS.Close
   end if
   set adoRS = nothing

end sub

        
sub OutputWriteSurvey()
dim adoRS, strSQL
    set adoRS = CreateObject("ADODB.Recordset")
   
                       strSQL = "SELECT QuestionNo, AnswerID, COUNT(AnswerID) AS Expr1 " &_
                     "FROM  SUR_ResponsesAnswers SUR_ResponsesAnswers " &_
                     "GROUP BY questionno, answerid " &_
                     "Having (QuestionNo = 3) " &_
                     "ORDER BY questionno, answerid "

     
    adoRS.CursorLocation = 3 'adUseClient
    adoRS.Open strSQL, ConnectString
     
response.write("<Table>" & VbCrLf)
do while not  adoRS    
    Response.write("<tr><td>" & adoRS("QuestionNo") & "</td><td>" & adoRS("AnswerID") & "</td><td>" & adoRS("Expr1") & "</td></tr>"  & VbCrLf)
    adoRS.MoveNext
Loop
response.write("</Table>")
end sub                                 

%>

0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12240339
Can you tell me exactly what line 150 is?

FtB
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12240352
Oops, I you gave that to me. Here is the fix:

do while not  adoRS.eof    
    Response.write("<tr><td>" & adoRS("QuestionNo") & "</td><td>" & adoRS("AnswerID") & "</td><td>" & adoRS("Expr1") & "</td></tr>"  & VbCrLf)
    adoRS.MoveNext
Loop
response.write("</Table>")
end sub          
0
 

Author Comment

by:elieazzi
ID: 12240355
FYI This is on line 150:
do while not  adoRS    

Thank you FtB.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12240360
I omitted the .eof on the adoRS part for the do stament, i.e.:

do while not  adoRS

is wrong!

This is correct:

do while not  adoRS.eof    

FtB
0
 

Author Comment

by:elieazzi
ID: 12240393
VERRY Good Professor, I'm having the same results at the SQL Script.

3 1 3
3 2 1
3 3 1


Now how i'm able to put it in the asp table ;) thank you.
Elie.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12240512
Okay, this is promising!

We are still not done, but here is the next step:

Response.write("<table>" & VBCrLf)
ResponseWrite("<tr><th>Response</th><th>Num Responses</th><th>Raw %</th></tr>" & VBCrLf)
do while not  adoRS.eof
    select case adoRS("QuestionNo")
            case 1
                  strLevel = "Beginner"
            case 2
                  strLevel = "Intermediate"
            case 3
                  strLevel = "Advanced"
           case else
                  strLevel = "N/A"
      end select
      Response.write("<tr><td>" & strLevel & "</td><td>" & Expr1 & "</td><td>--</td></tr>" & VbCrLf)
      adoRS.MoveNext
loop
response.write("</table>")
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:elieazzi
ID: 12240718
Hi Ftb,

Microsoft VBScript runtime error '800a01f4'
Variable is undefined: 'ResponseWrite'

/en/survey/survey2.asp, line 124
This is line: 124
ResponseWrite("<tr><th>Response</th><th>Num Responses</th><th>Raw %</th></tr>" & VBCrLf)

This is the entire sub.

sub OutputWriteSurvey()
dim adoRS, strSQL
    set adoRS = CreateObject("ADODB.Recordset")
   
                       strSQL = "SELECT QuestionNo, AnswerID, COUNT(AnswerID) AS Expr1 " &_
                     "FROM  SUR_ResponsesAnswers SUR_ResponsesAnswers " &_
                     "GROUP BY questionno, answerid " &_
                     "Having (QuestionNo = 3) " &_
                     "ORDER BY questionno, answerid "

     
    adoRS.CursorLocation = 3 'adUseClient
    adoRS.Open strSQL, ConnectString
     
      
      
      
Response.write("<table>" & VBCrLf)
ResponseWrite("<tr><th>Response</th><th>Num Responses</th><th>Raw %</th></tr>" & VBCrLf)
do while not  adoRS.eof
    select case adoRS("QuestionNo")
          case 1
               strLevel = "Beginner"
          case 2
               strLevel = "Intermediate"
          case 3
               strLevel = "Advanced"
          case else
               strLevel = "N/A"
     end select
     Response.write("<tr><td>" & strLevel & "</td><td>" & Expr1 & "</td><td>--</td></tr>" & VbCrLf)
     adoRS.MoveNext
loop
response.write("</table>")

end sub          

Thank you bro,
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12240759
Sorry again (I can't test your code here since I don't have access to the database)

This line is wrong:

ResponseWrite("<tr><th>Response</th><th>Num Responses</th><th>Raw %</th></tr>" & VBCrLf)


It should be:

Response.Write("<tr><th>Response</th><th>Num Responses</th><th>Raw %</th></tr>" & VBCrLf)

FtB
0
 

Author Comment

by:elieazzi
ID: 12240810
No problem, i'll test it...


Microsoft VBScript runtime error '800a01f4'
Variable is undefined: 'strLevel'

/en/survey/survey2.asp, line 129
Response Num Responses Raw %

This is on line 129:
       strLevel = "Advanced"

This is the entire code:

sub OutputWriteSurvey()
dim adoRS, strSQL
    set adoRS = CreateObject("ADODB.Recordset")
   
                       strSQL = "SELECT QuestionNo, AnswerID, COUNT(AnswerID) AS Expr1 " &_
                     "FROM  SUR_ResponsesAnswers SUR_ResponsesAnswers " &_
                     "GROUP BY questionno, answerid " &_
                     "Having (QuestionNo = 3) " &_
                     "ORDER BY questionno, answerid "

     
    adoRS.CursorLocation = 3 'adUseClient
    adoRS.Open strSQL, ConnectString
   
Response.write("<table>" & VBCrLf)
Response.Write("<tr><th>Response</th><th>Num Responses</th><th>Raw %</th></tr>" & VBCrLf)
do while not  adoRS.eof
    select case adoRS("QuestionNo")
          case 1
               strLevel = "Beginner"
          case 2
               strLevel = "Intermediate"
          case 3
               strLevel = "Advanced"
          case else
               strLevel = "N/A"
     end select
     Response.write("<tr><td>" & strLevel & "</td><td>" & Expr1 & "</td><td>--</td></tr>" & VbCrLf)
     adoRS.MoveNext
loop
response.write("</table>")

end sub          
 

0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12240827
Add it to your dim statement:

dim adoRS, strSQL, strLevel


FtB
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12240903
If we are lucky, this should be the whole thing....

      dim adoRS, strSQL, adoRSTotals, strLevel, intQuestions, numPercent
    set adoRS = CreateObject("ADODB.Recordset")
      strSQL = "SELECT QuestionNo, AnswerID, COUNT(AnswerID) AS Expr1 " &_
             "FROM  SUR_ResponsesAnswers  " &_
             "GROUP BY questionno, answerid " &_
             "Having (QuestionNo = 3) " &_
             "ORDER BY questionno, answerid "
      adoRS.CursorLocation = 3 'adUseClient
      adoRS.Open strSQL, ConnectString

    set adoRSTotals = CreateObject("ADODB.Recordset")
    strSQL = "SELECT COUNT(AnswerID) AS intQuestions FROM  SUR_ResponsesAnswers WHERE QuestionNo = 3"
      adoRSTotals.CursorLocation = 3 'adUseClient
      adoRSTotals.Open strSQL, ConnectString
      if not adoRSTotals.eof then
            intQuestions = adoRSTotals("intQuestions")
      else
            intQuestions = 0
      end if
 
      Response.write("<table>" & VBCrLf)
      Response.Write("<tr><th>Response</th><th>Num Responses</th><th>Raw %</th></tr>" & VBCrLf)
      do while not  adoRS.eof
            select case adoRS("QuestionNo")
                  case 1
                        strLevel = "Beginner"
                  case 2
                        strLevel = "Intermediate"
                  case 3
                        strLevel = "Advanced"
                   case else
                        strLevel = "N/A"
            end select
            if intQuestions >0 then
                  numPercent = 100*(Expr1/intQuestions)
            else
                  numPercent = 0
            end if

            Response.write("<tr><td>" & strLevel & "</td><td>" & Expr1 & "</td><td>" & numPercent &"</td></tr>" & VbCrLf)
            adoRS.MoveNext
      loop
      response.write("</table>")
      
      adoRS.Close
      set adoRS = Nothing
      adoRSTotals.Close
      set adoRSTotals = Nothing
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12241198
If you don't get the output for beginner, intermediate, and advanced, then we may need to add quotation marks around the select case parameters like this:

          select case adoRS("QuestionNo")
               case "1"
                    strLevel = "Beginner"
               case "2"
                    strLevel = "Intermediate"
               case "3"
                    strLevel = "Advanced"
                case else
                    strLevel = "N/A"
          end select
0
 

Author Comment

by:elieazzi
ID: 12241467
Hello Fritz,

I'm receiving this one as error:  (BUT I FIXED see below please)
----------------------------------
Microsoft VBScript runtime error '800a01f4'
Variable is undefined: 'Expr1'

/en/survey/survey2.asp, line 142
Response Num Responses Raw %
-----------------------------------

**************************
Which there is this on line 142.
  numPercent = 100*(Expr1/intQuestions)
********************************
For your information:
---------------------------
To let it run i've add the EXPR1 to DIM.  So it's working but i'm getting this as an OUTPUT, please advice.
                                 ============

================
THIS IS THE OUTPUT I'm having.
==================

Response Num Responses Raw %
Advanced  0
Advanced  0
Advanced  0


=========================
And this is the entire code I have.
==========================

sub OutputWriteSurvey()

dim adoRS, strSQL, adoRSTotals, strLevel, intQuestions, numPercent, Expr1
    set adoRS = CreateObject("ADODB.Recordset")
     strSQL = "SELECT QuestionNo, AnswerID, COUNT(AnswerID) AS Expr1 " &_
           "FROM  SUR_ResponsesAnswers  " &_
           "GROUP BY questionno, answerid " &_
           "Having (QuestionNo = 3) " &_
           "ORDER BY questionno, answerid "
     adoRS.CursorLocation = 3 'adUseClient
     adoRS.Open strSQL, ConnectString

    set adoRSTotals = CreateObject("ADODB.Recordset")
    strSQL = "SELECT COUNT(AnswerID) AS intQuestions FROM  SUR_ResponsesAnswers WHERE QuestionNo = 3"
     adoRSTotals.CursorLocation = 3 'adUseClient
     adoRSTotals.Open strSQL, ConnectString
     if not adoRSTotals.eof then
          intQuestions = adoRSTotals("intQuestions")
     else
          intQuestions = 0
     end if
 
     Response.write("<table>" & VBCrLf)
     Response.Write("<tr><th>Response</th><th>Num Responses</th><th>Raw %</th></tr>" & VBCrLf)
     do while not  adoRS.eof
          select case adoRS("QuestionNo")
               case 1
                    strLevel = "Beginner"
               case 2
                    strLevel = "Intermediate"
               case 3
                    strLevel = "Advanced"
                case else
                    strLevel = "N/A"
          end select
          if intQuestions >0 then
               numPercent = 100*(Expr1/intQuestions)
          else
               numPercent = 0
          end if

          Response.write("<tr><td>" & strLevel & "</td><td>" & Expr1 & "</td><td>" & numPercent &"</td></tr>" & VbCrLf)
          adoRS.MoveNext
     loop
     response.write("</table>")
     
     adoRS.Close
     set adoRS = Nothing
     adoRSTotals.Close
     set adoRSTotals = Nothing
end sub          
%>
0
 
LVL 46

Accepted Solution

by:
fritz_the_blank earned 500 total points
ID: 12241641
You really should just shoot me for making so many mistakes. How about this:

<%
sub OutputWriteSurvey()

dim adoRS, strSQL, adoRSTotals, strLevel, intQuestions, numPercent
    set adoRS = CreateObject("ADODB.Recordset")
     strSQL = "SELECT QuestionNo, AnswerID, COUNT(AnswerID) AS Expr1 " &_
           "FROM  SUR_ResponsesAnswers  " &_
           "GROUP BY questionno, answerid " &_
           "Having (QuestionNo = 3) " &_
           "ORDER BY questionno, answerid "
     adoRS.CursorLocation = 3 'adUseClient
     adoRS.Open strSQL, ConnectString

    set adoRSTotals = CreateObject("ADODB.Recordset")
    strSQL = "SELECT COUNT(AnswerID) AS intQuestions FROM  SUR_ResponsesAnswers WHERE QuestionNo = 3"
     adoRSTotals.CursorLocation = 3 'adUseClient
     adoRSTotals.Open strSQL, ConnectString
     if not adoRSTotals.eof then
          intQuestions = adoRSTotals("intQuestions")
     else
          intQuestions = 0
     end if
 
     Response.write("<table>" & VBCrLf)
     Response.Write("<tr><th>Response</th><th>Num Responses</th><th>Raw %</th></tr>" & VBCrLf)
     do while not  adoRS.eof
          select case adoRS("AnswerID")
               case 1
                    strLevel = "Beginner"
               case 2
                    strLevel = "Intermediate"
               case 3
                    strLevel = "Advanced"
                case else
                    strLevel = "N/A"
          end select
          if intQuestions >0 then
               numPercent = 100*(adoRS("Expr1")/intQuestions)
          else
               numPercent = 0
          end if

          Response.write("<tr><td>" & strLevel & "</td><td>" & adoRS("Expr1") & "</td><td>" & numPercent &"</td></tr>" & VbCrLf)
          adoRS.MoveNext
     loop
     response.write("</table>")
     
     adoRS.Close
     set adoRS = Nothing
     adoRSTotals.Close
     set adoRSTotals = Nothing
end sub          
%>
0
 

Author Comment

by:elieazzi
ID: 12241673
Man, I'll pray for you to god protect you ! Thank you're wonderful expert !!!

This is the output.

Response Num Responses Raw %
Beginner 3             60
Intermediate 1       20
Advanced 1             20


Is there a way: I can have the total of users respond to number 3. as like this:

Total Responses: 5

Tks again !! Supper !
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12241754
Sure:

<%
sub OutputWriteSurvey()

dim adoRS, strSQL, adoRSTotals, strLevel, intQuestions, numPercent
    set adoRS = CreateObject("ADODB.Recordset")
     strSQL = "SELECT QuestionNo, AnswerID, COUNT(AnswerID) AS Expr1 " &_
           "FROM  SUR_ResponsesAnswers  " &_
           "GROUP BY questionno, answerid " &_
           "Having (QuestionNo = 3) " &_
           "ORDER BY questionno, answerid "
     adoRS.CursorLocation = 3 'adUseClient
     adoRS.Open strSQL, ConnectString

    set adoRSTotals = CreateObject("ADODB.Recordset")
    strSQL = "SELECT COUNT(AnswerID) AS intQuestions FROM  SUR_ResponsesAnswers WHERE QuestionNo = 3"
     adoRSTotals.CursorLocation = 3 'adUseClient
     adoRSTotals.Open strSQL, ConnectString
     if not adoRSTotals.eof then
          intQuestions = adoRSTotals("intQuestions")
     else
          intQuestions = 0
     end if
 
     Response.write("<table>" & VBCrLf)
     Response.Write("<tr><th>Response</th><th>Num Responses</th><th>Raw %</th></tr>" & VBCrLf)
     do while not  adoRS.eof
          select case adoRS("AnswerID")
               case 1
                    strLevel = "Beginner"
               case 2
                    strLevel = "Intermediate"
               case 3
                    strLevel = "Advanced"
                case else
                    strLevel = "N/A"
          end select
          if intQuestions >0 then
               numPercent = 100*(adoRS("Expr1")/intQuestions)
          else
               numPercent = 0
          end if

          Response.write("<tr><td>" & strLevel & "</td><td>" & adoRS("Expr1") & "</td><td>" & numPercent &"</td></tr>" & VbCrLf)
          adoRS.MoveNext
     loop
    response.write("<tr><td colspan=3>Total Responses: " & intQuestions & "</td></tr>" & VBCrLf)
     response.write("</table>")
     
     adoRS.Close
     set adoRS = Nothing
     adoRSTotals.Close
     set adoRSTotals = Nothing
end sub          
%>
0
 

Author Comment

by:elieazzi
ID: 12251305
Wonderful!!!!!

You're a genius SQL / ASP Expert !! Thank you FtB, for your times and support.

I'll continue for others questions, then if i need anythging i'll post my questions.

Regards,
Elie.
0
 
LVL 46

Expert Comment

by:fritz_the_blank
ID: 12251471
Best of luck,

FtB
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now