This article describes how we can use c# razor to get data from SQL server and populate a HTML <SELECT>
The page retrieves a patient record and display it, but one of the fields is a foreign key to the disease table. We want to acheive the following:
1: Display the patient record
2: Populate the SELECT with all diseases
3: Auto select the disease assigned to this patient
4: On save esnure we save the current;y selected disease id along with the paitents other data
// Get result set of all diseases (to populate our SELECT)
var diseases = db.Query("SELECT * from hc_disease");
?
//Get the specified patient record
var row = db.QuerySingle("select * from hc_patient where hc_patient_id=@0", id);
Disease:
<select class="form-control" id="diseaselist" name="diseaselist">
</select>
In this step we create the 'select' contianer in which is where all the drop down items will be displayed.
1:<body>
2: <a class="btn btn-success" href="~/PatientCRUD/ShowPatients.cshtml" title="tooltip Create">Patient list</a>
3:
4: <a class="btn btn-success" href="~/PatientCRUD/ShowDisease.cshtml" title="tooltip Create">Manage Diseases</a>
5: <h1>Edit Patient</h1>
6: <form method="post">
7: Name: <input class="form-control" type="text" name="name" id="txtname" value="@row.name" />
8: <br />
9: Phone: <input class="form-control" type="text" name="phone" id="txtphone" value="@row.phone" />
10: <br />
11: Disease:
12: <select class="form-control" id="diseaselist" name="diseaselist">
13: @{
14: foreach (var r in diseases)
15: {
16: <option value="@r.hc_disease_id">@r.name</option>
17: }
18: <script type="text/javascript">
19: document.getElementById('diseaselist').value = "@row.hc_disease_id";
20: </script>
21: }
22: </select>
23: <br />
24: <textarea class="form-control" rows="2" name="notes" required>@row.notes</textarea>
25: <br />
26: <input class="btn btn-success" id="Submit1" type="submit" value="submit" />
27: </form>
28:</body>
The page above displays the input boxes where the user will update/ edit the patients record.
From lines 12-22 is our select container. Within the ontainer; for each row in the disease table we populate the SELECT.
Finally lines 19 uses a single line of javascript to set the selected value of the SELECT to the users disease (using the patient foreign key hc_disease_id).
if (IsPost)
{
//UPDATE this patients record
db.Execute("UPDATE hc_patient SET name =@0, phone=@1,notes=@2, hc_disease_id=@4 WHERE hc_patient_id= @3",
Request.Form["name"],Request.Form["phone"], Request.Form["notes"], Request.QueryString["id"], Request.Form["diseaselist"]);
//
//then navigate back to the patient list page
Response.Redirect("ShowPatients.cshtml");
}
When the user has finished editing they Submit the record and we then Save the updated record and return the our home page