Populate drop-down menu with two recordsets 1

[apex82]

I have two database tables. The link between the two is the Business.UnitID and Unit.Unit_ID.

Business:
Business_ID | Business Code | Business Description | UnitID | LogoID

Unit:
Unit_ID | Unit Code | Unit Description

At the moment on my page I have a drop-down menu that only lists the [Unit code] and [Unit description].

<select name="Unit" id="Unit">
        <%
	str_SQL = "SELECT * FROM [Unit] ORDER BY [Unit Code] ASC;"
	
	Set obj_RS1 = obj_CN.Execute(str_sql, adBoolean)
		
    ' Build the menu
	While Not obj_RS1.EOF
		
		str_Unit = obj_RS1("Unit code")
		str_UnitDescription = obj_RS1("Unit description")
        str_Option = "<option value=""" & """#Selected#>" & str_UnitID & " - " & " - " & str_UnitDescription & "</option>"
        Response.Write vbtab & str_Option & vbcrlf
	    obj_RS1.MoveNext
	Wend
%>
      </select>

Basically I want to change drop-down list so the [Business code] is grouped by the [Unit code] from the Unit table.

I am able to display the [Unit code] from the recordset separately:

<%
str_sql = "SELECT [Unit Code] FROM [Unit];"

Set obj_RS1 = obj_CN.Execute(str_sql, adBoolean)

do until obj_RS1.EOF
for each x in obj_RS1.fields
  Response.Write(x.value & "<br />")
next
  Response.Write("<br />")
  obj_RS1.MoveNext
loop
%>

Does anyone know how I can piece the two together so I end up with for example:

<optgroup label="Unit code 1">
    <option value="Business code 1"> Business code 1</option>
  </optgroup>

Thanks.

 

[apex82]

I can achieve a version of this, which works:

<select name="Unit" id="Unit">
        <%
	' Business
	
	sql = "SELECT * FROM Business INNER JOIN Unit ON Business.UnitID = Unit.Unit_ID ORDER BY [Unit Code] ASC;"

Set rs = obj_CN.Execute(sql, adBoolean)

			
str_Unit = rs("Unit code")
str_Business = rs("Business code")
		
    ' Build the menu
	While Not rs.EOF
		
		str_UnitID = rs("Unit_ID")
		str_Unit = rs("Unit code")
		str_UnitDescription = rs("Unit description")

        str_BusinessID = rs("Business_ID")
		str_BusinessCode = rs("Business code")

		str_Optgroup = "<optgroup label=""" & str_Unit & " - " & str_UnitDescription & """#Selected#>" & str_UnitID & " - " & str_Unit & " - " & str_UnitDescription & "</option>"
		Response.Write vbtab & str_Optgroup & vbcrlf
		str_Option = "<option value=""" & str_BusinessID & """#Selected#>" & str_BusinessCode & "</option>"
        Response.Write vbtab & str_Option & vbcrlf
	    rs.MoveNext
	Wend
	
%>
	</optgroup>
      </select>

The only fault with the above is when there is more than one Business Code per Unit Code I have repeats of the Unit Code Optgroup label e.g:

Unit Code AA
a1
Unit Code AA
a2

I would like to display as

Unit Code AA
a1
a2

Can anyone help?

Thanks.

 

[foxbox]

Something like this?

<select name="Unit" id="Unit">
<%
cSQL = "SELECT u.uid, u.ucode, u.udesc, b.bid, b.bcode FROM Business b" & _
       "INNER JOIN Unit u ON b.UnitID = u.Unit_ID "& _
       "ORDER BY u.ucode, b.bcode"
Set rs = obj_CN.Execute(sql)

nUunitID = -1

do while not rs.EOF
        
        str_UnitID = rs("uid")
        str_Unit = rs("ucode")
        str_UnitDescription = rs("udescr")"

	if nUnitId <> str_UnitID then
 	 if nUnitId <> -1 then
	  response.write "</optgroup>"
	 end if
         response.write "<optgroup label=""" & str_Unit & ">"
	 nUunitID = str_UnitID
	end if

        response.write "<option value=""" & str_BusinessID & """#Selected#>" & str_BusinessCode & "</option>"

        rs.MoveNext
loop
%>
</optgroup>
</select>

NOT TESTED, i just wrote it down in more or less pseudocode.
So everythime there is a unitid change we close teh optgroup, and open a new one (expet for the first group)

 

[apex82]

Thanks really appreciate it.

I have tried to change this into working code:

<%
str_SQL = "SELECT Unit.Unit_ID, Unit.[Unit code], Unit.[Unit Description], Business.Business_ID, Business.[Business Code], Business.[Business Description]  FROM Business " & _
       "INNER JOIN Unit ON Business.UnitID = Unit.Unit_ID "& _
       "ORDER BY Unit.[Unit Code], Business.[Business code]"
		Set rs = obj_CN.Execute(str_SQL)

nUnitID = -1

do while not rs.EOF

        str_UnitID = rs("Unit_ID")
        str_Unit = rs("Unit code")
	str_UnitDescription = rs("Unit Description")

	str_BusinessID = rs("Business_ID")
	str_BusinessCode = rs("Business code")
        
    	if nUnitID <> str_UnitID then
        if nUnitID <> -1 then
        Response.Write "</optgroup>"
        end if
        Response.Write "<optgroup label=""" & str_Unit & ">"
        nUnitID = str_UnitID
        end if

        Response.Write "<option value=""" & str_BusinessID & """#Selected#>" & str_BusinessCode & "</option>"

        rs.MoveNext
loop
%>

It is showing the drop-down as:

F1><option value=
F2><option value=
F3><option value=
F4><option value=
F5><option value=
aa
bb
cc
F6><option value=

Am I missing something simple in order to get this working?

Thanks.

 

[apex82]

Yes I was missing something simple!

response.write "<optgroup label=""" & str_Unit & """>"

Thanks again foxbox!