dynamic radio button list problem 3

[gchaves]

Greetings!

I am trying to create a dynamic list of radio buttons from a table in my database. I want the table to have however many rows necessary, but be three table cells across. I think I have something close to working, just that it is not really working. I am getting the same record three times in the same row before it moves on to the next record in the table. What I need is to have one record per table data cell. When the row reaches three cells, it starts another row, continuing on with the next record in the table. Here is my code...can anyone take a peek and let me know where my logic is a little off-base?

		Response.Write "<tr><td align=""left"" colspan=""5""><b>Check the one category that best describes your current area of employment:</b></td></tr>"
'-------------------------------------------------------------
					'dynamic dropdown for Position list starts here
					'create recordset object
					Dim a,b
					Dim catCount
					Dim sqlMembershipEmploymentCatAdd
					Dim objRSMembershipEmploymentCatAdd
					Set objRSMembershipEmploymentCatAdd = Server.CreateObject("ADODB.Recordset")

					'create SQL statement to get all fields for all records in the state table of the database 
					'and order the records by ID number

						sqlMembershipEmploymentCatAdd = "SELECT * FROM membership_employment_category ORDER BY category;"

						'open the recordset
						objRSMembershipEmploymentCatAdd.Open sqlMembershipEmploymentCatAdd, objConn, 2, 3

						objRSMembershipEmploymentCatAdd.MoveFirst
	
						Response.Write "<tr><td colspan=""4"">"
						Response.Write "<table border=""1"">"

						'catCount = objRSMembershipEmploymentCatAdd.RecordCount

						Do While Not objRSMembershipEmploymentCatAdd.EOF
	
						Response.Write "<tr>"

						a=0
						b=3

						For a=1 to b
						Response.Write "<td align=""left"" valign=""top"">"
						Response.Write "<input type=""radio"" name=""ID_employment_cat"" value=" & Chr(34) & objRSMembershipEmploymentCatAdd("ID_employment_cat") & Chr(34) & "></td><td width=""200"">" & objRSMembershipEmploymentCatAdd("category") & VbCrLf
						Response.Write "</td>"
						
						b=b+1
						
						Next

						Response.Write "</tr>"

						objRSMembershipEmploymentCatAdd.MoveNext

						Loop

						Response.Write "</table>"
						Response.Write "</td></tr>"

'-------------------------------------------------------------

Any help would be greatly appreciated!

Thanks!
G

 

[kaht]

You are incrementing b inside your for loop - don't do this. Additionally, you are calling the movenext method outside of the loop, you need to do it inside:

For a=1 to b
Response.Write "<td align=""left"" valign=""top"">"
Response.Write "<input type=""radio"" name=""ID_employment_cat"" value=" & Chr(34) & objRSMembershipEmploymentCatAdd("ID_employment_cat") & Chr(34) & "></td><td width=""200"">" & objRSMembershipEmploymentCatAdd("category") & VbCrLf
Response.Write "</td>"
[b][COLOR=red]objRSMembershipEmploymentCatAdd.MoveNext[/color][/b]
Next

-kaht

How much you wanna make a bet I can throw a football over them mountains?

 

[gchaves]

Awesome! That did the trick! I guess I was just looking at it way too long.

OK. Here's is my next problem that I've been killing myself over. If the total number of records isn't divisible by 3, then the for/next loop throws an exception error. How do I set my code up to test for the remaining number of records and set my col spaces to adjust for them?

Here's the error...

error '80020009'
Exception occurred.

/admin/includes/addsurvey.asp, line 645

Thanks again for the help! I appreciate the second set of eyes!

G

 

[kaht]

Your loop-within-a-loop is the problem. You can eliminate the for-loop altogether just by keeping track with a counter on your while loop. Try something like this:

[B][I]loopCounter = 0[/I][/B]
Do While Not objRSMembershipEmploymentCatAdd.EOF
   [B][I]if (loopCounter mod 3) = [COLOR=red]0[/color] then[/I][/B]
      Response.Write "<tr>"
   [B][I]end if[/I][/B]
   Response.Write "<td align=""left"" valign=""top"">"
   Response.Write "<input type=""radio"" name=""ID_employment_cat"" value=" & Chr(34) & objRSMembershipEmploymentCatAdd("ID_employment_cat") & Chr(34) & "></td><td width=""200"">" & objRSMembershipEmploymentCatAdd("category") & VbCrLf
   Response.Write "</td>"
   [B][I]if (loopCounter mod 3) = [COLOR=red]2[/color] then[/I][/B]
      Response.Write "</tr>"
   [B][I]end if[/I][/B]
   [B][I]loopCounter = loopCounter + 1[/I][/B]
   objRSMembershipEmploymentCatAdd.MoveNext
Loop

-kaht

How much you wanna make a bet I can throw a football over them mountains?

 

[gchaves]

Thanks! Again, your code works like a charm. The only thing is that it just makes one big table data cell, rather than split each record up into its own cell. I've tried switching the <table> tags, but still have been unsuccessful. Any ideas?

 

[DotNetGnat]

Try this code...earlier provided by Sheco

<table>
<%
  count = 0
  myMod = 0
  do while not rs.EOF
    myMod = count mod 3
    
    'Do we need to start a new row?
    if (myMod = 0) then Response.Write "<tr>"

    'Write table data cell here
    Response.Write "<td>" & rs("blah") & "</td>"

    'Do we need to end a the table row?
    if (myMod = 2) then Response.Write "</tr>"

    count = count + 1
    rs.MoveNext
  loop

  'finish the last table row here
  select case myMod
    case 0
      'need 2 empty cells and close table row
      Response.Write "<td>&nbsp;</td><td>&nbsp;</td></tr>"
    case 1
      'need 1 empty cell and close table row
      Response.Write "<td>&nbsp;</td></tr>"
    case 2
      'nothing else needed
  end select
%>
</table>

-DNG

 

[gchaves]

Awesome! That works out perfectly! Thank you, everyone, for all of your help! I really learned a couple of valuable things here!

G

 

[gchaves]

OK. Another question. I am creating a page that will pull up a user's entry from a database and list it in a form that the user can modify, if they choose to. The issue I am having is that I am trying to list the selections for a list of check boxes using a many to many relationship. I can get the list to work fine...if a user has selected at least one of the check boxes. Since the check boxes aren't required, the user doesn't have to select any. If none are selected, I get the following error...

ADODB.Recordset error '800a0bcd' 
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record. 

/admin/includes/editsurveys.asp, line 789

I know I have been staring at this way too long, so I am asking for help. Looking at my code below (which utilizes some of the code provided by the above experts on this page), is there an easy solution that will render all of the checkboxes even if none were selected by the user?

						'dynamic dropdown for services check box list starts here
						Dim sqlMembershipExpertiseEdit
						Dim objRSMembershipExpertiseEdit
						Set objRSMembershipExpertiseEdit = Server.CreateObject("ADODB.Recordset")

						'create SQL statement to get all fields for all records in the designation table of the database 
						'and order the records by ID number
		
						sqlMembershipExpertiseEdit = "SELECT membership_expertise.ID_expertise, membership_expertise.expertise FROM membership_expertise;"
						
						'open the recordset
						objRSMembershipExpertiseEdit.Open sqlMembershipExpertiseEdit, objConn, 2, 3
				
						objRSMembershipExpertiseEdit.MoveFirst

						Response.Write "<tr><td align=""left"" colspan=""4"">"
						Response.Write "<table border=""1"">"
						
						Dim sqlExpertiseEdit
						Dim	objRSExpertiseEdit 				
						Set objRSExpertiseEdit = Server.CreateObject("ADODB.Recordset")
						
						sqlExpertiseEdit = "SELECT survey_expertise_match.ID_member, Max(IIf([survey_expertise_match].[ID_expertise]=[membership_expertise].[ID_expertise],1,0)) AS Exp, membership_expertise.expertise, membership_expertise.ID_expertise FROM membership_expertise, survey_expertise_match WHERE (((ID_member)=" & surveySrchTerm & ")) GROUP BY survey_expertise_match.ID_member, membership_expertise.expertise, membership_expertise.ID_expertise ORDER BY survey_expertise_match.ID_member, membership_expertise.ID_expertise;"

						objRSExpertiseEdit.Open sqlExpertiseEdit, objConn, 2, 3

						objRSExpertiseEdit.MoveFirst

							Do While Not objRSExpertiseEdit.EOF
							Dim chkBox,expID
							chkBox=""
							If objRSExpertiseEdit("Exp")=1 Then 
								chkBox=" CHECKED"
								Else
								chkBox=""
							End If
								expID=objRSExpertiseEdit("Exp")

							Dim expEditMod, expEditCount
							expEditMod = expEditCount mod 2
    
    							'Do we need to start a new row?
    							If (expEditMod = 0) Then
									Response.Write "<tr>"
								End If

							Response.Write "<td align=""right"" valign=""top"">"
							Response.Write "<input type=""checkbox"" name=""ID_expertise"" value=" & Chr(34) & objRSExpertiseEdit("ID_expertise") & Chr(34) & chkBox & "></td><td colspan=""2"" width=""300"">" & objRSExpertiseEdit("expertise") & "</td>" & VbCrLf
								chkBox=""
	
	    						'Do we need to end a the table row?
    							If (expEditMod = 2) Then
									Response.Write "</tr>"
								End If
	
    						expEditCount = expEditCount + 1
							objRSExpertiseEdit.MoveNext
						
							Loop

  							'finish the last table row here
  								select case expMod
    								'case 0
      								'need 2 empty cells and close table row
      								'Response.Write "<td colspan=""2"">&nbsp;</td><td colspan=""2"">&nbsp;</td></tr>"
    								case 0
      									'need 1 empty cell and close table row
      									Response.Write "<td colspan=""2"">&nbsp;</td></tr>"
    								case 1
      									'nothing else needed
  									end select
							
						Response.Write "</td></tr></table>"
						Response.Write "</td></tr>"
'end dynamic loop for

I appreciate any and all or your help!

Thanks,
G

 

[Chopstik]

I assume that the problem lies in the following code:

sqlExpertiseEdit = "SELECT survey_expertise_match.ID_member, Max(IIf([survey_expertise_match].[ID_expertise]=[membership_expertise].[ID_expertise],1,0)) AS Exp, membership_expertise.expertise, membership_expertise.ID_expertise FROM membership_expertise, survey_expertise_match WHERE (((ID_member)=" & surveySrchTerm & ")) GROUP BY survey_expertise_match.ID_member, membership_expertise.expertise, membership_expertise.ID_expertise ORDER BY survey_expertise_match.ID_member, membership_expertise.ID_expertise;"

If the "surveySrchTerm" is the value that is sometimes empty if the user does not choose any checkboxes, then the solution should be fairly simple. Do as follows:

sqlExpertiseEdit = "SELECT survey_expertise_match.ID_member, Max(IIf([survey_expertise_match].[ID_expertise]=[membership_expertise].[ID_expertise],1,0)) AS Exp, membership_expertise.expertise, membership_expertise.ID_expertise FROM membership_expertise, survey_expertise_match "
[COLOR=red]if surveySrchTerm <> "" then
  "WHERE (((ID_member)=" & surveySrchTerm & ")) "
end if[/color]
"GROUP BY survey_expertise_match.ID_member, membership_expertise.expertise, membership_expertise.ID_expertise ORDER BY survey_expertise_match.ID_member, membership_expertise.ID_expertise;"

If this is not the problem, then you need to specify the line of code that is the problem or only post the relevant code, else it is like searching for a needle in a haystack.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill