CODEWITHSUNDEEP
javascriptjqueryhtmlvbscriptasp-classic
chenks
chenks

Reputation: 139

HTML dropdown onChange value to be used in SQL query

I have a web form (HTML5 compliant I think!) that uses Classic ASP with VBScript with a few free text fields, a datepicker and a few drop-downs where the options are pulled from various SQL queries. (This is a stripped down basic version of the form used for testing this problem.)

One of the drop-downs needs to show options that are dependent on the selection from the previous drop-down.

I am able to grab the selected option using javascript onChange and display it on the page (which is what the current get_boiler script does, but I can't work out how to use that in the query that is used to show the required options.

The drop-down where the value is being grabbed by the OnChange is "Installed Boiler Make" and the drop-down that will use that value as part of the SQL query is "Installed Boiler Model"

I thought about pushing the value out as a querystring and reloading the form, but then I lose all the previously entered values, which is not a desired outcome.

Can anyone point me in the right direction? The query that needs to use the selected value is the recordset called "models". I have put ???? as a temporary value in the SQL query.

Hopefully I've explained this properly and used the proper tags in the post. I'm not looking for someone to do the work for me, just point me in the right direction. I've had a good look thru Stackoverflow for a problem that is the same as this but didn't find anything that was trying to do the same.

<!--#include virtual="/includes/functions.asp"-->

<!DOCTYPE html>

<%
Call OpenDB()
Call OpenRecordSet(heatcontrols, "select callmd3_field_no, callmd3_listitem from dbo.Call_More_Def3 where callmd3_link_to_def1 = 'LOG' and callmd3_field_no = '170' and callmd3_listorder not in ('1') order by callmd3_listorder")
Call OpenRecordSet(maincontrols, "select callmd3_field_no, callmd3_listitem from dbo.Call_More_Def3 where callmd3_link_to_def1 = 'LOG' and callmd3_field_no = '168' and callmd3_listorder not in ('1') order by callmd3_listorder")
Call OpenRecordSet(installtype, "select callmd3_field_no, callmd3_listitem from dbo.Call_More_Def3 where callmd3_link_to_def1 = 'LOG' and callmd3_field_no = '171' and callmd3_listorder not in ('1') order by callmd3_listorder")
Call OpenRecordSet(makes, "select make from lu_make where make not in ('-') order by make")
Call OpenRecordSet(models, "select model from lu_model where link_to_make = '????' order by model")
%>

<html>

<head>
    <title>TEST</title>
    <meta charset="UTF-8">  
    <link rel="stylesheet" href="/includes/css/installer.css"/>
    <link rel="stylesheet" href="/includes/datepicker/jquery.ui.all.css"/>
    <script type="text/javascript" src="/includes/scripts/jquery.min.js"></script>
    <script type="text/javascript" src="/includes/scripts/jquery-1.7.1.js"></script>
    <script type="text/javascript" src="/includes/scripts/jquery.ui.core.js"></script>
    <script type="text/javascript" src="/includes/scripts/jquery.ui.datepicker.js"></script>

    <script>
        $(function() {
        $( "#surveydate" ).datepicker({
                dateFormat: 'dd MM yy'
            });
        });
    </script>

    <script type="text/javascript"> 
        function get_boiler(){
            document.getElementById("boilervalue").innerHTML = document.getElementById("boilermake").value;
        return true;
        }
    </script>   

    <script type="text/javascript" src="/includes/scripts/update-validate.js"></script>
    <script type="text/javascript" src="/includes/scripts/update-showhide.js"></script>
</head>

<body>

<div class="popup-content">

    <form method="POST" name="updatejob" action="/includes/update.asp" onsubmit="return validateForm()">
        <h1>TEST FORM</h1>
        <br/>
        <b>Surveyor</b>
        <br/>
        <input type="text" name="surveyor" id="surveyor" maxlength="50" class="addjobmenudropmedium">
        <br/><br/>
        <b>Survey Date</b>
        <br/>
        <input type="text" name="surveydate" id="surveydate" class="addjobmenudropmedium">
        <br/><br/>
        <b>Heating Controls (with boiler install)</b>
        <br/>
        <select name="continst" id="continst" class="addjobmenudropsmall">
            <option value="">Select.....</option>
            <% WHILE (NOT heatcontrols.EOF) %>
                <option value="<%=heatcontrols.Fields("callmd3_listitem").value %>"><%=heatcontrols.Fields("callmd3_listitem").value %></option>
            <% heatcontrols.movenext()
            Wend %>
        </select>
        <br/><br/>
        <b>Main Heating Controls</b>
        <br/>
            <select name="heatcon" id="heatcon" class="addjobmenudrop">
                <option value="">Select.....</option>
                <% WHILE (NOT maincontrols.EOF) %>
                    <option value="<%=maincontrols.Fields("callmd3_listitem").value %>"><%=maincontrols.Fields("callmd3_listitem").value %></option>
                <% maincontrols.movenext()
                Wend %>
            </select>
        <br/><br/>      
        <b>Install Type</b>
        <br/>
            <select name="installtype" id="installtype" class="addjobmenudrop">
                <option value="">Select.....</option>
                <% WHILE (NOT installtype.EOF) %>
                    <option value="<%=installtype.Fields("callmd3_listitem").value %>"><%=installtype.Fields("callmd3_listitem").value %></option>
                <% installtype.movenext()
                Wend %>
            </select>
        <br/><br/>
        <b>Installed Boiler Make</b>
        <br/>
        <select name="boilermake" id="boilermake" class="addjobmenudrop" onChange="get_boiler()">
            <option value="">Select.....</option>
            <% WHILE (NOT makes.EOF) %>
                <option value="<%=makes.Fields("make").value %>"><%=makes.Fields("make").value %></option>
            <% makes.movenext()
            Wend %>
        </select>
        <br/><br/>
        <b>Installed Boiler Model</b>
        <br/>
        <select name="boilermodel" id="boilermodel" class="addjobmenudrop">
            <option value="">Select.....</option>
            <% WHILE (NOT models.EOF) %>
                <option value="<%=models.Fields("model").value %>"><%=models.Fields("model").value %></option>
            <% models.movenext()
            Wend %>
        </select>       
        <br/><br/>
        <b>Installed Boiler Serial Number</b>
        <br/>
        <input type="text" name="boilerserial" id="boilerserial" maxlength="50" class="addjobmenudropmedium">
        <br/><br/>
        <b>Notes</b>
        <br/>
        <textarea name="notes" id="notes" rows="5" cols="62" maxlength="1000" class="addjobtextbox"></textarea>
        <br/><br/><br/>
        <input type="submit" value="UPDATE JOB" name="Submit" class="formbutton">
        <br/><br/><br/>
    </form>

</div>

<%
Call CloseRecordSet(heatcontrols)
Call CloseRecordSet(maincontrols)
Call CloseRecordSet(installtype)
Call CloseRecordSet(makes)
Call CloseRecordSet(models)
Call CloseDB()
%>

</body>
</html>

Here is a live version of the form - http://www.chenks.co.uk/form.asp

Upvotes: 0

Views: 1302

Answers (1)

Danmoreng
Danmoreng

Reputation: 2367

I didn't program with ASP yet, but I guess it works the same way you would do it with PHP:

Create a new page called something like "ModelsDropdown.asp" with the content you need for the dropdown:

<!--#include virtual="/includes/functions.asp"-->

<%
Call OpenDB()
Call OpenRecordSet(models, "select model from lu_model where link_to_make = url_parameter_make order by model")
%>

    <option value="">Select.....</option>
    <% WHILE (NOT models.EOF) %>
        <option value="<%=models.Fields("model").value %>"><%=models.Fields("model").value %></option>
    <% models.movenext()
    Wend %>

<%
Call CloseRecordSet(models)
Call CloseDB()
%>

How you use URL Parameters in ASP I don't know, but I hope you do. When you open http://www.chenks.co.uk/ModelsDropdown.asp?make=some_make it should spit out just the select options for the passed make.

Now you can get this content and put it into your original form via JavaScript/Ajax:

$("#boilermake").on("change", function(){
    // Use jQuery get to call your new page with the selected make as parameter
    $.get("/ModelsDropdown.asp", {make: $("#boilermake")[0].value}, function(data){
        // paste the select options into the DOM
        $("#boilermodel").html(data);
    });
});

Upvotes: 1

Related Questions