Lookup Tables to populate combo boxes in web application

Good morning,

I'm developing a web application (ASP.NET 3.5, Visual Studio 2008, VB 2008). I plan to design and implement Custom Business Object, a Data Access Layer and Business Logic Layer and use the objectdatasource control  and bind it to a grid view.

But first I'm designing the database in SQL Server 2008. I have my data tables about 90% designed and I'm designing the lookup tables. And I just want to make sure I'm approaching this correctly or if their is a better approach.

It looks as though I have a many to many relationship between two of my lookup tables, ActivityDetailType and LocationType. Normally, I would use a junction table called ActivityLocationType but this is my first attempt at web developement and I need these values to be populated in the combo boxes as mentioned earlier. (See attachment)

The user will select the Activity (ActivityDetailType) from the first combo box. Based on their selection I would like to filter the next combo box with the values they can choose (LocationType) based on their previous selection.

Is this approach appropriate or should I be doing something completely different?

FYI - I'm pretty much new to everything! And the ActivityType Table is for output (Report) purposes.

Thanks

 ActivityReportDatabaseDiagram.pdf
tis9700Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bernie BsenChef de cuisineCommented:
looks like a Ajax issue.
onChange (onKeyUp, onBlur....) your select tag you runs a javascript. This javascript calls another script doing the database query and producing new html-code (using echo...)
This code is filled in as innerHTML into your originating page.
tis9700Author Commented:
Hi brb6708
I'm not familiar with Ajax or Javascript........yet. As I said, I'm a newbie. Are you saying I shouldn't use lookup tables and stored procedures to populate my combo boxes?
Bernie BsenChef de cuisineCommented:
I use PHP for those issues. But as it is not a PHP but a browser solution, it's quite easy to solve it.

Here's an example:

First the select tag that triggers the database query:

       <td><select id=\"kursid\" name='kursid' tabindex='1' width="200px"        onBlur="kursanzeigen();" onChange="kursanzeigen();">        <option value="opt1">Option 1</option>        <option value="opt2">Option 2</option>        </select></td>
Second, you need a div box containing the resulting html-tags:

      <div id="resultHTML">....resulting HTMLcode here ......</div>
and third, the following Javascript:


function handleContent() {        if (xmlHttpObject.readyState == 4) {                document.getElementById('resultHTML').innerHTML = xmlHttpObject.responseText;        }}function getxmlHTTPObject() {        var nxmlHttpObject = false;        if (typeof XMLHttpRequest != 'undefined') {                nxmlHttpObject = new XMLHttpRequest();        }        if (!nxmlHttpObject) {                try {                        nxmlHttpObject = new ActiveXObject("Msxml2.XMLHTTP");        } catch(e) {                        try {                                nxmlHttpObject = new ActiveXObject("Microsoft.XMLHTTP");                        } catch(e) {                                nxmlHttpObject = null;                        }                }        }              return nxmlHttpObject;}function kursanzeigen() {        xmlHttpObject=getxmlHTTPObject();        xmlHttpObject.open('get','ajm_kursanzeige.php?kursid=' + document.getElementById('kursid').value);        xmlHttpObject.onreadystatechange = handleContent;        xmlHttpObject.send(null);        return false;}

The separate script "ajm_kursanzeige.php" does the sql query and simply echoes the result
for example:
<select name="option2"><option>Option2 No1</option><option2 No 2></select>
If the value kursid in first select tag changes, then ajm-kuranzeige.php is executed and the resultiung string replaces the div box with "... some HTML code... "

The way you use SQL to produce the innerHTML code has no impact to the result. As you know there are a lot of ways leading to happyness.....
The Trick is simply to separate the database functionality from the presentation layer (HTML).


Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

tis9700Author Commented:
Hey brb6708,

Looks an interesting solution. Thanks for the help but I just found out management wants to be able to add new values to the lookup tables as needed and they would not be ables to do that in the HTML. I will have to provide them a screen to do it.

Thanks
Bernie BsenChef de cuisineCommented:
That's exactly why I use this approach!

The second script ajm-kursanzeige.php just reads a database and filters the selections.

Let's assume the user highlights Option2 in the first select then a query like
  "select SecondLevelOption from SecondLevelTable where option = 'opt2'

opt2 is the value thats passed as getVariable kursid in javascript function

$kursid = $_REQUEST["kursid"];$result = query("select SecondLevelOption from SecondLevelTable where option = '$kursid');if ($result) {    echo "<select name='SecondSelect'>";    while ($option=fetch_object($result) {         echo "<option>".$option->SecondLevelOption."</option>";    }   echo "</select>";} else {   echo "<strong>Sorry, no Options for this Issue</strong>";}
So your managers simply must care for valid database entries. They have nothing to do with html coding. It's all done by this ajp-kursanzeige.php script.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tis9700Author Commented:
It works but It was outside of the technologies stated in my question. I was looking for a solution based on those.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.