Having Trouble with a Loop Command

This is confusing so I will try to explain it as best I can I have a query that gets foods from a database called (dietdb) it is supposed to add up how much protein and carbs the person needs say thier protein is 62 and their carbs is 48 it should show me how ever many protein foods to add up to 62 and how ever many carbs foods to add up to 48 my probelm is it is only showing me one food per each and it is showing the same protein food each time. I am pretty sure it has to do with bookmarking or looping but I cannot get it. I have been working on this for over a month with no luck always the same results.

Here is the code

<%
Set Myconn=Server.CreateObject("ADODB.Connection")
Set rs=Server.CreateObject("ADODB.Recordset")
Myconn.open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " & _
Server.MapPath("dietdb.mdb")

'CustID = 441


SQL = "Select Distinct tblcustinfo.resist1, tblcustinfo.resist2, AllFoodP.Meal1FoodP, AllFoodP.Meal1ServingP, AllFoodP.Meal1ProteinCount, AllFoodC.Meal1FoodC, AllFoodC.Meal1ServingC, AllFoodC.Meal1CarbCount From tblcustinfo, AllFoodP, AllFoodC Where tblcustinfo.resist1 >= Meal1ProteinCount AND tblcustinfo.resist2 >= Meal1CarbCount AND tblcustinfo.LastName= '"&LastName&"'"

RS.Open SQL, MyConn, 3, 3

On Error Resume Next
rs.MoveFirst
do while Not rs.eof
RS.Bookmark = Bookmark
RS.movenext
%>
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><b>Protein Food Item</b> <%=Server.HTMLEncode(rs.Fields("Meal1FoodP").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><b>Protein Serving Size</b> <%=Server.HTMLEncode(rs.Fields("Meal1ServingP").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><b>Carb Food Item</b> <%=Server.HTMLEncode(rs.Fields("Meal1FoodC").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><b>Carb Serving Size</b> <%=Server.HTMLEncode(rs.Fields("Meal1ServingC").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><b>Carb Count</b> <%=Server.HTMLEncode(rs.Fields("Meal1CarbCount").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><b>Protein Count</b> <%=Server.HTMLEncode(rs.Fields("Meal1ProteinCount").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><b>Protein Allowance</b> <%=Server.HTMLEncode(rs.Fields("resist1").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT SIZE=2 FACE="Arial" COLOR=#000000><b>Carb Allowance</b> <%=Server.HTMLEncode(rs.Fields("resist2").Value)%><BR></FONT></TD>
<hr>
</TR>
<%
rs.MoveNext
loop
'RS.close
'MyConn.Close
'Set RS = Nothing
'Set MyConn = Nothing
%>


and here is what it produces ______________________________________

Protein Food Item Sourdough Bread
Protein Serving Size 1 Slice
Carb Food Item Apple Juice
Carb Serving Size (canned, unsweetened), 1 cup (250mL)
Carb Count 29
Protein Count 54
Protein Allowance 62
Carb Allowance 48

--------------------------------------------------------------------------------
Protein Food Item Sourdough Bread
Protein Serving Size 1 Slice
Carb Food Item Banana
Carb Serving Size 1 medium
Carb Count 28
Protein Count 54
Protein Allowance 62
Carb Allowance 48

--------------------------------------------------------------------------------
Protein Food Item Sourdough Bread
Protein Serving Size 1 Slice
Carb Food Item Eggnog
Carb Serving Size 1 cup (254g)
Carb Count 34
Protein Count 54
Protein Allowance 62
Carb Allowance 48
________________________________________________
as you can see it is changing the carb food item like it is supposed to but not the protein which is the first line. It is also not givng me enough foods to add up to the desired amount. For exmplae in the first record the person would be allowed 62 in protein but he is only getting 54. That is actually close enough but sometimes it shorts them by 20 or 30 and their are items in the db with that amount. I would appreacite any ideas or suggestions please. Thank you in advance.

Mariane

It appears that you have two consecutive rs.MoveNexts before you issue your next statement.

victorpendleton, I noticed that and I have now changed it to

<%
Set Myconn=Server.CreateObject("ADODB.Connection")
Set rs=Server.CreateObject("ADODB.Recordset")
Myconn.open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " & _
Server.MapPath("dietdb.mdb")

'CustID = 441


SQL = "Select Distinct tblcustinfo.resist1, tblcustinfo.resist2, AllFoodP.Meal1FoodP, AllFoodP.Meal1ServingP, AllFoodP.Meal1ProteinCount, AllFoodC.Meal1FoodC, AllFoodC.Meal1ServingC, AllFoodC.Meal1CarbCount From tblcustinfo, AllFoodP, AllFoodC Where tblcustinfo.resist1 >= Meal1ProteinCount AND tblcustinfo.resist2 >= Meal1CarbCount AND tblcustinfo.LastName= '"&LastName&"'"

RS.Open SQL, MyConn, 3, 3

On Error Resume Next
do while Not rs.eof
%>


<%
RS.MoveNext
loop
RS.close
MyConn.Close
Set RS = Nothing
Set MyConn = Nothing
%>


ALthough using this I still get the same answer I got before.

Mariane

How are you joing the tables? I do not see any On statements or where clauses that relate the tables.

I did not join the tables it is pulling all of the infromaton I wanted so I did not know I would have to join them it is pulling the information I need from two tables just with the query but it is always showing the same thing for the protein value. Does that make sense? How would joining the tables help me I am new to that.

Mariane

Try changing your SQL request to
SQL =
"
Select Distinct tblcustinfo.resist1,
tblcustinfo.resist2,
AllFoodP.Meal1FoodP,
AllFoodP.Meal1ServingP,
AllFoodP.Meal1ProteinCount,
AllFoodC.Meal1FoodC,
AllFoodC.Meal1ServingC,
AllFoodC.Meal1CarbCount
From tblcustinfo,
AllFoodP,
AllFoodC
Where tblcustinfo.resist1 >= AllFoodP.Meal1ProteinCount AND
tblcustinfo.resist2 >= AllFoodC.Meal1CarbCount AND
tblcustinfo.LastName= '"&LastName&"'
"

But most probably the problem lies inside logic.

Without a join you have what is called a cartesian join. This results in the product of the referenced tables and can product `unexpected` results. In most cases, you will use the primary key of one table and a foreign key of another table to join the data in the referenced tables. In your case something along the lines may work.
from tblcustinfo t inner join allfoodp afp
on t.key = afp.key
INNER JOIN allfoodc afc
ON t.key = afc.key
...
This would depend on how your tables are structured. If you post the DDL of the tables we may be able to help.

Would it help any if you could see the db I have loaded it here on a friends site if you care to download it I dont know if that will help any

http://www.shakehappens.com/dietdb_copy.mdb

Mariane

I have downloaded your database. Which query are you attempting to run?

The query I am running now is the one you provided me which is

________________________________________________
SQL =
"
Select Distinct tblcustinfo.resist1,
tblcustinfo.resist2,
AllFoodP.Meal1FoodP,
AllFoodP.Meal1ServingP,
AllFoodP.Meal1ProteinCount,
AllFoodC.Meal1FoodC,
AllFoodC.Meal1ServingC,
AllFoodC.Meal1CarbCount
From tblcustinfo,
AllFoodP,
AllFoodC
Where tblcustinfo.resist1 >= AllFoodP.Meal1ProteinCount AND
tblcustinfo.resist2 >= AllFoodC.Meal1CarbCount AND
tblcustinfo.LastName= '"&LastName&"'
"
________________________________________________

and it works with the exception of the fact that I am getting the same food for Protein over and over the carb food is different for each day but the protein is always the same. I cannot figure out why it seems to go from record to record on the carb but not the protein except maybe it is becuase protein is first and it always goes to the first record to start again. I think maybe the way I have the db laid out could be wrong as you can probably see from the db file I have tried many different ideas.

Basically in a nut shell it is suppposed to get information from a form and write it to the db and it figures out a protein value and a carb value for each person and then it is supposed to show them foods ( hopefully 1 food or so for each) of a carb and a protein that they can have for each meal. I have it doing everything I need it to do so far with the exception of displaying the foods correctly what I get now is

Protein Food Item Sourdough Bread
Protein Serving Size 1 Slice
Carb Food Item Apple
Carb Serving Size 1 medium (2 3/4-inch diameter [about 7cm] )
Carb Count 21
Protein Count 54
Protein Allowance 62
Carb Allowance 48

--------------------------------------------------------------------------------
Protein Food Item Sourdough Bread
Protein Serving Size 1 Slice
Carb Food Item Apple Juice
Carb Serving Size (canned, unsweetened), 1 cup (250mL)
Carb Count 29
Protein Count 54
Protein Allowance 62
Carb Allowance 48

--------------------------------------------------------------------------------
Protein Food Item Sourdough Bread
Protein Serving Size 1 Slice
Carb Food Item Bagel (plain, toasted)
Carb Serving Size 1 Bagel (3 1/2-inch diameter [8.9cm])
Carb Count 38
Protein Count 54
Protein Allowance 62
Carb Allowance 48

and what I need to get is instead of sourdough each day something differnt each day but not to excedd his protein value which is in this case 62.

Does that make sense I admit it is very confusing. Thank you again for all your help I greatly apprecaite all of it.

Mariane

Okay. What you are wanting to return is matrix of what the person can consume without going over a specified amount. Does that sound correct? If so, post the full query that you initially attempted and tell me in your own words what you expected to retrieve. I will then run your query and then I will attempt to generate a query based on what you said you would like to have. I have the database I just need to know what it is you want out of the database.

Thank you, I cant tell you how much I appreicate all your help. I think that what you said is exaclty correct I even have a little margin for error cause I can be as much as 10 or 15 off so say the person can consume protein of 54 for that meal I could have a food or foods that equal 44 and leave it at that for they day. I have tried many many queries but the one that seems to come the closest is this one

<%
Set Myconn=Server.CreateObject("ADODB.Connection")
Set rs=Server.CreateObject("ADODB.Recordset")
Myconn.open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= " & _
Server.MapPath("dietdb.mdb")




SQL = "Select Distinct tblcustinfo.resist1, tblcustinfo.resist2, AllFoodP.Meal1FoodP, AllFoodP.Meal1ServingP, AllFoodP.Meal1ProteinCount,AllFoodC.Meal1FoodC, AllFoodC.Meal1ServingC,AllFoodC.Meal1CarbCount From tblcustinfo, AllFoodP, AllFoodC Where tblcustinfo.resist1 >= AllFoodP.Meal1ProteinCount AND tblcustinfo.resist2 >= AllFoodC.Meal1CarbCount AND tblcustinfo.LastName= '"&LastName&"' "

RS.Open SQL, MyConn, 3, 3

On Error Resume Next
do while Not rs.eof
%>
<b>Protein Food Item</b> <%=Server.HTMLEncode(rs.Fields("Meal1FoodP").Value)%><BR></FONT></TD>
<b>Protein Serving Size</b> <%=Server.HTMLEncode(rs.Fields("Meal1ServingP").Value)%><BR></FONT></TD>
<b>Carb Food Item</b> <%=Server.HTMLEncode(rs.Fields("Meal1FoodC").Value)%><BR></FONT></TD>
<b>Carb Serving Size</b> <%=Server.HTMLEncode(rs.Fields("Meal1ServingC").Value)%><BR></FONT></TD>
<b>Carb Count</b> <%=Server.HTMLEncode(rs.Fields("Meal1CarbCount").Value)%><BR></FONT></TD>
<b>Protein Count</b> <%=Server.HTMLEncode(rs.Fields("Meal1ProteinCount").Value)%><BR></FONT></TD>
<b>Protein Allowance</b> <%=Server.HTMLEncode(rs.Fields("resist1").Value)%><BR></FONT></TD>
<b>Carb Allowance</b> <%=Server.HTMLEncode(rs.Fields("resist2").Value)%><BR></FONT></TD>
<hr>
</TR>
<%
rs.MoveNext
loop
RS.close
MyConn.Close
Set RS = Nothing
Set MyConn = Nothing
%>


It is very close the only problem with this one is it is showing the same food for protein over and over. While I actually needed it to show a different protein each day if possible, Ideally I wanted it to show enough proteins to actually come close to the allowed amount I would be thrilled if I could just get it to show a differnt protein each day.

Mariane