Classic ASP Discussion - Populating Multiple Dropdown Boxes from MS Access

Print Page | Close Window

Populating Multiple Dropdown Boxes from MS Access

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=27930
Printed Date: 28 March 2026 at 5:58pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Populating Multiple Dropdown Boxes from MS Access

Posted By: genkidave
Subject: Populating Multiple Dropdown Boxes from MS Access
Date Posted: 11 October 2009 at 3:56pm

Hey there out there, I am currently re-learning ASP, VB, SQL and MS Access.

I have been charged to update my Martial Arts instructor's website and want to add
ASP and a database to it.

I can finally figured out how to display CURRENT info that I entered manually in to
MS Access in tables so it's nice and neat to read and understand, BUT I now need
to ADD, UPDATE and DELETE info about a new member of our club.

Most of this process is easy enough as there are tons of tutorials out there, but when
it comes to populating multiple Dropdown Boxes (Gender, Belt Level, Dojo, Sensei), I am
at a loss.

Here's an example of what I mean.

As shown above, I have different tables with info already ready to be selected

Gender
----------
Male
Female

Belt Level
-------------
10th Kyu
9th Kyu
8th Kyu

so on and so forth with the other tables. My instructor wants to be able to just click
on each of the "Dropdown, List, Menu, Option" Boxes instead of manually typing it in. The
problem I'm having is, even though I can populate one Dropdown box (taken from a tutorial)
when I tried to put in several Dropdown boxes on the same form, I kept on getting ASP errors
and different kinds. What is the ACTUAL name for those boxes anyway, Dropdown, List, Menu or Option?

Is there a very generic way that any COMPLETE newbie to ASP programing with MS Access can understand on how to do this?

Thanks in advance

Dave

Replies:

Posted By: WebWiz-Bruce
Date Posted: 12 October 2009 at 11:22am

How I would do it is that you create your drop downs in HTML as normal with the list of whatever you need in it. You then need to decide which option in the list is selected depending on what is in the database.

Below is a drop down from my own code for a gender option drop down:-

<select name="gender" id="gender" tabindex="10">
      <option value=""<% If strGender = "" Or strGender = null Then Response.Write(" selected") %>>Private</option>
      <option value="Male"<% If strGender = "Male" Then Response.Write(" selected") %>>Male</option>
      <option value="Female"<% If strGender = "Female" Then Response.Write(" selected") %>>Female</option>
     </select>

-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting

Posted By: genkidave
Date Posted: 12 October 2009 at 3:23pm

Thanks for the quick reply Bruce, your advice is really appreciated!

I'm trying to learn as much as possible about ASP, VBScript and getting and posting info from a DB,
so I really need some help and advice on doing thing the "Hard Way" so I can take pleasure in knowing that if I'm ever in a similar situation, I can just read from the DB from the start.

I tried your idea and when I went back to the DB, I noticed that Private, Male and Female had been
put in again. Weird, eh? So now I have 5 records in there, (Male, Female, Private, Male, Female)

I'll have to take a look at that and make sire that no redundancies occur again, LoL!!

Any advice as to how to GET the info from the MS Access DB for several dropdown menus on the same form? Here's the code for what DOES work right now for ONE dropdown box, but like I said, when I try to get the info for more than one, my browser goes haywire, LoL~!
__________________________________________________________________________

<%
    ' Connection object
    Dim adoConn

    ' Recordset object
    Dim rsGender

    ' SQL String
    Dim strSQLGender

    ' Create the Connection object
    Set adoConn = Server.CreateObject("ADODB.Connection")

    ' Open the Connection
    adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("mydatabase-Name_taken_out.mdb")

    ' Creates the recordset object
    Set rsGender = Server.CreateObject("ADODB.Recordset")


    ' SQL String for query
    strSQLGender = "SELECT tblGender.fldGenderID, tblGender.fldGender FROM tblGender;"

    ' I execute the SQL the statement that I want
    ' The query should return all the data I'll need in my combo box(s)
    rsGender.Open strSQLGender, adoConn
%>
<html>
<head>
<title>Add Member Info</title>
</head>
<body>

<form name="form" method="post" action="addmember.asp">

<table width="500" border="1" align="center">
    <tr>
      <td colspan="2"><div align="center">MEMBER'S PERSONAL INFO</div></td>
    </tr>
    <tr>
      <td width="250">First Name:</td>
      <td width="250">Last Name:</td>
    </tr>
    <tr>
      <td width="250"><input type="text" name="txtFname" id="txtFname"></td>
      <td width="250"><input type="text" name="txtLname" id="txtLname"></td>
    </tr>
    <tr>
      <td width="250">D.O.B.:</td>
      <td width="250">Gender:</td>
    </tr>
    <tr>
           <td width="250"><input type="text" name="txtDOB" id="txtDOB"></td>
        <td width="250">
            <%
                      ' This code seems to work the way it's set up between the <td></td> tags
though you obviously won't see it without the DB, but it does populate
                      properly
             %>

        <SELECT NAME = "GENDER" id = "GENDER">
         <%
        ' While I don't get the end of DB
        Do While Not rsGender.EOF
            'Put the ID at Value
            ' Value is important to linked tables
        %>
        <OPTION VALUE="<%=rsGender("fldGenderID")%>"> <%=rsGender("fldGender")%></OPTION>

        <%
        ' Move to the next record...
        rsGender.MoveNext
        ' Keep the loop
        Loop
        %>
        </SELECT>
        </td>
    </tr>
    <tr>
      <td width="250">Address:</td>
      <td width="250">City:</td>
    </tr>
    <tr>
      <td width="250"><input type="text" name="txtAddress" id="txtAddress"></td>
      <td width="250"><input type="text" name="txtCity" id="txtCity"></td>
    </tr>
    <tr>
      <td width="250">Prov:</td>
      <td width="250">Postal Code:</td>
    </tr>
    <tr>
      <td><input type="text" name="txtProv" id="txtProv"></td>
      <td><input type="text" name="txtPcode" id="txtPcode"></td>
    </tr>
    <tr>
      <td>Phone:</td>
      <td>Email:</td>
    </tr>
    <tr>
      <td width="250"><input type="text" name="txtPnum" id="txtPnum"></td>
      <td width="250"><input type="text" name="txtEmail" id="txtEmail"></td>
    </tr>
    <tr>
      <td colspan="2"><div align="center">STAT INFO</div></td>
    </tr>
    <tr>
      <td width="250">1st Martial Art:</td>
      <td width="250">2nd Martial Art</td>
    </tr>
    <tr>
      <td width="250"><select name="select" id="select">
      </select></td>
      <td width="250"><select name="select2" id="select2">

Posted By: WebWiz-Bruce
Date Posted: 12 October 2009 at 4:19pm

The code I gave you before is what you need. However you are trying to do this the wrong way.

You create the options for the list in HTML and you decide which options is 'selected' from the value in the database, rather than trying to populate the drop down from the database.

-------------
https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting
https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting

Posted By: ljamal
Date Posted: 12 October 2009 at 5:47pm

The code you posted,genkidave, should work

After that first, select box then should be able to do:


<td width="250">

   

        <SELECT NAME = "belt" id = "belt">

         <%

  ' SQL String for query    

    strSQLGender = "SELECT beltID, Belt FROM tblBelt;"





    ' I execute the SQL the statement that I want

    ' The query should return all the data I'll need in my combo box(s)

    rsGender.Open strSQLGender, adoConn



        ' While I don't get the end of DB

        Do While Not rsGender.EOF

            'Put the ID at Value

            ' Value is important to linked tables

        %>

        <OPTION VALUE="<%=rsGender("beltID")%>"> <%=rsGender("Belt")%></OPTION>

       

        <%

        ' Move to the next record...

        rsGender.MoveNext

        ' Keep the loop

        Loop

        %>

        </SELECT>

        </td>

As long as you run the code BEFORE the connection to the DB is closed.

-------------
L. Jamal Walton

http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming

Posted By: genkidave
Date Posted: 12 October 2009 at 7:05pm

OK, I tried this for my martial arts dropdown menu. We teach karate and kobudo, below is similar code, but added some recordsets, sql strings and the code for the dropdown box. I got an error:

Technical Information (for support personnel)

Error Type:
Provider (0x80020005)
Type mismatch.
/db/addmember.asp, line 33

Browser Type:
Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.3) Gecko/20090824 Firefox/3.5.3 (.NET CLR 3.5.30729)

Page:
GET /db/addmember.asp

Time:
October 12, 2009, 10:46:47 AM

More information:
http://www.microsoft.com/ContentRedirect.asp?prd=iis&sbp=&pver=5.0&ID=500;100&cat=Provider&os=&over=&hrd=&Opt1=&Opt2=%2D2147352571&Opt3=Type+mismatch%2E" rel="nofollow - Microsoft Support

Here's my code (DB name taken out):
___________________________________________________________

<%
    ' the connection object
    Dim adoConn

    ' the recordset object
    Dim rsGender
    Dim rsMA1 '<------ New

    ' the SQL String
    Dim strSQLGender
    Dim strSQLMA1 '<------ New

    ' create the Connection object
    Set adoConn = Server.CreateObject("ADODB.Connection")

    ' open the Connection
    adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("databasename.mdb")

    ' creates the recordset object
    Set rsGender = Server.CreateObject("ADODB.Recordset")

    ' New Recordset
    Set rsMA1 = Server.CreateObject("ADODB.Recordset")


    ' SQL String for query
    strSQLGender = "SELECT tblGender.fldGenderID, tblGender.fldGender FROM tblGender;"
    'New SQL String
    strSQLMA1 = "SELECT tblMArt1.fldMArt1ID, tblMArt1.fldMArt1Name FROM tblMArt1;"

    ' execute the SQL statement that you want
    ' The query should return all the data you'll
    ' need in your combo box
    rsGender.Open, strSQLGender, adoConn
    rsMA1.Open strSQLMA1, adoConn '<------ New Recordset and SQL string

%>
<html>
<head>
<title>Add Member Info</title>
</head>
<body>

<form name="form" method="post" action="addmember.asp">

<table width="500" border="1" align="center">
    <tr>
      <td colspan="2"><div align="center">MEMBER'S PERSONAL INFO</div></td>
    </tr>
    <tr>
      <td width="250">First Name:</td>
      <td width="250">Last Name:</td>
    </tr>
    <tr>
      <td width="250"><input type="text" name="txtFname" id="txtFname"></td>
      <td width="250"><input type="text" name="txtLname" id="txtLname"></td>
    </tr>
    <tr>
      <td width="250">D.O.B.:</td>
      <td width="250">Gender:</td>
    </tr>
    <tr>
           <td width="250"><input type="text" name="txtDOB" id="txtDOB"></td>
        <td width="250">
        <SELECT NAME = "GENDER" id = "GENDER">
         <%
        ' while we don't get the end of DataBase
        Do While Not rsGender.EOF
            'we put the ID at Value
            ' this value is important to linked tables
        %>
        <OPTION VALUE="<%=rsGender("fldGenderID")%>"> <%=rsGender("fldGender")%></OPTION>

        <%
        ' Move to the next record...
        rsGender.MoveNext
        ' keep the loop
        Loop
        %>
        </SELECT>
        </td>
    </tr>
    <tr>
      <td width="250">Address:</td>
      <td width="250">City:</td>
    </tr>
    <tr>
      <td width="250"><input type="text" name="txtAddress" id="txtAddress"></td>
      <td width="250"><input type="text" name="txtCity" id="txtCity"></td>
    </tr>
    <tr>
      <td width="250">Prov:</td>
      <td width="250">Postal Code:</td>
    </tr>
    <tr>
      <td><input type="text" name="txtProv" id="txtProv"></td>
      <td><input type="text" name="txtPcode" id="txtPcode"></td>
  &nb