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

    Unhappy Unanswered: Stumped once again. ASP and oracle

    Hello,

    Need some help on this ASAP.
    I have a survey form, that retrieves information from a database and displays it on the screen. What happens is my ASP code calls a stored procedure(well 2 of them so far) and displays the information in the following format.
    Question
    ---radio button choices.

    The following code accomplished this.
    Code:
    <%
    		Do while not objRS.eof and not objRS.BOF 
    		response.write (objRS("Descr")& "<BR>") & "<BR>"
    			
    		Do while not objTxt.eof and not objTxt.BOF 
    	%>
    	<%  If (objTxt("Type")) = "Radio" then %>
    		<input type="checkbox" name="radio" value="<%=(objTxt("Text"))%>"> <%=(objTxt("Text")&"<BR>")%> 
    	    
    	<%ELSE%>
    		
    		<br><%=(objTxt("Text"))%>&nbsp;&nbsp;<input type="text" size ="75" name="text" value="<%=(objTxt("sub_text"))%>"> <BR>
            
    	<%End IF%>
    	<%	
    	objTxt.movenext()
    		  loop
    	objRS.movenext()%>
    	<BR>
    <%
    	Loop
    %>
    My question now is how do I take the values that the user will select, based on the questions for that survey and insert them into a database table that has the following columns.
    SurveyID,
    QuestionID
    AnswerID
    Descr.

    I will call a stored procedure that does the insert, how do I call a stored procedure in asp that will do an insert. More importantly, how do I take the values that are choosen by the user and insert them into the database columns.

    Your help is appreciated.

  2. #2
    Join Date
    Apr 2006
    Posts
    140
    Sorry experts,

    Has anyone had a chance to look at this for me. Sorry just have some deadlines. Thanks again.

  3. #3
    Join Date
    Aug 2005
    Location
    D/FW, Texas, USA
    Posts
    78
    This is from something in SQL Server. I'm not 100% sure how Oracle expects things. This will call a stored procedure and pass in 2 variables. The parameters are used based on the order of the parameters in the Stored Procedure definition.
    Code:
    set cmd = Server.CreateObject("ADODB.Command")
    
    set cmd.ActiveConnection = yourConnectionObject
    cmd.CommandText = "YourStoredProcedureName"
    cmd.CommandType = 4
    
    cmd(1) = Request.QueryString("QuestionID")
    cmd(2) = Request.QueryString("AnswerId")
    You'll just need to adapt this sample so that it matches your data.
    --wayne
    SELECT * FROM Users WHERE Clue>0
    0 rows returned

  4. #4
    Join Date
    Apr 2006
    Posts
    140
    Hello wayneph,

    Thanks again for the response. I finally figured out how to pass variables from asp to oracle however, now I have more issues.

    I have a surveyUserAnswer table that consists of the following columns
    SurveyID,Questionid,answerId,text.

    Now I have successfuly passed to values using this
    Code:
    oCmd.Parameters.Append oCmd.CreateParameter("AnswerID", adVarChar, adParamInput, 100, Request.Form("interests"))
    How do I populate the text field with information from the survey question as well as how do I populate surveyid, and questionID. These are not form elements for I cannot use request.form. I think I will need to do something in my stored procedure. Here it is if someone could build on this it would be greatly appreciated
    Code:
    CREATE OR REPLACE
    PACKAGE BODY SurveyAnswerInsert 
    AS
      PROCEDURE SurveyAnswerInsert(AnswerID IN VARCHAR2,
                                   Text IN VARCHAR2) 
      IS
      BEGIN
          INSERT INTO Survey_User_Answer(Answer_Id,Text)
          VALUES(AnswerID, Text);
      END;
    END;
    Thanks again everyone.

  5. #5
    Join Date
    Aug 2005
    Location
    D/FW, Texas, USA
    Posts
    78
    The surveyid should come from either the querystring or the session, or a hidden form field. You're going to have to store it somewhere when you display the survey to the user.

    I'd say the QuestionID is going to come out of the form's field name...

    ie. <input type="radio" id="Question23" value="1">

    In this case I would strinp the 23 out of the id field, and get the value...

    This will show you all the answers at the same time. You'll just need to replace the display part in the loop with the call to the database.
    Code:
    <%
      For Each Item in Request.Form
        If left(Item, 8) = "Question" Then
          'You know you are dealing with a question
          QuestionID = Right(item, Len(Item)-8)
          AnswerID = Request.Form(item)
          Response.Write "Answer #" & AnswerID & " for Question #" & QuestionID & "<BR>"
        End If
      Next
    %>
    Of course if you only have one question per page, it would be easier, and you just need to store the Question ID in a hidden field or something like that, similar to what you are doing with the survey id.
    --wayne
    SELECT * FROM Users WHERE Clue>0
    0 rows returned

  6. #6
    Join Date
    Apr 2006
    Posts
    140
    wayneph,

    Thanks again and please bear with me on this.
    I have taken your advice and used hidden fields on my form to store the values as in the code below
    Code:
    <table width="60%" cellspacing="0" cellpadding="5">
    	<%
    		Do while not objRS.eof
    		response.write (objRS("Question_Id")& ".    ")&(objRS("Descr")& "<BR>") & "<BR>"
    			
    		Do while not objTxt.eof
    	%>
    	<%  If (objTxt("Type")) = "Radio" then %>
    		<input type="checkbox" name="Question" value="<%=objTxt("sub_text")%>"> <%=(objTxt("Text")&"<BR>")%> 
    		<input type="hidden" name="HiddenSurveyID" value="<%=objRS("Survey_ID")%>">
    		<input type="hidden" name="HiddenQuestionID" value="<%=objRS("Question_id")%>">
    		<input type="hidden" name="HiddenText" value="<%=objTxt("text")%>">
    	<%ELSE%>
            <br><%=(objTxt("Text"))%>&nbsp;&nbsp;<input type="text" size ="75" name="textBoxAnswer"
     value=""> <BR>
            	<%End IF%>
    	<%objTxt.movenext()
    	Loop
    	objRS.movenext()%>
    	<BR>
    <%
    	Loop
    %>
    </table>
    Now on my next page I have made a call to the database requesting
    Code:
    Request.Form("Answers")) and Request.Form("HiddenText"))
    I have not included all the code but giving you an idea of what I requested.
    My problem is when I look at the table that recieved these inserted values my request.form("Answers") is inputting the correct values however my request.form("hiddenText") is displaying all the choices from the form regardless of what they selected. So for instance if the user puts a checkmark next to a and b, I should see in the database AnswerID=a,b and the text should just be the text of what a and be refer too. Not a,b,c,d.....
    Maybe you have answered this in your code but I am not understanding. Thanks again Wayneph.

  7. #7
    Join Date
    Apr 2006
    Posts
    140

    Unhappy Sorry experts just in desparte need of this

    Can anyone provide an answer to this. Thanks again.

Posting Permissions

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