Cascading Selectboxes from Database

I'm stuck at the following problem - probably an easy one. I got two select boxes (department, persons). When choosing department 1, I wan't only the persons in department one to be displayed. The sql-statements are working with static values (like in the example: 'DEPARTMENT 2'). I just don't get the variable from the select box department in the sql statement dynamically. A hint would be appreciated. Thanks a lot. Here some code snippets:

var department = Request["department"];
 var selectQueryStringPerson = "SELECT PERSON.SURNAME, PERSON.NAME, DEPARTMENT.DEPARTMENT FROM DEPARTMENT INNER JOIN PERSON ON PERSON.DEPARTMENT_ID = DEPARTMENT.DEPARTMENT_ID where DEPARTMENT.DEPARTMENT = 'DEPARTMENT 2'";
 
			...

			@foreach(var row in db.Query(selectQueryStringDEPARTMENT)){
			<option>@row.DEPARTMENT</option>
			}
		   
		   ...
		   
		   @foreach(var row in db.Query(selectQueryStringFotograf)){
			<option>@row.NACHNAME, @row.VORNAME</option>
			}

Re: Cascading Selectboxes from Database

If I have understood your problem, the query for populating the second dropdown list is the following:

var department = Request["department"];
var selectQueryStringPerson = "SELECT PERSON.SURNAME, PERSON.NAME, DEPARTMENT.DEPARTMENT FROM DEPARTMENT " +
	"INNER JOIN PERSON ON PERSON.DEPARTMENT_ID = DEPARTMENT.DEPARTMENT_ID where DEPARTMENT.DEPARTMENT = @0";
 
			...

			@foreach(var row in db.Query(selectQueryStringDEPARTMENT)){
			<option>@row.DEPARTMENT</option>
			}
		   
		   ...
		   
		   @foreach(var row in db.Query(selectQueryStringPerson, department)){
			<option>@row.NACHNAME, @row.VORNAME</option>
			}

Re: Cascading Selectboxes from Database

that's what I did in the first place, but it throws an error: "System.ArgumentNullException: Parameterized query expects a parameter value which was not supplied" Parameter name 0. The values of department are string values, is that a problem?

Re: Cascading Selectboxes from Database

I think that the error occurs because you execute the query before you have a value for Request["department"].

The simplest solution is to have two pages, one with the first dropdown list and the other with the second dropdown list.

When you choose the department in the first list, the form submission redirect to the other page where the second list is populated with the members of the choosen department.

Re: Cascading Selectboxes from Database

Can you think of another option within the one form. I have to collect all Information (department, person, other values from user input) and send it all with the post. Is there a possibility to fill the variable of the second list with the selected item of the first one?

Re: Cascading Selectboxes from Database

Adjust the following snippet to your goals:

@{
    // ... here your former code

    var dep = db.Query(selectQueryStringDEPARTMENT);
    var curDep = dep.First().DEPARTMENT;
    var selectQueryStringPerson = "SELECT PERSON.SURNAME, PERSON.NAME, DEPARTMENT.DEPARTMENT FROM DEPARTMENT " +
        "INNER JOIN PERSON ON PERSON.DEPARTMENT_ID = DEPARTMENT.DEPARTMENT_ID where DEPARTMENT.DEPARTMENT = @0";
    
    if(IsPost) {
        if(!Request["btn2"].IsEmpty()){
 
            // ... here  form processing
        
	}
        
        curDep = Request["drop1"];
        
    }
    
    var persons = db.Query(selectQueryStringPerson, curDep);
}

<html>
    <body>
        <form action="" method="post">
            <select name="drop1">
                @foreach(var row in dep){
                    <option
                        @if(@row.DEPARTMENT == curDep) {<text>selected="selected"</text>}>@row.DEPARTMENT</option>
                }
            </select>
            <input type="submit" name="btn1" value="Go"/>
            
            <select name="drop2">
                @foreach(var row in persons){
                    <option>@row.NACHNAME, @row.VORNAME</option>
                }
            </select>
            <input type="submit" name="btn2" value="Submit"/>
        </form>
    </body>
</html>

When starts, the page takes the first department as default (var curDep = dep.First().DEPARTMENT;) and populates the second dropdown list with persons from this department.

If you want to choose another department, you must select it and click the "Go" button.

Clicking the "Submit" button starts the form processing.

Hope I haven't made mistakes.

Re: Cascading Selectboxes from Database

thank you for the example. I wonder if there's another option without the second submit-button. I'd like to dispense with the second button. Is there something like onchange?

Re: Cascading Selectboxes from Database

Make these changes:

.....
    <body>
        <form action="" method="post" id="form1">
            <select name="drop1" onchange='document.forms["form1"].submit();'>
.....

Pay attention that it doesn't work if Javascript is disabled in the browser.

Re: Cascading Selectboxes from Database

Great, it works! - of course you would say

Thank you very much.

Re: Cascading Selectboxes from Database

I still got a problem with the onchange submit. That's because there is another input-field which allows the user to select several pictures to upload. Now, by changing the department, the selected images gets uploaded - and that's what I want to prevent. So I need a possibility to get the variable from department without submitting the form. Any ideas?