Solved

Database access

Posted on 1998-07-01
2
200 Views
Last Modified: 2010-04-09
I am pretty new to the area of web development, and I am working on a simple project to retrieve info from an access db and display it. Basically, I need to populate a combo box with a list of countries from the Country table, the user then selects a country which I then use to retrieve a list of cities and populate another combo box.  I need guidance on how I construct this and possible some sample code to do this, ...HTML seems very different to usual VB programming which I have been doing for the last 4 years.  Thanks.
0
Comment
Question by:gbr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 28

Expert Comment

by:sybe
ID: 1844505
It can be done (i have done it), but it is not so easy to write it down in a few words.

I have used ASP (Active Server Pages) to get the data from the database. Then I used javascript to change the second combobox according to the choice made in combo #1.

In this case it all work in one page, in NS3+ en IE4 (IE3 does not have enough javascript capacities).

It also can be done using 2 ASP pages, in which the first page (with the country choice) calls the second (with the city choice).

ASP is not so very different from VB, as it uses VB-script, at least it should look familiar to you :)

Anyway, it seems no use to give my answer if you are not running ASP. So that is my first question to you.





0
 
LVL 28

Accepted Solution

by:
sybe earned 100 total points
ID: 1844508
I'll try to explain it in steps, it will make the code understandable.

1. Database
===========

You have 2 tables, those should look something like this

Countries: Country_id, Country_name
Cities: City_id, City_name, Country_id

The Country_id field in the Cities table indicates the country in which the city is.


2. HTML-form
============

In the form are two comboboxes (maybe also some other stuff, but that's not important for the functionality). The form is named Form1.

<form name="Form1" action="...">

<select name="Country">
   <option value="...Country_id from the database..">..Country_name...
</select>


<select name="City">
   <option value="...City_id from the database..">..City_name...
</select>

3. Filling combobox #1 from the database
========================================

Combobox #1 (Country) can be filled from the database by ASP directly, it won't change, so the result can be hardcoded in HTML. In fact that is what your code does.

<%
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "lmdata"
Set RS = Conn.Execute("SELECT Country_id, Country FROM Countries ORDER by Country")
%>

<select name="Country" size="1">
<%Do While Not RS.EOF%>
   <option value="<%=RS("Country_id")%>"><%=RS("Country_name")%></option>
<%
RS.MoveNext
Loop
RS.Close
%>
</select>


4. Combobox #2
==============

To start this should be an empty combobox. When nothing is filled in, however, the width will be very small, and it won't change later). So It should be filled with some dummy code, to give it the right size. Also the length is fixed after loading.

<SELECT Name="City">
<option> ----------------------------------
<option>
<option>
<option>
<option>
<option>
<option>
<option>
</SELECT>


5. Javascript to change Combobox #2 after something is selected in combo #1.
==============================================================

Generally you can change the values of a combobox by a code like this:


<script>
//deleting all options from a combobox in a form named Form1 and a combobox names City:

i = 0
while (document.Form1.City.options.length) {
      document.Form1.City.options[i] = null;
}

//adding a new option:

document.document.Form1.City.options[0] = new Option('text', 'value');

// the [0] indicates the number of the option, 0 is the first

</script>


So what you want is that a choice made in combo #1 triggers a function that will change combo #2

This can be done by adding an "onChange" to combo #1:

<select name="Country" onChange="ChangeCombo2(this.options[this.selectedIndex].value)">

..

</select>

In this way the onChange passes the value of the highlighted country to the function ChangeCombo2(). You want to have the value, because that is how you can select which cities should be displayed in combo #2 (it corresponds to the country_id in the cities table).


The ChangeCombo2() function should look like this:

<script>
function ChangeCombo2(country_id) {
    // first delete the existing options
    i = 0
    while (document.Form1.City.options.length) {
        document.Form1.City.options[i] = null;
    }
    // then add the new options
    // you want a loop through all the cities with the country_id    
    // chosen. I'll show below.
}

</script>


6. Making an array of all the cities, including the country_id
==============================================================

This is the fun part.
As the whole thing will run client-side, you have to store all the cities in javascript variables, and make it possible to select on country_id. This can be done by making an 2-dimensional array of all cities from the database. In this example I'll use a way to do it in JavaScript 1.1 (so it will work in NS3 too).

The result should look something like this, but as it has to be generated by ASP (because the values come from the database) it will be a bit more complicated.

<script>
arrCities = new Array();
arrCities[1] = new Array();
arrCities[1][0] = new Array();
arrCities[1][0][0] ='Amsterdam';
arrCities[1][0][1] =5;

// this is the code for a city named "Amsterdam" with country_id "1" and city_id "5".

arrCities[1][1][0] ='Rotterdam';
arrCities[1][1][1] =8;

// this is the code for a city named "Rotterdam" with country_id "1" and city_id "8".

//In general:
//the first index = country_id
//the second index = just a counter (to use to loop through)
//the third index: if [0] then value =  city_name, if [1] then value = city_id

</script>


Now it is possible to loop through an array, based on country_id and put all the cities in combo #2:

<script>
function ChangeCombo2(country_id) {
    // first delete the existing options
    i = 0
    while (document.Form1.City.options.length) {
        document.Form1.City.options[i] = null;
    }
    i = 0;
    while (i < arrCities[country_id].length) {
        document.inputform.Category.options[i] = new Option(arrCities[country_id][i][0], arrCities[country_id][i][1]);
    i++;
    }
}
</script>


7. Generating the JavaScript arrays from ASP
============================================

It is the easiest to start from the Countries table:


<script>
<%
Set RS = Conn.Execute("SELECT Country_id, Country FROM Countries ORDER by Country")

Do While Not RS.EOF
    Country_id = RS("country_id")
    ' 1. make the javascript array for categories for this subject
      strSQL = "SELECT city_id, city_name FROM Cities WHERE countr_id =" & Country_id


      Set RS_Cities = Conn.Execute(strSQL)
        Response.write "arrCities[" & countr_id & "] = new Array();" & CHR(10)

      j = 0
      Do While Not RS_City.EOF

            City_id = RS_City("City_ID")

            Response.write "arrSubjects[" & Country_id & "][" & j & "] = new Array();" & CHR(10)
          Response.write "arrSubjects[" & Country_id & "][" & j & "][0] ='" & RS_City("City_Name") & "';" & CHR(10)
          Response.write "arrSubjects[" & Country_id & "][" & j & "][1] =" & City_id & ";" & CHR(10)

      j = j + 1
      RS_City.MoveNext
      Loop
      RS_City.Close

    i = i + 1
RS.MoveNext
Loop
RS.Close

%>
</script>

===================

That's it

I guess you get the idea, I have no overview while writing the answer, so there might be a mistake in it, but principally it should work.





0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Find out what you should include to make the best professional email signature for your organization.
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
In this tutorial viewers will learn how add a scalable full-width header using CSS3. Create a new HTML document with an internal stylesheet. Set a tiled background.:  Create a new div and name it Header. Position it with position:absolute at the top…
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question