Nested "For" Loop Trouble

[scripter50]

I have an asp page, much of the html is dynamically generated in ASP for loops. My problem is saving the data.
The code runs like this..

How many? (Qty input box)
For i = 1 to Qty
Field 1
Field 2
Field 3
Next

For k = 1 to Field 3 Qty
Field 4
Field 5
Next

If request.form("btnOkay") = "Proceed" then

For i = 1 to Qty
sql= "insert into tbl...."
objConn.execute sql
Next

(the code works fine til here)

But how do I get the next level of loop inserted??

Thanks in advance for your help.
Scripter50

 

[FesterSXS]

need more detail I'm afraid?

what do you mean by "But how do I get the next level of loop inserted??"

Tony

Spirax-Sarco - steam traps control valves heat exchangers
Sun Villa - Luxury Florida accommodation

 

[guitardave78]

Are you trying to pause the application until a form button is pressed?

}...the bane of my life!

http://www.fuzzyd.co.uk

 

[scripter50]

I was trying to avoid too much code in the string...
I insert the "day" information ( 1 to Qty )..
then I need to insert the "visit" information ( for 1 to Qty days, there are 1 to qty Visits)

For i = 1 to Qty
sql= "insert into tbl...."
objConn.execute sql
Next

For i = 1 to Qty (days)
For k = 1 to Qty (visits)
sql = "insert into tbl..."
next
next

Is that the right approach???

 

[damber]

Do you mean you want to insert related information into related tables in your database ?

So - an example could be: the days were Mon, Tue, Wed and on Mon you had visit at 10am and a visit at 2pm, and on Wed you had a visit at 1pm ?

Which you want to add into two tables: Days and Visits that are linked by a Primary and Foreign Key?

If so, then I understand your question - how to add related records to a database. But.. the example you have used - days / visits seems a little strange - why wouldn't you have both of those bits of information in the same table ... e.g. visit date and visit time (two colums) ? You can easily deduce the day from that.

I think we need more information - your example data and scenario is just too vague. Please post your actual code - this should help.

A smile is worth a thousand kind words. So smile, it's easy!

 

[scripter50]

Here's the code..

<% if request.form("btnProceed") = "Proceed" then

nNbrOfDays = request.form("txtNbrOfDays") %>

<input type="hidden" name="hdnNbrOfDays" value="<%=nNbrOfDays%>">

<% for i = 1 to nNbrOfDays %>

<table cellspacing="0" cellpadding="2" border="0" width>
<tr>
<td class="fld_input_top" width="175">Day <%=i%>&nbsp;<input type="text" name="txtDate<%=i%>" value style="width:80">
<img src="images/calendar.gif" WIDTH="16" HEIGHT="16"></td>

<td class="fld_input_top" width="125">
<select name="cboService<%=i%>" style="width:120">
<option value="0">No Service</option>
<option value="1">Daily Visits</option>
<option value="2">Live-In</option>
</select>
</td>

<td class="fld_input_top" width="150">
<select name="cboVisits<%=i%>" style="width:120">
<option value="0">No Visit</option>
<option value="1">One Visit</option>
<option value="2">Two Visits</option>
<option value="3">Three Visits</option>
<option value="4">Four Visits</option>
</select>
</td>
</tr>
</table>
<br><br>




<% next %>

<input type="submit" name="btnProceed2" value="Show Visit Details">

<br>

<% end if

if request.form("btnProceed2") <> "" then

nNbrOfDays = Request.Form("hdnNbrOfDays")

for i = 1 to nNbrOfDays %>

<table cellspacing="0" cellpadding="2" border="0" width>
<tr>
<td class="fld_input" colspan="4"><font style="color:#0272B6; font-weight:bold">Day Number <%=i%> &nbsp;&nbsp;Date:&nbsp;<%=Request.Form("txtDate" & cint(i))%></font></td>
</tr>

<% for k = 1 to request.form("cboVisits" & cint(i)) %>

<tr>
<td class="fld_input" width="25">&nbsp;</td>
<td class="fld_input" colspan="3"><b>Visit <%=k%></b></td>
</tr>

<tr>
<td width="25">&nbsp;</td>
<td width="25">&nbsp;</td>
<td class="fld_input" width="100">Time of Visit:&nbsp;</td>
<td class="fld_input" width="100">
<select name="cboTime<%=i%>_<%=k%>" style="width:150">
<option value="1">8:00am</option>
</select>
</td>
</tr>

<tr>
<td width="25">&nbsp;</td>
<td width="25">&nbsp;</td>
<td class="fld_input">Petsitter:&nbsp;</td>
<td class="fld_input">
<select name="cboPetsitter<%=i%>_<%=k%>" style="width:150">
<option value="1">Petsitter</option>
</select>
</td>
</tr>

<tr>
<td width="25">&nbsp;</td>
<td width="25">&nbsp;</td>
<td class="fld_input">Fee:&nbsp;$&nbsp;</td>
<td class="fld_input"><input type="text" name="txtFee<%=i%>_<%=k%>" value style="width:150"></td>
</tr>

<% next %>

</table>

<input type="hidden" name="hdnDate<%=i%>" value="<%=Request.Form("txtDate" & cint(i))%>">
<input type="hidden" name="hdnService<%=i%>" value="<%=Request.Form("cboService" & cint(i))%>">
<input type="hidden" name="hdnVisits<%=i%>" value="<%=Request.Form("cboVisits" & cint(i))%>">
<input type="hidden" name="hdnNbrOfDays2" value="<%=nNbrOfDays%>">

<br>

<% next

end if

if Request.Form("btnSubmit") = "Submit" then

nDayNbr = cint(0)

for i = 1 to nNbrOfDays


nLastDayKey = 1
nNewDayKey = cint(nLastDayKey) + 1
nDayNumber = nDayNumber + 1
sDate = fnSubstitute(Request.Form("hdnDate" & cint(i)))
nNbrOfVisits = Request.Form("hdnVisits" & cint(i))
nXrefService = Request.Form("hdnService" & cint(i))

sql_days = "insert into tblReservationDays ....."

objConn.execute sql_days

next




for i = 1 to nNbrOfDays

nDayNbr = i

'Response.Write "Day Number " & nDayNbr & "<br>"

for k = 1 to nNbrOfVisits


nLastVisitKey = 1
nNewVisitKey = cint(nLastVisitKey) + 1

nVisitNbr = k
nVisitTime = Request.Form("cboTime" & cstr(i) & "_" & cstr(k))
nVisitSitter = Request.Form("cboPetsitter" & cstr(i) & "_" & cstr(k))
nVisitFee = Request.Form("txtFee" & cstr(i) & "_" & cstr(k))


'Response.Write "Visit Number " & nVisitNbr & "<br>"
'Response.Write "nVisitTime " & nVisitTime & "<br>"
'Response.Write "nVisitSitter " & nVisitSitter & "<br>"
'Response.Write "nVisitFee " & nVisitFee & "<br><br>"
'Response.End


sql_visit = "insert into tblReservationVisits (intVisitKey, intXrefDay, strTime, intXrefPetsitter, " & _
" nbrVisitFee, intVisitNbr, intXrefReservation, strLastModified) " & _
" values (" & nNewVisitKey & "," & nDayNbr & "," & nVisitTime & "," & nVisitPetsitter & "," & _
nVisitFee & "," & nVisitNbr & "," & nNewResNbr & "," & sDateString & ")"

'response.write sql_visit & "<br><br>"
'response.end

objConn.execute sql_visit

next
next


if err.number <> 0 then
Response.write "Error with sql_visit: = " & sql_visit & "-" & err.description
end if

end if

%>