store array in database

Results 1 to 2 of 2

Thread: store array in database

  1. #1
    Join Date
    Dec 1969

    Default store array in database

    In the "add.asp" page, it collects 2 kind of input: "q_text" and "ans(5)".<BR><BR>Question text : &#060;INPUT type="test" name="q_text" value="" size="50"&#062;<BR><BR>Answer text: <BR> &#060;% dim ans(5)<BR> for i=1 to 5%&#062;<BR> &#060;INPUT type=text name="ans(&#060;%=i%&#062;)" value="ans(&#060;%=i%&#062;)" size="20"&#062;<BR><BR> &#060;%next%&#062;<BR>----------------------------------------------------------------<BR>In second page called "manage.asp", the data collected will be stored into database. <BR><BR> qtext=Request.Form("q_text") <BR><BR> for m=1 to 5<BR> if Request.Form("ans("&m&")")&#060;&#062;"" then<BR> a(m) = request.form("ans("&m&")")<BR> end if<BR> next<BR><BR>I have no problem in storing the "qtext" into database table by this way:<BR>sql_insert = "insert into question (qtext) values (&#039" & qtext & "&#039)"<BR><BR>For the "ans" in array format, i don&#039t know how.<BR>Let say my table has field as below:<BR> qtext: text<BR> ans1 : text<BR> ans2 : text<BR> ans3 : text<BR> ans4 : text<BR> ans5 : text<BR><BR>How do i write the sql statement to store the all values collected into database?

  2. #2
    Join Date
    Dec 1969

    Default RE: store array in database

    First of all, you are complicating a couple of things you don&#039t need to. Why name the fields "ans(1)", "ans(2)", etc.? Why not simply "ans1", "ans2", etc., to correspond to the names of the DB fields?<BR><BR>Second, do you *really* mean that you want to initialize those text fields to say "ans(1)", "ans(2)", etc.??? Aren&#039t those kind of meaningless answers? Do you truly want to initialize them, at all? Or do you want to leave them blank for the user to fill in?<BR><BR>Third thing: Are you showing us all the code? If so, then what is the point of the ans() array in your first page? That is, in the following code?<BR><BR>&#060;% dim ans(5)<BR>for i=1 to 5%&#062;<BR>&#060;INPUT type=text name="ans(&#060;%=i%&#062;)" value="ans(&#060;%=i%&#062;)" size="20"&#062;<BR>&#060;%next%&#062;<BR><BR>The ans array contains no data. Not that it matters, since you never use it in that loop. (Don&#039t mistake the VBS array for the *strings* that you put into the form field name and value properties.<BR><BR>If you aren&#039t showing all the code, and if the ans() array *does* contain some data, then I *suspect* that what you meant to do was something like this:<BR><BR>&#060;% dim ans(5)<BR>... somehow initialize the contents of the ans array ...<BR>for i=1 to 5%&#062;<BR>&#060;INPUT type=text name="ans&#060;%=i%&#062;" value="&#060;%=ans(i)%&#062;" size="20"&#062;<BR>&#060;%next%&#062;<BR><BR>That may look similar to what you posted, but it is not at all the same. In that code, the i&#039th value of the ans() array is used to initialize the i&#039th answer "ansi" text field.<BR><BR>So...moving to the next page. Assuming you use my suggestion for the field names (whether or not you initialize the text field values), then...<BR><BR>&#060;%<BR>qtext = Replace( Request.Form("qtext"),"&#039","&#039&#039" )<BR><BR>SQL = "INSERT INTO table (qtext,ans1,ans2,ans3,ans4,ans5) VALUES(&#039" & qtext & "&#039"<BR><BR>For i = 1 to 5<BR>&nbsp; &nbsp; fname = "ans" & i<BR>&nbsp; &nbsp; ans = Replace( Request.Form( fname ),"&#039","&#039&#039" )<BR>&nbsp; &nbsp; SQL = SQL & ",&#039" & ans & "&#039"<BR>Next<BR><BR>SQL = SQL & ")"<BR>...<BR>%&#062;<BR><BR>Do you see it? We build the name of the Form field in the same way as the name of the DB field. (This is not necessary, of course, but it does make following the program flow easier and more readable.) Then we go get the form field and append it, in place, to the query.<BR><BR>We do the Replace to ensure that if the user types in a &#039 character it won&#039t mess up our SQL.<BR><BR>************************************<B R>DISCLAIMER: If you prefer not to receive responses from me, please so inform me! You may do so publicly in this forum or privately by emailing me at at your option. Any statements or comments made by me are my own responsibility and opinions and are in no way endorsed by my employer or the owner of this forum. Thank you. <BR><BR>

Posting Permissions

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