Solved

Database access

Posted on 1998-07-01
2
162 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
  • 2
2 Comments
 
LVL 28

Expert Comment

by:sybe
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Do you want to insert HTML5 video into your site? This is the tutorial how to do so. What are the main advantages of HTML5 video? 1) Have good compression, good image quality, and low decode processor use. 2) It is royalty-free 3) It is easi…
In this tutorial viewers will learn how to embed Flash content in a webpage using HTML5. Ensure your DOCTYPE declaration is set to HTML5: "<!DOCTYPE html>": Use the <object> tag to embed Flash content.: To specify that the object is Flash content, d…
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now