Really need help ASAP with ASP, looping, inserting from database SP

[clinttoris@hotmail.com]

Hello,

If someone could help me it would be appreciated as I am not having
much luck.
I'm struggling with my asp code and have some questions relating to asp
and oracle database.

First question. I have a web survey that I am working on and have
successfully dynamically taken the info from a database, displayed it
on the screen and then taken the users answers and inserted them into a
database table. Let me explain the problem now that you have the
background by showing my code.
Code:
<table width="60%" cellspacing="0" cellpadding="5" >
<%
Do while not objRS.eof
response.write (objRS("Questio n_Id")& ". ")&(objRS("Desc r")&
"<BR>") & "<BR>"
Do while not objTxt.eof
%>
<% If (objTxt("Type") ) = "Radio" then %>
<input type="checkbox" name="questions "
value="<%=objTx t("sub_text")%> "> <%=(objTxt("Tex t")&"<BR>")% >
<input type="hidden" name="HiddenSur veyID"
value="<%=objRS ("Survey_ID")%> ">
<input type="hidden" name="HiddenQue stionID"
value="<%=objRS ("Question_id") %>">
<input type="hidden" name="HiddenTex t"
value="<%=objTx t("text")%>">
<%ELSE%>
<br><%=(objTxt( "Text"))%>&nbsp ;&nbsp;<input type="text" size
="75" name="textBoxAn swer"
value=""> <BR>

<%End IF%>
<%
objTxt.movenext ()
Loop
objRS.movenext( )%>
<BR>
<%
Loop
%>
</table>

This is the main code that loops through each question from the
database and then displays the question and associated choices
underneath.
ie.How are you today
good(checkbox field)
Not bad(checkbox field)
excellent(check box field)

Other(Text box field)

The answers(ie.good , not bad etc) have values stored with a,b,c,d in
the database. When the user chooses good for instance I want the value
A to be written to the database and in another column in the database
the value GOOD. Instead it is writing A and then "good,not
bad,excellent,o ther". It is looping but have no idea how to just take
the value that they selected rather then inserting all the values. If
the whole structure of the loop needs to be changed please change it to
whatever you experts think is needed.

Secondly, how do I take the value of the text input field if other is
chosen for the associated question and then add D(or whatever value it
is) and the users text value(i.e what ever they fill in the text box).
So in the above example, if the user chooses other and writes in the
text box(horrible day, got into an accident), I want to insert into the
database d and "horrible day, got into an accident". Here is the code
that passes those values
Code:
<%
oConn.Open

Set oCmd = Server.CreateOb ject("ADODB.Com mand")
oCmd.ActiveConn ection = oConn
oCmd.CommandTyp e = 4
oCmd.CommandTex t = "surveyanswerin sert.surveyansw erinsert"

oCmd.Parameters .Append oCmd.CreatePara meter("SurveyID ", adVarChar,
adParamInput, 400, Request.Form("H iddenSurveyID") )
oCmd.Parameters .Append oCmd.CreatePara meter("AnswerID ", adVarChar,
adParamInput, 900, Request.Form("q uestions"))
oCmd.Parameters .Append oCmd.CreatePara meter("Text", adVarChar,
adParamInput, 900, Request.Form("H iddenText"))
oCmd.Execute
%>

Lastly, as you can see with the above code you might be wondering why I
used adVarchar for SurveyID and AnswerID. Well I don't want to but if I
make the surveyID adNumeric and change the survey id field in the
database to Number and run this code on the web I get
Code:

Error Type:
ADODB.Command (0x800A0D5D)
Application uses a value of the wrong type for the current operation.


It only works with adVarchar. Could someone tell me what I am doing
wrong. It's oracle 9 and classic asp.

Thanks experts and sorry for all the questions.

[Jeff Cochran]

Re: Really need help ASAP with ASP, looping, inserting from database SP

>The answers(ie.good , not bad etc) have values stored with a,b,c,d in[color=blue]
>the database. When the user chooses good for instance I want the value
>A to be written to the database and in another column in the database
>the value GOOD. Instead it is writing A and then "good,not
>bad,excellent, other". It is looping but have no idea how to just take
>the value that they selected rather then inserting all the values. If
>the whole structure of the loop needs to be changed please change it to
>whatever you experts think is needed.[/color]

Stop using checkboxes, use a radio button. Unless you really mant to
select more than one. And the selected item's value is the VALUE
assigned in the radio button that gets checked. if you want
additional info in a new field, you have to do an IF/THEN on the
value, something like:

IF VALUE="A" THEN DATA="Chevy"

Insert that into the database in your SQL INSERT statement.
[color=blue]
>Secondly, how do I take the value of the text input field if other is
>chosen for the associated question and then add D(or whatever value it
>is) and the users text value(i.e what ever they fill in the text box).
>So in the above example, if the user chooses other and writes in the
>text box(horrible day, got into an accident), I want to insert into the
>database d and "horrible day, got into an accident". Here is the code
>that passes those values[/color]

Again, learn to use forms. The same process is used, but if the
VALUE="D" THEN DATA = TextBoxValue.
[color=blue]
>Lastly, as you can see with the above code you might be wondering why I
>used adVarchar for SurveyID and AnswerID. Well I don't want to but if I
>make the surveyID adNumeric and change the survey id field in the
>database to Number and run this code on the web I get
>Code:[/color]
[color=blue]
>Error Type:
>ADODB.Comman d (0x800A0D5D)
>Application uses a value of the wrong type for the current operation.[/color]

That's a "Well, duh.." kind of statement. You can't use a letter in a
numeric field. Nothing wrong with VARCHAR except that if the field
length is always 1 (A, B, C, etc...) then it's not really VAR is it?
Just a CHAR with a length of 1.

Note that you'l need to use whatever Oracle has, I've used SQL Server
for so long I almost forgot how to spell "Oracle".

Jeff

[clinttoris@hotmail.com]

Re: Really need help ASAP with ASP, looping, inserting from database SP


Jeff Cochran wrote:[color=blue][color=green]
> >The answers(ie.good , not bad etc) have values stored with a,b,c,d in
> >the database. When the user chooses good for instance I want the value
> >A to be written to the database and in another column in the database
> >the value GOOD. Instead it is writing A and then "good,not
> >bad,excellent, other". It is looping but have no idea how to just take
> >the value that they selected rather then inserting all the values. If
> >the whole structure of the loop needs to be changed please change it to
> >whatever you experts think is needed.[/color]
>
> Stop using checkboxes, use a radio button. Unless you really mant to
> select more than one. And the selected item's value is the VALUE
> assigned in the radio button that gets checked. if you want
> additional info in a new field, you have to do an IF/THEN on the
> value, something like:
>
> IF VALUE="A" THEN DATA="Chevy"
>
> Insert that into the database in your SQL INSERT statement.
>[color=green]
> >Secondly, how do I take the value of the text input field if other is
> >chosen for the associated question and then add D(or whatever value it
> >is) and the users text value(i.e what ever they fill in the text box).
> >So in the above example, if the user chooses other and writes in the
> >text box(horrible day, got into an accident), I want to insert into the
> >database d and "horrible day, got into an accident". Here is the code
> >that passes those values[/color]
>
> Again, learn to use forms. The same process is used, but if the
> VALUE="D" THEN DATA = TextBoxValue.
>[color=green]
> >Lastly, as you can see with the above code you might be wondering why I
> >used adVarchar for SurveyID and AnswerID. Well I don't want to but if I
> >make the surveyID adNumeric and change the survey id field in the
> >database to Number and run this code on the web I get
> >Code:[/color]
>[color=green]
> >Error Type:
> >ADODB.Comman d (0x800A0D5D)
> >Application uses a value of the wrong type for the current operation.[/color]
>
> That's a "Well, duh.." kind of statement. You can't use a letter in a
> numeric field. Nothing wrong with VARCHAR except that if the field
> length is always 1 (A, B, C, etc...) then it's not really VAR is it?
> Just a CHAR with a length of 1.
>
> Note that you'l need to use whatever Oracle has, I've used SQL Server
> for so long I almost forgot how to spell "Oracle".
>
> Jeff[/color]



Jeff thanks for getting back to me.

O.k I wish I could use any of the info you provided but I cannot as it
doesn't apply.
As for using the radio buttons. This will not work as I need to select
multiple values. My questions usually end with select all that apply?
So using radio buttons will not work and that is why I am using check
boxes. Any ideas how to construct my loop or just take the values the
user selects from my checkboxes as posted in my original question?

Secondly I know how to use forms but if my loop is not constructed
properly my passing of data will be affected so therefore I will not be
able to capture the proper text. So your answer will not be sufficient
either.

Thirdly, the surveyID and answerID is not getting passed a letter it
is getting passed a number. So your statement Well, duh.." kind of
statement is incorrect as you are mistaken as to what value was being
passed. I'm assuming because my loop and hidden fields are not working
correctly the value 1,1,1,1 is being passed as a string so the
AdNumeric will not function. But I need someone to first fix my loop
using the required checkboxes needed. Thanks for your replies Jeff and
wish I could use anything you said but unfortunately I cannot.

Also, I wish I could use SQL server which I have over the past 7 yrs
unfortunately my new company is using Oracle. Ya that Sucks:(

Any other ideas?