Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

An output of a SQL Statement in ASP.

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
elieazzi
Asked:
elieazzi
  • 14
  • 10
1 Solution
 
fritz_the_blankCommented:
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
 
fritz_the_blankCommented:
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
 
elieazziAuthor Commented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
elieazziAuthor Commented:
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
 
fritz_the_blankCommented:
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
 
elieazziAuthor Commented:
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
 
fritz_the_blankCommented:
Can you tell me exactly what line 150 is?

FtB
0
 
fritz_the_blankCommented:
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
 
elieazziAuthor Commented:
FYI This is on line 150:
do while not  adoRS    

Thank you FtB.
0
 
fritz_the_blankCommented:
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
 
elieazziAuthor Commented:
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
 
fritz_the_blankCommented:
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
 
elieazziAuthor Commented:
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
 
fritz_the_blankCommented:
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
 
elieazziAuthor Commented:
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
 
fritz_the_blankCommented:
Add it to your dim statement:

dim adoRS, strSQL, strLevel


FtB
0
 
fritz_the_blankCommented:
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
 
fritz_the_blankCommented:
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
 
elieazziAuthor Commented:
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
 
fritz_the_blankCommented:
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
 
elieazziAuthor Commented:
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
 
fritz_the_blankCommented:
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
 
elieazziAuthor Commented:
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
 
fritz_the_blankCommented:
Best of luck,

FtB
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 14
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now