Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

An output of a SQL Statement in ASP.

Posted on 2004-10-06
24
302 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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

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
 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

838 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