Doubt - Dependent Combobox with Classic ASP

Question

I have two tables in SQL: One of groups that has two columns: id and group

One of products that has three columns: id, group and product

In the products table, the column "group" is equivalent to the column "id" of the groups table.

I am trying to make an inclusion form in a third table of entries (columns: group, product and input).

In this form, there is a combobox in which the user selects the Group and, when selecting the Group, only the Products related to it should appear in a second combobox. (similar to state/city combobox).

The web page is connecting right in the database, but I’m not able to make the dependent combobox.

I built it this way:

Combo:

<%
SET RS_grupo = conexao.execute("SELECT * FROM [dbo].[nova_pd_grupos] ORDER BY grupo")

grupo1= RS_grupo("grupo")
idgrupo=RS_grupo("id")
grupo=request.querystring("grupo1")

%>

<select class="custom-select custom-select-sm" name="idgrupo" onChange="location.href('index.asp?idgrupo='+formulario.idgrupo.options[formulario.idgrupo.selectedIndex].value)">
                                <% while not RS_grupo.eof%>
                                <% if grupo <>"" then
                                   if grupo = grupo1 then
                                   response.write "<option value="&idgrupo&"&grupo1="&grupo1&" selected>"&grupo1&"</option>"
                                   elseif grupo <> grupo1 then
                                   response.write "<option value="&idgrupo&"&grupo1="&grupo1&">"&grupo1&"</option>"
                                   end if
                                   else 
                                   response.write "<option value="&idgrupo&"&grupo1="&grupo1&">"&grupo1&"</option>"
                                   end if

                                   RS_grupo.movenext
                                   wend
                                   RS_grupo.close


                                %>


                            </select>

Combo of products:

<% if request.querystring("idgrupo") <> "" then %>
                                <select class="custom-select custom-select-sm" name="produto">
<%  SET RS_produto = conexao.execute("SELECT * FROM [dbo].[nova_pd_produtos] where grupo = "&request.querystring("idgrupo")&" order by produto asc;")
                                while not RS_produto.eof

                                   grupo = request.querystring("grupo")
                                   produto1 = RS_produto("produto")
                                %>
                                <option  value="<%=produto1%>,<%=grupo%>">
                                    <%=produto1%>
                                </option>

                                     <%
                                        RS_produto.movenext
                                        wend
                                        RS_produto.close
                                        %>
                                </select>
                                <% end if %>

However, in the group list appear 13 options, all with the name of the first option. And there’s nothing on the list.

Can someone help me? ASP classic (vbscript).

Thank you!

Answer 1

There are two problems with the code:

Place variables from the bank at the beginning of the first while, otherwise they will not be changed and will have the same value within the loop:

<%
while not RS_grupo.eof
   grupo1= RS_grupo("grupo")
   idgrupo=RS_grupo("id")
   ...
%>

And the onchange is with wrong syntax. The correct is:

onchange="location.href = 'index.asp?idgrupo='+this.value"

The this.value will return the value of option selected.