I'm having a problem searching a SQL database. I have a project that has drop down list, textboxes, and a button. When I click on the button, all the information from the database is shown, I only want the information I selected to be shown. I want search the database and then have the results shown in the table.
Here is my controller:
public IActionResult Index()
{
List<string> states = _Db.Officers
.Select(o => o.State).Distinct().ToList();
List<Officer> officers = _Db.Officers.ToList();
ViewBag.States = states;
return View(officers);
}
[HttpPost]
public IActionResult Index(string state)
{
List<Officer> Officers;
if (string.IsNullOrEmpty(state) || state == "")
{
Officers = _Db.Officers.ToList();
}
else
{
Officers = _Db.Officers.Where(o => o.State == state).ToList();
}
ViewBag.States = _Db.Officers.Select(o => o.State).Distinct().ToList();
return View("Index", Officers);
}
My view:
@model IEnumerable<Officer>
<td>
<td align= "right">City:</td>
<input type= "text" name="City"/>
<td>
<td align="right">State:</td>
<td>
<select>
<option Value=""> Select a State . . .</option>
<option value="DE">Delaware</option>
<option value="DC">District of Columbia</option>
<option value="IN">Indiana</option>
<option value="MD">Maryland</option>
<option value="MI">Michigan</option>
<option value="NJ">New Jersey</option>
<option value="NY">New York</option>
<option value="OH">Ohio</option>
<option value="PA">Pennsylvania</option>
<option value="VA">Virginia</option>
<option value="ON">Ontario</option>
<option value="QC">Quebec</option>
</select>
</td>
<input type="button" Value="Search"/>
<table id="Results"class="table table-bordered table-striped" style="width:100%; background-color:lightgray; border-color:black;display:none" >
<thead>
<tr>
<th style="color:blue">
First Name
</th>
<th style="color:blue">
Last Name
</th>
<th style="color:blue">
Phone
</th>
<th style="color:blue">
Cell Phone
</th>
<th style="color:blue">
City
</th>
<th style="color:blue">
State
</th>
<th style="color:blue">
Zip Code
</th>
<th style="color:blue">
Agency
</th>
<th style="color:blue">
Address
</th>
<th style="color:blue">
Date Modified
</th>
</thead>
@foreach (var officer in @Model)
{
<tr>
<td>@officer.FirstName</td>
<td>@officer.LastName</td>
<td>@officer.Phone</td>
<td>@officer.Cell</td>
<td>@officer.City</td>
<td>@officer.State</td>
<td>@officer.PostalCode</td>
<td>@officer.AgencyName</td>
<td>@officer.Address1</td>
<td>@officer.DateUpdated</td>
</tr>
}
</table>
<script>
function func()
{
document.getElementById('Results').style.display = 'block';
document.getElementById("HideDiv").style.display = 'block';
}
</script>
Is there a way to have the data show only after the state is selected?
You can change the controller method to pass an empty object when the event is triggered,And in the corresponding page, you can set an if statement to accept empty objects and not display the relevant data. Here's an example you can use as a reference:
Controller:
public IActionResult Index()
{
List<string> states = _Db.Officers.Select(o => o.State).Distinct().ToList();
ViewBag.States = states;
return View();
}
[HttpPost]
public IActionResult Search(string selectedState)
{
List<Models.Officer> officers;
if (string.IsNullOrEmpty(selectedState) )
{
officers = null;
}
else
{
IQueryable<Models.Officer> query = _Db.Officers;
if (!string.IsNullOrEmpty(selectedState))
{
query = query.Where(o => o.State == selectedState);
}
officers = query.ToList();
}
ViewBag.SelectedState = selectedState;
ViewBag.States = _Db.Officers.Select(o => o.State).Distinct().ToList();
return View("Index", officers);
}
}
Page:
@model IEnumerable<Officer>
@{
ViewData["Title"] = "Search Officers";
}
<!DOCTYPE html>
<html>
<head>
<title>@ViewData["Title"]</title>
</head>
<body>
<form id="searchForm" method="post" action="/Officer/Search">
<select name="selectedState" id="selectedState">
<option value=">Select a State . . .</option>
@foreach (var state in ViewBag.States)
{
<!option value="@state @(state == ViewBag.SelectedState ? "selected" : "")>@state</!option>
}
</select>
</form>
@if (Model != null && Model.Any())
{
<table class="table table-bordered table-striped" style="width: 100%; background-color: lightgray; border: 1px solid black;">
<thead>
<tr>
<th style="color: blue">First Name</th>
<th style="color: blue">Last Name</th>
<th style="color: blue">Phone</th>
<th style="color: blue">Cell Phone</th>
<th style="color: blue">City</th>
<th style="color: blue">State</th>
<th style="color: blue">Zip Code</th>
<th style="color: blue">Agency</th>
<th style="color: blue">Address</th>
<th style="color: blue">Date Modified</th>
</tr>
</thead>
@foreach (var officer in Model)
{
<tr>
<td>@officer.FirstName</td>
<td>@officer.LastName</td>
<td>@officer.Phone</td>
<td>@officer.CellPhone</td>
<td>@officer.City</td>
<td>@officer.State</td>
<td>@officer.PostalCode</td>
<td>@officer.AgencyName</td>
<td>@officer.Address1</td>
<td>@officer.DateUpdated</td>
</tr>
}
</table>
}
<script src=https://code.jquery.com/jquery-3.6.0.min.js></script>
<script>
$('#selectedState').change(function () {
$('#searchForm').submit();
});
</script>
</body>
</html>
Since the initial issue has been resolved, you could to create a new post if you encounter any further problems.