We help IT Professionals succeed at work.

Dynamically Populating Drop Down From SQL Server Table using ASP

kibbs
kibbs asked
on
3,076 Views
Last Modified: 2008-01-09

Using ASP I need to make a dynamic drop down box where the slected value of the firsst drop down (category) dynamically populates the values of the second drop down box (SubCategories) based on the relationship in the database tables.  Does anyone have code or query that might accomplish this?

I've got two tables
TABLE #1   CATEGORIES
Columns
ID
Category

Opened
ID     Category
100   Catagory1
200   Catagory2
300.. etc..  

TABLE #2 SUBCATEGORIES
Columns
ID           - where the next available CatoryID incremented by +1
BindTo    - is actually the category ID I want to bind to
SubCategory

Opened
ID     Bindto  SubCategory
101  100      SubCat1
102  100      SubCat2
103..
201  200      SubCat3,1
202  200      SubCat4,2
203  200      SubCat5,3
204..        
 
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2005

Commented:
Yes, I wrote an article on this here:

http://www.fairfieldconsulting.com/asp_multipleSelects.asp

FtB
CERTIFIED EXPERT
Top Expert 2005
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Commented:
Fritz, beat me to it, his example is very good.

Neal.

Author

Commented:
I cant get any of these scripts to work.

Here ismy SQL query.  Neal I liked that java script example you gave me, ritz looks good but it involves three drop downs, I only have two , and half-a-brain.  When I start chopping code shi- happens.  

This is the SQL I use

select category.categoryName, subcategory.subcategory
from subcategory, category
Where subcategory.bindto = category.id
ORDER BY category.categoryName, subcategory.bindto

Produces these results
CATEGORY                SUBCATEGORY

Business Resources      Corporate Training
Business Resources      Business Networking
Business Resources      Conferences
Business Resources      Import Export
Blogs                      Teacher Blogs
Blogs                      Business Blogs
Blogs                      Political Blogs

I'm geting an error using the java script
Microsoft VBScript compilation error '800a0409'

Unterminated string constant

Here is the code:

<html>
<head>
<title>Multiple Dynamic Drop Down Boxes - Javascript</title>
<script language=javascript >
var categorys = new Array(); // Array of all categorys in database
var Cities = new Array(); // Array of Arrays - One array for each category, each array holds the subcategory names for that category

function addcategory(category) {
    // Determine current length of the categorys Array
    v = categorys.length;
    // increase the size of the categorys array by 1
    categorys.length ++;
    // Store the new category information into the array (note using the old length because arrays start at 0
    categorys[v] = category;
    // Determine the size of the Cities array
    v = Cities.length;
    // increase the size of the Cities array by 1
    Cities.length ++;
    // create a new array in the Cities array for the new category
    Cities[category] = new Array();
}

function addsubcategory(category,subcategory) {
    // Determine the current length of the subcategory[category] array.
    // that is the sub array of Cities for the specified category
    v = Cities[category].length;
    // Increase the size of the subcategory[category] array by 1.
    Cities[category].length ++;
    // Add the new subcategory to the array
    Cities[category][v] = subcategory;
}

function loadcategoryList() {
    // Clear the category options
    var ctrlcategory = document.frmAddress.category;
    ctrlcategory.options.length = 0;
    // Load the category options
    for (i=0;i<categorys.length;i++) {
        ctrlcategory.options[i] = new Option(categorys[i],categorys[i]);
    }
}

function loadsubcategoryList() {
    // Determine the Selected category
    var ctrlcategory = document.frmAddress.category;
    var selcategory = ctrlcategory.options[ctrlcategory.selectedIndex].value;
    // Clear the subcategory Options
    var ctrlsubcategory = document.frmAddress.subcategory;
    ctrlsubcategory.options.length = 0;
    // Load the subcategory Options
    for (i=0;i<Cities[selcategory].length;i++) {
        ctrlsubcategory.options[i] = new Option(Cities[selcategory][i]);
    }
}
</script>

</head>
<body onload="loadcategoryList();loadsubcategoryList();">
<h2>Dynamically Linked Drop-Down Boxes - JavaScript</h2>
<%
' Open Database and setup a recordset with categorys Listed.
' Note the Distinct option in the SQL categoryment. This will limit each category to showing only once in the recordset.
set conn = server.createobject("ADODB.Connection")
Conn.open("driver={SQL Server};server=USA\SERVER;uid=UserID;pwd=password;database=parked")
strSQL = "select category.categoryName, subcategory.subcategory from subcategory, category Where subcategory.bindto = category.id
ORDER BY category.categoryName, subcategory.bindto"
set rs = conn.execute(strSQL)
' If there are no records found, display a message. Otherwise display our form.
if rs.eof then
    response.write("No Addresses Found")
    rs.close
    set rs=nothing
    response.end
end if
' Here is where we load up the javascript arrays:
' If the category not the same as the previous record then add it to the
' categorys array and create a new array to hold the cities for the new category
' then add the subcategory to the new category array
' if the category is the same as the previous record, then check to see if the
' subcategory is the same as the previous record. If not then add it to the array
' for the correct category.
strcategory = ""
strsubcategory = ""
do until rs.eof
    if not rs("category") = strcategory then
        response.write("<script>addcategory('" & rs("category") & "')</script>")
        response.write("<script>addsubcategory('" & rs("category") & "','" & rs("subcategory") & "')</script>")
        strcategory = rs("category")
        strsubcategory = rs("subcategory")
    else
        if not rs("subcategory") = strsubcategory then
            response.write("<script>addsubcategory('" & rs("category") & "','" & rs("subcategory") & "')</script>")
            strsubcategory = rs("subcategory")
        end if
    end if
    rs.MoveNext
loop
rs.close
set rs=nothing
' OK so now our arrays are all created
' Lets actually put the information on the screen.
%>
<form name="frmAddress" method="POST" action="">
    <table>
        <tr>
            <td>Name:</td>
            <td><input type=text name="fullName"  /></td>
        </tr>
        <tr>
            <td>Address:</td>
            <td><input type=text name="address"  /></td>
        </tr>
        <tr>
            <td>category</td>
            <td>
                <!-- The onChange function runs the loadsubcategoryList function.
                Note also that the category and subcategory drop-downs have no options.
                The options are loaded from the onLoad method of the body tag.
                -->
                <select size="1" name="category" onChange="loadsubcategoryList();">
                </select>
            </td>
        </tr>
        <tr>
            <td>subcategory</td>
            <td>
                <select size="1" name="subcategory">
                </select>
            </td>
        </tr>
        <tr>
            <td>Phone</td>
            <td><input type=text name="Phone" /></td>
        </tr>
    </table>
    <p>
    <input type="submit" value="Submit" name="B1" />
    <input type="reset" value="Reset" name="B2" />
    </p>
</form>
</body>
</html>

Commented:
On what like are you getting the error?

Can you post the exact error message please

Neal.

Commented:
Check out your SQL statement I think you have an extra carriage return in there.

Neal.

Author

Commented:
Microsoft VBScript compilation error '800a0409'

Unterminated string constant

/dynamic_droplist.asp, line 65

Commented:
Did you check your SQL statement?  and which line is line 65?

Commented:
Make sure the following is on a ssingle line

strSQL = "select category.categoryName, subcategory.subcategory from subcategory, category Where subcategory.bindto = category.id ORDER BY category.categoryName, subcategory.bindto"

Commented:
else

strSQL = "select category.categoryName, subcategory.subcategory from subcategory, category Where subcategory.bindto = category.id " & _
"ORDER BY category.categoryName, subcategory.bindto"

Author

Commented:
Here's what I've done.

I made one table called categories having three fields.  ID, Category, SubCategory
Using Fritzs Code..  I get the window, a single drop down
BUT it is not populated.  

you can see result by viewing http://ESLZ.net/3.asp

Is it the connection string?  
I am developing this on the Win2003 server using FP extensions, with SQL SVR 2005
set objConnection = server.createobject("ADODB.Connection")
strConnectString = "DSN=DSN_name;DRIVER={SQL Native Client};UID=xxxUSERid;PWD=xxxPasword"  

I am unfamiliar with how to use fritzs code below to make the connection:
'set connection strings for entire application
strDataPath = server.MapPath("NorthWind.mdb")
strConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;"_
               + " Data Source= " & strDataPath & ";"_
               + " Mode=Share Deny None;User Id=admin;PASSWORD=;"
               
if not IsObject("ojbConnection") then
     set objConnection=Server.CreateObject("ADODB.Connection")
     objConnection.ConnectionTimeout = 15
     objConnection.CommandTimeout =  10
     objConnection.Mode = 3 'adModeReadWrite
     if objConnection.state = 0 then
          objConnection.Open strConnectString
     end if
end if

Thanks again for your patient help and response..  

Author

Commented:
Here's the code  as it is being used now.. Thanks for being patient I am still learning about these things.


<%@ Language = VBScript %>
<%Option Explicit%>
<%Response.Buffer = True%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft FrontPage 6.0">
<TITLE></TITLE>

<%
dim strConnectString, objConnection
dim strCategory, strSubcategory, objRS, strSelected, strSQL

strCategory = Request.Form("category")
strSubcategory = Request.Form("Subcategory")



'set connection strings for entire application
set objConnection = server.createobject("ADODB.Connection")
strConnectString = "DSN=DSN_name;DRIVER={SQL Native Client};UID=xxxUSERid;PWD=xxxPasword"  

               

sub makecategory()
     if not isObject("objRS") then
          set objRS=Server.CreateObject("ADODB.RecordSet")
     end if
     if objRS.state <> 0 then
          objRS.close
     end if
     strSQL = "SELECT DISTINCT Category  FROM categories ORDER BY category"
     objRS.Open strSQL,objConnection,2,2
     Response.Write("<option></option>" & VBCRLF )
     do while not objRS.EOF
          if objRS("Category") = strcategory then
               strSelected = " Selected "
          else
               strSelected = ""
          end if
          Response.Write("<option" & strSelected & ">" &_
                     objRS("category") & "</option>" & VBCRLF )
          objRS.MoveNext
     loop
     objRS.Close
     set objRS=Nothing
end sub




sub makeSubcategory()
     if strcategory <> "" then
          if not isObject("objRS") then
               set objRS=Server.CreateObject("ADODB.RecordSet")
          end if
          if objRS.state <> 0 then
               objRS.close
          end if
          strSQL ="SELECT DISTINCT SubCategory FROM Categories WHERE Category = '" &_
                     strcategory & "' ORDER BY SubCategory"
          objRS.Open strSQL,objConnection,3,3
          if objRS.eof then
               Response.Write("<option>No SubCategories Found</option>")
          else
               Response.Write("<option>Select Subcategory Now</option>" & VBCRLF )
               do while not objRS.EOF
                    if objRS("Subcategory") = strSubcategory then
                         strSelected = " Selected "
                    else
                         strSelected = ""
                    end if
                    Response.Write("<option" & strSelected & ">" &_
                               objRS("Subcategory") & "</option>" & VBCRLF )
                    objRS.MoveNext
               loop
          end if
          objRS.Close
          set objRS=Nothing
     else
          Response.Write("<option>Select a category First</option>")
     end if
end sub

sub makeCity()
     if strSubcategory <> "Select a category First" AND _
        strSubcategory <> "Select Subcategory Now" AND strSubcategory <>"" then
          if not isObject("objRS") then
               set objRS=Server.CreateObject("ADODB.RecordSet")
          end if
          if objRS.state <> 0 then
               objRS.close
          end if
          strSQL = "SELECT DISTINCT City FROM Customers WHERE Subcategory = '" &_
                     strSubcategory & "' ORDER BY City"
          objRS.Open strSQL,objConnection,3,3
          if objRS.eof then
               Response.Write("<option>No Cities Found</option>")
          else
               Response.Write("<option>Select City Now</option>" & VBCRLF )
               do while not objRS.EOF
                    if objRS("City") = strCity then
                         strSelected = " Selected "
                    else
                         strSelected = ""
                    end if
                    Response.Write("<option" & strSelected & ">" &_
                               objRS("City") & "</option>" & VBCRLF )
                    objRS.MoveNext
               loop
          end if
          objRS.Close
          set objRS=Nothing
     else
          Response.Write("<option>Select a Subcategory First</option>")
     end if
end sub
%>

<SCRIPT LANGUAGE=javascript>
<!--

function submitcategory(){
     var objForm = document.forms[0];
     objForm.elements['Category'].selectedIndex=0;
     objForm.elements['SubCategory'].selectedIndex = 0;
     objForm.submit();
}
function submitSubcategory(){
     var objForm = document.forms[0];
     objForm.elements['SubCategory].selectedIndex = 0;
     objForm.submit();
}

function submitForm(){
     var objForm = document.forms[0];
     objForm.action = "http://ESLZ.net/3.asp"
     return true;
}
//-->
</SCRIPT>

</HEAD>
<BODY onLoad="window.moveTo(0,0);window.resizeTo(800,400);">
<div style="align: center; text-align: center;">
<a href=# onClick="window.close(); return false;">Close Window</a>
</div>
<FORM action="" method=POST id=form1 name=form1 onSubmit="return submitForm()">
<SELECT  name="category" onChange="submitcategory()">
     <%call  makecategory%>
</SELECT><br>
<SELECT  name="Subcategory" onChange="submitSubcategory()">
     <%call makeSubcategory%>
</SELECT><br>

<p><INPUT type="submit" value="Submit" id=submit1 name=submit1></p>
</FORM>
</BODY>
<%
objConnection.Close
set objConnection = Nothing
%>

</HTML>

Commented:
Fritz's code is for an Access database, not SQL.  If you are using SQL you may continue to use your
connection information.



Author

Commented:
Its returning nothing..  The drop down list is blank.. I've tested on another site as well..  using another database..  
CERTIFIED EXPERT
Top Expert 2005

Commented:
I am wondering where you actually open the connection?

<%@ Language = VBScript %>
<%Option Explicit%>
<%Response.Buffer = True%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft FrontPage 6.0">
<TITLE></TITLE>

<%
dim strConnectString, objConnection
dim strCategory, strSubcategory, objRS, strSelected, strSQL

strCategory = Request.Form("category")
strSubcategory = Request.Form("Subcategory")



'set connection strings for entire application
set objConnection = server.createobject("ADODB.Connection")
strConnectString = "DSN=DSN_name;DRIVER={SQL Native Client};UID=xxxUSERid;PWD=xxxPasword"  


if not IsObject("objConnection") then
     set objConnection=Server.CreateObject("ADODB.Connection")
     objConnection.ConnectionTimeout = 15
     objConnection.CommandTimeout =  10
     objConnection.Mode = 3 'adModeReadWrite
     if objConnection.state = 0 then
          objConnection.Open strConnectString
     end if
end if
       
Now you have a connection to work with for your recordsets.

FtB

Author

Commented:
Hi Fritz,

That seemed to do the trick, thanks for your help!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.