Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2006
    Posts
    140

    Red face Unanswered: Trying ASAP with Oracle

    Hello,

    Trying to building a survey form that stores answers from radio and text boxes into a database. I have created the database and populated it with some sample data. Now I am on my way to my asp pages. I have successfully tapped into oracle using asp ADO connection but have run into an issue on how to complete the following.

    1)2 tables from my Database..
    SurveyQuestion - stores surveyid, questionID, question
    SurveySubQuestion - Surveyid, questionid, description
    So for example I have question
    Are you happy?
    yes
    no
    Other[]
    I need my asp page to loop through all questions, display them and underneath each question have the appropriate SurveySubQuestion.Description displayed.

    Here is the code that I am working onbut cannot figure out
    Code:
    	
    <%while not objRS.EOF
    if Trim(objRS("Question_ID")) <> 0 then
    %>
    <option value=<%=Trim(objRS("QuestionID"))%>><B><%=Trim(objRS("QuestionID") & ". ")%></B>&nbsp;&nbsp;&nbsp;<%=Trim(objRS("question"))%></option><BR><BR>
    <option value=<%=Trim(objTxt("description"))%>><input type = "Checkbox" name = "RADIO" Value ="<%=Trim(objTxt("description"))%>"><%=Trim(objTxt("description"))%></option>
    <BR>
    <% end if
    objRS.MoveNext()
    wend
    %>
    This obviously is not producing the desired results. I need to see question and then subchoices underneath

    2)How do I code this to show some questions as text box and not radio based on the loop structure. ie. Other[]
    I could add another field in my database called Type=radio or memo but I do not know what I should do with this in asp.

    I can create oracle stored procedures if procedures are what you recommend but I do not understand what I am trying to do from this end.

    Thanks again. Your suggestions and feedback will really help.

  2. #2
    Join Date
    Aug 2005
    Location
    D/FW, Texas, USA
    Posts
    78
    1) You have two options. You could use two separate Recordsets. Have the first one just get the questions. As you're looping through that recordset, open a second recordset that passes the Question ID in the where clause. Or, If Oracle has a PIVOT command (sorry, I'm not an Oracle guy) you could do it all in a single query. Pivot the answers up so that they appear in the same record as the question. Then Answer1 would be the first choice, Answer2 would be the second choice, etc...

    2) Personally, I would create a field to designate if you need a text box or not. Then when you're looping through the answers (assuming you don't use a PIVOT) you can just check that using an IF statement or a SELECT CASE to determine what to display. When you're reading it back in, you'll also have to know what was displayed so you know what kind of data to check for.
    --wayne
    SELECT * FROM Users WHERE Clue>0
    0 rows returned

  3. #3
    Join Date
    Apr 2006
    Posts
    140
    I appreciate the reply wayneph and have done exactly what you pointed out in the first answer. Here are my oracle queries

    Code:
    SELECT Questionid,
                   Question
            FROM SURVEYQUESTIONS;
    The second recordset
    Code:
    	SELECT SURVEYQUESTIONS.questionid,
                   SURVEYQUESTIONS.Descr,
                   SurveySubQuestion.Description 
          FROM SURVEYQUESTIONS, SurveySubQuestion
          WHERE SurveyQuestions.Question_ID = SurveySubQuestion.Questionid
          and SurveyQuestions.SurveyID = SurveySubQuestion.Surveyid;
    Now how do I construct my asp code to display this information with
    Question
    Subquestion.

    Sorry, I tried to do it with the code I specified but could not come up with the answer. If you could direct me with some sample code that will be great. As well, if you could sample the if else part that would be appreciated. I have setup the connections to the 2 Stored procedures and can loop through to make sure the recordset get's returned but I cannot put my head around the code to make both recordsets come back in order.

    I think the issue is because my second query returns the results as follows

    Descr Description
    How old are you 15
    How old are you 20
    How old are you 30

    So when I loop through this recordset, how old are you gets repeated. I basically need it to show how old are you once. Can't figure it out

  4. #4
    Join Date
    Aug 2005
    Location
    D/FW, Texas, USA
    Posts
    78
    can you show us your loop in ASP? It should be as easy as just telling it not to rewrite that column out of the recordset...
    --wayne
    SELECT * FROM Users WHERE Clue>0
    0 rows returned

  5. #5
    Join Date
    Apr 2006
    Posts
    140
    yes sorry I meant in the last post to refer to my first post and there you will see the code. regardless I have posted it again. Hope this helps.

    Code:
    <%while not objRS.EOF
    if Trim(objRS("QuestionID")) <> 0 then
    %>
    <option value=<%=Trim(objRS("QuestionID"))%>><B><%=Trim(objRS("QuestionID") & ". ")%></B>&nbsp;&nbsp;&nbsp;<%=Trim(objRS("question"))%></option><BR><BR>
    <option value=<%=Trim(objTxt("description"))%>><input type = "Checkbox" name = "RADIO" Value ="<%=Trim(objTxt("description"))%>"><%=Trim(objTxt("description"))%></option>
    <BR>
    <% end if
    objRS.MoveNext()
    wend
    %>

  6. #6
    Join Date
    Aug 2005
    Location
    D/FW, Texas, USA
    Posts
    78
    what you'll need is two loops. this is pseudo code...

    Code:
    open recordset with list of questions
    while not questions.eof
      response.write question
      open recordset with list of answers for this single question
      while not answers.eof
        write each answer
        answers.movenext
      loop
      questions.movenext
    loop
    --wayne
    SELECT * FROM Users WHERE Clue>0
    0 rows returned

  7. #7
    Join Date
    Apr 2006
    Posts
    140
    Thanks Wayneph,

    I got it now. Not sure what I was thinking. here is the final code if someone else wishes to use it
    Code:
    <%
    	Do while not objRS.eof
    		  response.write (objRS("Descr")& "<BR>") & "<BR>"
    
        Do while not objTxt.eof
    	%>
    	      <input type="radio" name="Question" value="<%=(objTxt("text"))%>"> <%=(objTxt("text")&"<BR>")%>	
    	<%
    	objTxt.movenext()
    		  loop
    	objRS.movenext()%>
    	<BR>
    <%
    	Loop
    %>
    Have a great weekend.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •