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>
}
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>
}
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?
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.
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?
@{
// ... 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.
Marked as answer by being_mp on Feb 22, 2012 09:35 AM
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?
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?
being_mp
Member
42 Points
26 Posts
Cascading Selectboxes from Database
Feb 21, 2012 02:43 PM|LINK
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> }variable select box Database sql
GmGregori
Contributor
5564 Points
749 Posts
Re: Cascading Selectboxes from Database
Feb 21, 2012 02:57 PM|LINK
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> }being_mp
Member
42 Points
26 Posts
Re: Cascading Selectboxes from Database
Feb 21, 2012 03:20 PM|LINK
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?
GmGregori
Contributor
5564 Points
749 Posts
Re: Cascading Selectboxes from Database
Feb 21, 2012 03:40 PM|LINK
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.
being_mp
Member
42 Points
26 Posts
Re: Cascading Selectboxes from Database
Feb 21, 2012 07:11 PM|LINK
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?
GmGregori
Contributor
5564 Points
749 Posts
Re: Cascading Selectboxes from Database
Feb 22, 2012 12:10 AM|LINK
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.
being_mp
Member
42 Points
26 Posts
Re: Cascading Selectboxes from Database
Feb 22, 2012 07:23 AM|LINK
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?
GmGregori
Contributor
5564 Points
749 Posts
Re: Cascading Selectboxes from Database
Feb 22, 2012 09:12 AM|LINK
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.
being_mp
Member
42 Points
26 Posts
Re: Cascading Selectboxes from Database
Feb 22, 2012 09:35 AM|LINK
Great, it works! - of course you would say
Thank you very much.
being_mp
Member
42 Points
26 Posts
Re: Cascading Selectboxes from Database
Feb 22, 2012 10:42 AM|LINK
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?