Stumped once again. ASP and oracle

MrhelpMe · #1 (**permalink**) 05-29-06, 13:11

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.

MrhelpMe · #2 (**permalink**) 05-31-06, 12:27

Sorry experts,

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

wayneph · #3 (**permalink**) 05-31-06, 17:50

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.

MrhelpMe · #4 (**permalink**) 06-02-06, 15:32

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.

wayneph · #5 (**permalink**) 06-02-06, 17:12

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.

MrhelpMe · #6 (**permalink**) 06-05-06, 09:06

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.

MrhelpMe · #7 (**permalink**) 06-06-06, 10:42

Can anyone provide an answer to this. Thanks again.

Internet Services

Web Development

Digital Marketing

Consumer Tech