Populate fields in webpage based on option selecte

Question

I have a page that has a drop down combo box, the values for which are coming from a database. next to the combo box are two text boxes.

now based on the option selected from the drop down list, I need to go to the DB and get relevant data from the db and populate the two text boxes.

There are other fields in the page that are straight forward, but i don't know how to go about with this situation.

any link/sample code/reading material / or any other form of help will be greatly appreciated.

Thanks,

Ok am thinking loud here, here's the sample code

   <%
    While not rsRecordSet.EOF
    %>
    <OPTION VALUE="<%=rsRecordSet("FldID")%>"><%=rsRecordSet("FldName")%></OPTION>
    <%
    rsRecordSet.MoveNext
    Wend
    Set rsRecordSet= nothing
    %>

I need the Value i.e. FldID as the reference to the data selected, but if i can tag the rest of the data somewhere. e.g. the other two fields need to show FldAge and FldGender. I can get those in the rsRecordSet, and as i am populating the Option i can populate some tag and some how call it later depending on the option selected from the combo. Just thinking loud here. Thank,s

Answer 1

There are of course quite a few ways to do this. This example will submit the form when you select an option then display the age and gender. (query, connection, etc will need to be changed to suit your objects)

<form action="" method="get">
    <select name="id" onchange="this.form.submit()">
<%
While not rsRecordSet.EOF
%>
        <OPTION VALUE="<%=rsRecordSet("FldID")%>"><%=rsRecordSet("FldName")%></OPTION> 
<% 
    rsRecordSet.MoveNext 
Wend 
Set rsRecordSet= nothing 
%>
    </select> 
</form>
<%
If Request.QueryString("id") <> "" Then
'Open recordset to retrieve age and gender
rsRecordSet2.Open "SELECT FldAge, FldGender FROM table WHERE FldID = " & CInt(Request.QueryString("id")), connection
If Not rsRecordSet2.EOF Then
%>
    Age: <%= rsRecordSet2("FldAge") %><br />
    Gender: <%= rsRecordSet2("FldGender") %><br />
<%
End If
rsRecordSet2.Close
Set rsRecordSet2 = Nothing
%>

It is better to do this sort of thing with AJAX, and this is easy to do even in Classic ASP, particularly if you can use jQuery. I can post an example if you would like to go this way.

Update with AJAX solution:

Okay, here is an example using jQuery which I recommedn you to use as doing Ajax in JavaScript will take me too long...

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
<script type="text/javascript">
    $(document).ready(function () {
        $("#id").change(function() {
            $.get('ajax.asp?id='+$("#id").val(), function(data) {
                var fields = data.split(',');
                $("#age").html(fields[0]);
                $("#gender").html(fields[1]);
            }); 
        });
    });
</script>

<select id="id">
<%
While not rsRecordSet.EOF
%>
    <OPTION VALUE="<%=rsRecordSet("FldID")%>"><%=rsRecordSet("FldName")%></OPTION>
<%
    rsRecordSet.MoveNext
Wend
Set rsRecordSet= nothing
%>
</select>
<div id="age"></div>
<div id="gender"></div>

Create a script called ajax.asp. You will need to add all your database connection stuff in there. Then open a recordset based on the ID passed and return the age and gender as a comma separated string, like this:

<%
'Open recordset to retrieve age and gender 
rsRecordSet2.Open "SELECT FldAge, FldGender FROM table WHERE FldID = " & CInt(Request.QueryString("id")), connection 
If Not rsRecordSet2.EOF Then 
    Response.Write(rsRecordSet2("FldAge") & "," & rsRecordSet2("FldGender"))
End If 
rsRecordSet2.Close 
Set rsRecordSet2 = Nothing 
%>