I have a problem where "There is a nice sub routine in there but I think 
there is too much code and what is happening is that for selected 
departments, they end up showing several times (not in the database but 
just in the displayed multi option box at the page - so it's a coding 
issue)."

Thanks a million!!

Code:
<%
function showAddDept(belong_to,lvl)
if lvl <= 10 then
sql_select = "SELECT Store_Items_AddDepts.Item_ID,Store_Dept.

Department_ID, Store_Dept.Department_Name,Store_Items_AddDepts.ref_id 
FROM Store_Dept left 

join Store_Items_AddDepts on Store_Items_AddDepts.department_id = 
store_dept.department_id 

WHERE belong_to = " & belong_to & " and store_dept.store_id = " & 
store_id & " ORDER BY 

Department_Name"
response.write sql_select & "<BR>"
set subdeptRS = conn_store.execute(sql_select)
for index = 1 to lvl
sym = sym & "-->"
next
do until subdeptRS.eof
%>
<% if subdeptRS("ref_id") <> "" and subdeptRS("item_id") = 

item_id then%>
<% selected = "selected" %>
<% selected_flag = "true" %>
<% else %>
<% selected = "" %>
<% end if %>
<option <%= selected %> value="<%= subdeptRS("Department_ID

") %>"><%=sym%><%= subdeptRS("Department_Name") %> </option>
<%
if lvl < 10 then
'call showAddDept(subdeptRS("Department_ID"),lvl+1)
end if
%>
</option> 

<% 
subdeptRS.movenext
loop
end if
end function
%>
<% sql_select = "SELECT Store_Dept.Department_ID, Store_Dept.

Department_Name,Store_Items_AddDepts.ref_id FROM Store_Dept left join 
Store_Items_AddDepts 

on Store_Items_AddDepts.department_id = store_dept.department_id WHERE 
Belong_to = 0 AND 

store_dept.store_id = " & store_id & " ORDER BY Department_Name" %>
<% rs_Store.open sql_select, conn_store %> <% selected_flag = "

false" %> <select name="Additional_Departments">
<% Do while Not rs_store.EOF %>
<% if rs_store("ref_id") <> "" then %>
<% selected = "" %> <% selected = "selected" %>
<% selected_flag = "true" %>
<% else %>
<% selected = "" %> 
<% end if %>
<option <%= selected %> value="<%= rs_store("Department_ID") 

%>"> 
<%= rs_store("Department_Name") %> </option>
<% call showAddDept(rs_store("Department_ID"),1) %>
<% rs_store.MoveNext %>
<% Loop %>
<% rs_store.close %>
<% if selected_flag="false" then %>
<option selected value="0">Un Assigned</option>
<% End If %>
</select>

what u want to know
is thecode is not working
or u want to know what the code does

This code is really hard to read the way it is - you need to change it to 
make it more readable, i.e. I took a shot at making it indented and 
eliminating the dozens of redundant <% switches, etc.

When you redo the code to be more readable and tell us what it does when it 
runs you want to fix I and others might have a bettor shot at fixing it.

<%
function showAddDept(belong_to,lvl)
         if lvl <= 10 then
                 sql_select = "SELECT 
Store_Items_AddDepts.Item_ID,Store_Dept.Department_ID, 
Store_Dept.Department_Name,Store_Items_AddDepts.ref_id
                 FROM Store_Dept left
                 join Store_Items_AddDepts on 
Store_Items_AddDepts.department_id 
                 store_dept.department_id
                 WHERE belong_to = " & belong_to & " and 
store_dept.store_id = " &
                 store_id & " ORDER BY

                 Department_Name"
                 response.write sql_select & "<BR>"
                 set subdeptRS = conn_store.execute(sql_select)
                 for index = 1 to lvl
                         sym = sym & "-->"
                 next
                 do until subdeptRS.eof
                         if subdeptRS("ref_id") <> "" and 
subdeptRS("item_id") =item_id then%>
                                 selected = "selected"
                                 selected_flag = "true"
                         else
                                 selected = ""
                         end if
                 response.write "<option " & selected & 
"value='subdeptRS("Department_ID") '>
                 response.write sym & subdeptRS("Department_Name") & 
"</option>"
                 if lvl < 10 then
'                       call showAddDept(subdeptRS("Department_ID"),lvl+1)
                 end if
                 response.write "</option>"
                 subdeptRS.movenext
                 loop
         end if
end function

         sql_select = "SELECT Store_Dept.Department_ID, Store_Dept.
         Department_Name,Store_Items_AddDepts.ref_id FROM Store_Dept left join
         Store_Items_AddDepts
         on Store_Items_AddDepts.department_id = store_dept.department_id 
WHERE
         belong_to = 0 AND
         store_dept.store_id = " & store_id & " ORDER BY Department_Name"
         rs_Store.open sql_select, conn_store
         selected_flag = "false"
                 %>
         <select name="Additional_Departments">
         <%
          Do while Not rs_store.EOF
                 if rs_store("ref_id") <> "" then
                         selected = ""
                         selected = "selected"
                         selected_flag = "true"
                 else
                         selected = ""
                 end if
                 response.write "<option " & selected & " value='" 
&  rs_store("Department_ID")  & "'">"
                 response.write rs_store("Department_Name") & "</option>"
                 call showAddDept(rs_store("Department_ID"),1)
                 rs_store.MoveNext
         Loop
         rs_store.close
         if selected_flag="false" then%>
                 <option selected value="0">Un Assigned</option>
         <% End If %>
         </select>


At 05:16 AM 1/17/2003 +0000, you wrote:
>I have a problem where "There is a nice sub routine in there but I think
>there is too much code and what is happening is that for selected
>departments, they end up showing several times (not in the database but
>just in the displayed multi option box at the page - so it's a coding
>issue)."
>
>Thanks a million!!
>
>Code:
><%
>function showAddDept(belong_to,lvl)
>if lvl <= 10 then
>sql_select = "SELECT Store_Items_AddDepts.Item_ID,Store_Dept.
>
>Department_ID, Store_Dept.Department_Name,Store_Items_AddDepts.ref_id
>FROM Store_Dept left
>
>join Store_Items_AddDepts on Store_Items_AddDepts.department_id 
>store_dept.department_id
>
>WHERE belong_to = " & belong_to & " and store_dept.store_id = " &
>store_id & " ORDER BY
>
>Department_Name"
>response.write sql_select & "<BR>"
>set subdeptRS = conn_store.execute(sql_select)
>for index = 1 to lvl
>sym = sym & "-->"
>next
>do until subdeptRS.eof
>%>
><% if subdeptRS("ref_id") <> "" and subdeptRS("item_id") 
>
>item_id then%>
><% selected = "selected" %>
><% selected_flag = "true" %>
><% else %>
><% selected = "" %>
><% end if %>
><option <%= selected %> value="<%= subdeptRS("Department_ID
>
>") %>"><%=sym%><%= subdeptRS("Department_Name") %> </option>
><%
>if lvl < 10 then
>'call showAddDept(subdeptRS("Department_ID"),lvl+1)
>end if
>%>
></option>
>
><%
>subdeptRS.movenext
>loop
>end if
>end function
>%>
><% sql_select = "SELECT Store_Dept.Department_ID, Store_Dept.
>
>Department_Name,Store_Items_AddDepts.ref_id FROM Store_Dept left join
>Store_Items_AddDepts
>
>on Store_Items_AddDepts.department_id = store_dept.department_id WHERE
>Belong_to = 0 AND
>
>store_dept.store_id = " & store_id & " ORDER BY Department_Name" %>
><% rs_Store.open sql_select, conn_store %> <% selected_flag = "
>
>false" %> <select name="Additional_Departments">
><% Do while Not rs_store.EOF %>
><% if rs_store("ref_id") <> "" then %>
><% selected = "" %> <% selected = "selected" %>
><% selected_flag = "true" %>
><% else %>
><% selected = "" %>
><% end if %>
><option <%= selected %> value="<%= rs_store("Department_ID")
>
>%>">
><%= rs_store("Department_Name") %> </option>
><% call showAddDept(rs_store("Department_ID"),1) %>
><% rs_store.MoveNext %>
><% Loop %>
><% rs_store.close %>
><% if selected_flag="false" then %>
><option selected value="0">Un Assigned</option>
><% End If %>
></select>
>---
>[This E-mail scanned for viruses by Declude Virus]


---
[This E-mail scanned for viruses by Declude Virus]

The code works, however it will like return something in the database
multiple times...so like the menu list will return lets say "database
item 1" 6 times, but in the database, there's only 1 entry.
Thanks for your thelps

-----Original Message-----
From: sashi@a... [mailto:sashi@a...]
Sent: 2003=C4=EA1=D4=C217=C8=D5 7:48
To: ASP Databases
Subject: [asp_databases] Re: database menulist help!

what u want to know
is thecode is not working
or u want to know what the code does