I am having trouble with pushing the SQL query through to filter the table after the user clicks on a link in the sidebar. I am using the sample database Northwind to create this app. I have implemented a search function and sorting functions already. The user should be able to use any combination of sorting, searching, and filtering.
This is the tutorial I followed for the majority of creating this application (minus the paging; I have kept everything on one page). I also removed the functionality for editing, adding, and deleting entries. I only want to be able to view the entries in the list view.
Below is my code - I have gotten to the point where I thought it would filter but nothing happens when the links on the side menu are pressed. The text is pushed to the end of the URL but that is it.
Index.vbhtml
@ModelType IEnumerable(Of WebApplication1_testdirectory.Customer)
@Code
ViewData("Title") = "Customers"
End Code
<!DOCTYPE html>
<html>
<head>
<style>
table {
border-collapse: collapse;
width: 100%;
}
th, td {
text-align: left;
padding: 8px;
}
tr:nth-child(even) {
background-color: #f2f2f2;
}
* {
list-style: none;
text-decoration: none;
margin: 0;
padding: 0;
box-sizing: border-box;
font-family: 'Open Sans', sans-serif;
}
body {
background: #f5f6fa;
}
.wrapper .sidebar {
background: rgb(5, 68, 104);
position: fixed;
top: 0;
left: 0;
width: 225px;
height: 100%;
padding: 20px 0;
transition: all 0.5s ease;
}
.wrapper .sidebar .profile {
margin-bottom: 30px;
text-align: center;
}
.wrapper .sidebar .profile img {
display: block;
width: 100px;
height: 100px;
border-radius: 50%;
margin: 0 auto;
}
.wrapper .sidebar .profile h3 {
color: #ffffff;
margin: 10px 0 5px;
}
.wrapper .sidebar .profile p {
color: rgb(206, 240, 253);
font-size: 14px;
}
.wrapper .sidebar ul li a {
display: block;
padding: 13px 30px;
border-bottom: 1px solid #10558d;
color: rgb(241, 237, 237);
font-size: 16px;
position: relative;
}
.wrapper .sidebar ul li a .icon {
color: #dee4ec;
width: 30px;
display: inline-block;
}
.wrapper .sidebar ul li a:hover,
.wrapper .sidebar ul li a.active {
color: #0c7db1;
background: white;
border-right: 2px solid rgb(5, 68, 104);
}
.wrapper .sidebar ul li a:hover .icon,
.wrapper .sidebar ul li a.active .icon {
color: #0c7db1;
}
.wrapper .sidebar ul li a:hover:before,
.wrapper .sidebar ul li a.active:before {
display: block;
}
.wrapper .section {
width: calc(100% - 225px);
margin-left: 225px;
transition: all 0.5s ease;
}
.wrapper .section .top_navbar {
background: rgb(7, 105, 185);
height: 50px;
display: flex;
align-items: center;
padding: 0 30px;
}
.wrapper .section .top_navbar .hamburger a {
font-size: 28px;
color: #f4fbff;
}
.wrapper .section .top_navbar .hamburger a:hover {
color: #a2ecff;
}
body.active .wrapper .sidebar {
left: -225px;
}
body.active .wrapper .section {
margin-left: 0;
width: 100%;
}
</style>
<meta name="viewport" content="width=device-width" />
<title>Northwind Employee Directory</title>
<link rel="stylesheet" href="style.css" />
<link rel="stylesheet" href="Gridmvc.css" />
</head>
<body>
<h2>Customers Directory</h2>
<!--Top menu -->
<div >
<!--profile image & text-->
<!--menu item-->
<br />
<br />
<br />
<div >
<img src="logo.jpg">
<h3>Directory</h3>
<p>Filter by Locations:</p>
</div>
<ul>
<li>
<a href="#" >
<span ><i ></i></span>
<span >All Locations</span>
</a>
</li>
<li>
<a href="#Argentina">
<span ><i ></i></span>
<span >Argentina</span>
</a>
</li>
<li>
<a href="#Austria">
<span ><i ></i></span>
<span >Austria</span>
</a>
</li>
<li>
<a href="#Belgium">
<span ><i ></i></span>
<span >Belgium</span>
</a>
</li>
<li>
<a href="#Brazil">
<span ><i ></i></span>
<span >Brazil</span>
</a>
</li>
<li>
<a href="#Canada">
<span ><i ></i></span>
<span >Canada</span>
</a>
</li>
<li>
<a href="#France">
<span ><i ></i></span>
<span >France</span>
</a>
</li>
<li>
<a href="#Germany">
<span ><i ></i></span>
<span >Germany</span>
</a>
</li>
<li>
<a href="#Portugal">
<span ><i ></i></span>
<span >Portugal</span>
</a>
</li>
<li>
<a href="#USA">
<span ><i ></i></span>
<span >USA</span>
</a>
</li>
<li>
<a href="#Venezuela">
<span ><i ></i></span>
<span >Venezuela</span>
</a>
</li>
</ul>
</div>
</div>
@Using Html.BeginForm()
@<p>
Search by name: @Html.TextBox("SearchString")
<input type="submit" value="Search" />
</p>
End Using
<table >
<tr>
<th>
@Html.DisplayNameFor(Function(model) model.CompanyName)
</th>
<th>
@Html.ActionLink("Name", "Index", New With {.sortOrder = ViewBag.NameSortParm, .currentFilter = ViewBag.CurrentFilter})
</th>
<th>
@Html.ActionLink("Title", "Index", New With {.sortOrder = ViewBag.TitleSortParm, .currentFilter = ViewBag.CurrentFilter})
</th>
<th>
@Html.DisplayNameFor(Function(model) model.Address)
</th>
<th>
@Html.DisplayNameFor(Function(model) model.City)
</th>
<th>
@Html.DisplayNameFor(Function(model) model.Country)
</th>
<th>
@Html.DisplayNameFor(Function(model) model.Phone)
</th>
<th>
@Html.DisplayNameFor(Function(model) model.Fax)
</th>
</tr>
@For Each item In Model
@<tr>
<td>
@Html.DisplayFor(Function(modelItem) item.CompanyName)
</td>
<td>
@Html.DisplayFor(Function(modelItem) item.ContactName)
</td>
<td>
@Html.DisplayFor(Function(modelItem) item.ContactTitle)
</td>
<td>
@Html.DisplayFor(Function(modelItem) item.Address)
</td>
<td>
@Html.DisplayFor(Function(modelItem) item.City)
</td>
<td>
@Html.DisplayFor(Function(modelItem) item.Country)
</td>
<td>
@Html.DisplayFor(Function(modelItem) item.Phone)
</td>
<td>
@Html.DisplayFor(Function(modelItem) item.Fax)
</td>
</tr>
Next
</table>
</body>
</html>
CustomersController.vb
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Entity
Imports System.Linq
Imports System.Net
Imports System.Web
Imports System.Web.Mvc
Imports WebApplication1_testdirectory
Imports PagedList
Namespace Controllers
Public Class CustomersController
Inherits System.Web.Mvc.Controller
Private db As New NorthwindEntities1
' GET: Customers
Function Index(ByVal sortOrder As String, searchString As String, countryString As String) As ActionResult
If (Not String.IsNullOrEmpty(countryString)) Then
countryString = countryString.Substring(1)
End If
ViewBag.CurrentSort = sortOrder
ViewBag.NameSortParm = If(String.IsNullOrEmpty(sortOrder), "name_desc", String.Empty)
ViewBag.TitleSortParm = If(sortOrder = "Title", "title_desc", "Title")
ViewBag.CountryFilter = If(String.IsNullOrEmpty(countryString), String.Empty, countryString)
Dim customers = From c In db.Customers Select c
If Not String.IsNullOrEmpty(searchString) Then
customers = customers.Where(Function(c) (c.ContactTitle.ToUpper().Contains(searchString.ToUpper()) _
Or c.ContactName.ToUpper().Contains(searchString.ToUpper())) And
(c.Country.ToUpper().Contains(countryString.ToUpper()) Or String.IsNullOrEmpty(countryString)))
End If
Select Case sortOrder
Case "name_desc"
customers = customers.OrderByDescending(Function(c) c.ContactName)
Case "title_desc"
customers = customers.OrderBy(Function(c) c.ContactTitle)
Case Else
customers = customers.OrderBy(Function(c) c.ContactName)
End Select
Return View(customers.ToList())
End Function
Protected Overrides Sub Dispose(ByVal disposing As Boolean)
If (disposing) Then
db.Dispose()
End If
MyBase.Dispose(disposing)
End Sub
End Class
End Namespace
CodePudding user response：
I figured out what I needed to change in order to make the links in the sidebar filter. They needed to become ActionLinks in order to actually filter in the SQL table.
Changed from this:
<a href="#Argentina">
To this:
@Html.ActionLink("Argentina", "Index", New With {.sortOrder = ViewBag.NameSortParm, .currentFilter = ViewBag.CurrentFilter, .countryString = "Argentina"})
I also needed to fix the If statements in the controller to allow for both searching and filtering.
Changed from this:
If Not String.IsNullOrEmpty(searchString) Then
customers = customers.Where(Function(c) (c.ContactTitle.ToUpper().Contains(searchString.ToUpper()) _
Or c.ContactName.ToUpper().Contains(searchString.ToUpper())) And
(c.Country.ToUpper().Contains(countryString.ToUpper()) Or String.IsNullOrEmpty(countryString)))
End If
To this:
If Not String.IsNullOrEmpty(searchString) And Not String.IsNullOrEmpty(countryString) Then
customers = customers.Where(Function(c) (c.ContactTitle.ToUpper().Contains(searchString.ToUpper()) _
Or c.ContactName.ToUpper().Contains(searchString.ToUpper())) And
(c.Country.ToUpper().Contains(countryString.ToUpper())))
ElseIf Not String.IsNullOrEmpty(searchString) And String.IsNullOrEmpty(countryString) Then
customers = customers.Where(Function(c) (c.ContactTitle.ToUpper().Contains(searchString.ToUpper()) _
Or c.ContactName.ToUpper().Contains(searchString.ToUpper())))
ElseIf String.IsNullOrEmpty(searchString) And Not String.IsNullOrEmpty(countryString) Then
customers = customers.Where(Function(c) (c.Country.ToUpper().Contains(countryString.ToUpper())))
End If
Here's the updated code:
Index.vbhtml:
@ModelType IEnumerable(Of WebApplication1_testdirectory.Customer)
@Code
ViewData("Title") = "Customers"
End Code
<!DOCTYPE html>
<html>
<head>
<style>
table {
border-collapse: collapse;
width: 100%;
}
th, td {
text-align: left;
padding: 8px;
}
tr:nth-child(even) {
background-color: #f2f2f2;
}
* {
list-style: none;
text-decoration: none;
margin: 0;
padding: 0;
box-sizing: border-box;
font-family: 'Open Sans', sans-serif;
}
body {
background: #f5f6fa;
}
.wrapper .sidebar {
background: rgb(5, 68, 104);
position: fixed;
top: 0;
left: 0;
width: 225px;
height: 100%;
padding: 20px 0;
transition: all 0.5s ease;
}
.wrapper .sidebar .profile {
margin-bottom: 30px;
text-align: center;
}
.wrapper .sidebar .profile img {
display: block;
width: 100px;
height: 100px;
border-radius: 50%;
margin: 0 auto;
}
.wrapper .sidebar .profile h3 {
color: #ffffff;
margin: 10px 0 5px;
}
.wrapper .sidebar .profile p {
color: rgb(206, 240, 253);
font-size: 14px;
}
.wrapper .sidebar ul li a {
display: block;
padding: 13px 30px;
border-bottom: 1px solid #10558d;
color: rgb(241, 237, 237);
font-size: 16px;
position: relative;
}
.wrapper .sidebar ul li a .icon {
color: #dee4ec;
width: 30px;
display: inline-block;
}
.wrapper .sidebar ul li a:hover,
.wrapper .sidebar ul li a.active {
color: #0c7db1;
background: white;
border-right: 2px solid rgb(5, 68, 104);
}
.wrapper .sidebar ul li a:hover .icon,
.wrapper .sidebar ul li a.active .icon {
color: #0c7db1;
}
.wrapper .sidebar ul li a:hover:before,
.wrapper .sidebar ul li a.active:before {
display: block;
}
.wrapper .section {
width: calc(100% - 225px);
margin-left: 225px;
transition: all 0.5s ease;
}
.wrapper .section .top_navbar {
background: rgb(7, 105, 185);
height: 50px;
display: flex;
align-items: center;
padding: 0 30px;
}
.wrapper .section .top_navbar .hamburger a {
font-size: 28px;
color: #f4fbff;
}
.wrapper .section .top_navbar .hamburger a:hover {
color: #a2ecff;
}
body.active .wrapper .sidebar {
left: -225px;
}
body.active .wrapper .section {
margin-left: 0;
width: 100%;
}
</style>
<meta name="viewport" content="width=device-width" />
<title>Northwind Employee Directory</title>
<link rel="stylesheet" href="style.css" />
<link rel="stylesheet" href="Gridmvc.css" />
</head>
<body>
<h2>Customers Directory</h2>
<!--Top menu -->
<div >
<!--profile image & text-->
<!--menu item-->
<br />
<br />
<br />
<div >
<img src="logo.jpg">
<h3>Directory</h3>
<p>Filter by Locations:</p>
</div>
<ul>
<li>
@Html.ActionLink("All Locations", "Index", New With {.sortOrder = ViewBag.NameSortParm, .currentFilter = ViewBag.CurrentFilter, .countryString = ""})
<span ><i ></i></span>
<span ></span>
</li>
<li>
@Html.ActionLink("Argentina", "Index", New With {.sortOrder = ViewBag.NameSortParm, .currentFilter = ViewBag.CurrentFilter, .countryString = "Argentina"})
<span ><i ></i></span>
<span ></span>
</li>
<li>
@Html.ActionLink("Austria", "Index", New With {.sortOrder = ViewBag.NameSortParm, .currentFilter = ViewBag.CurrentFilter, .countryString = "Austria"})
<span ><i ></i></span>
<span ></span>
</li>
<li>
@Html.ActionLink("Belgium", "Index", New With {.sortOrder = ViewBag.NameSortParm, .currentFilter = ViewBag.CurrentFilter, .countryString = "Belgium"})
<span ><i ></i></span>
<span ></span>
</li>
<li>
@Html.ActionLink("Brazil", "Index", New With {.sortOrder = ViewBag.NameSortParm, .currentFilter = ViewBag.CurrentFilter, .countryString = "Brazil"})
<span ><i ></i></span>
<span ></span>
</li>
<li>
@Html.ActionLink("Canada", "Index", New With {.sortOrder = ViewBag.NameSortParm, .currentFilter = ViewBag.CurrentFilter, .countryString = "Canada"})
<span ><i ></i></span>
<span ></span>
<li>
@Html.ActionLink("France", "Index", New With {.sortOrder = ViewBag.NameSortParm, .currentFilter = ViewBag.CurrentFilter, .countryString = "France"})
<span ><i ></i></span>
<span ></span>
</li>
<li>
@Html.ActionLink("Germany", "Index", New With {.sortOrder = ViewBag.NameSortParm, .currentFilter = ViewBag.CurrentFilter, .countryString = "Germany"})
<span ><i ></i></span>
<span ></span>
</li>
<li>
@Html.ActionLink("Portugal", "Index", New With {.sortOrder = ViewBag.NameSortParm, .currentFilter = ViewBag.CurrentFilter, .countryString = "Portugal"})
<span ><i ></i></span>
<span ></span>
</li>
<li>
@Html.ActionLink("USA", "Index", New With {.sortOrder = ViewBag.NameSortParm, .currentFilter = ViewBag.CurrentFilter, .countryString = "USA"})
<span ><i ></i></span>
<span ></span>
</li>
<li>
@Html.ActionLink("Venezuela", "Index", New With {.sortOrder = ViewBag.NameSortParm, .currentFilter = ViewBag.CurrentFilter, .countryString = "Venezuela"})
<span ><i ></i></span>
<span ></span>
</li>
</ul>
</div>
</div>
@Using Html.BeginForm()
@<p>
Search by name or title: @Html.TextBox("SearchString")
<input type="submit" value="Search" />
</p>
End Using
<table >
<tr>
<th>
@Html.DisplayNameFor(Function(model) model.CompanyName)
</th>
<th>
@Html.ActionLink("Name", "Index", New With {.sortOrder = ViewBag.NameSortParm, .currentFilter = ViewBag.CurrentFilter})
</th>
<th>
@Html.ActionLink("Title", "Index", New With {.sortOrder = ViewBag.TitleSortParm, .currentFilter = ViewBag.CurrentFilter})
</th>
<th>
@Html.DisplayNameFor(Function(model) model.Address)
</th>
<th>
@Html.DisplayNameFor(Function(model) model.City)
</th>
<th>
@Html.DisplayNameFor(Function(model) model.Country)
</th>
<th>
@Html.DisplayNameFor(Function(model) model.Phone)
</th>
<th>
@Html.DisplayNameFor(Function(model) model.Fax)
</th>
</tr>
@For Each item In Model
@<tr>
<td>
@Html.DisplayFor(Function(modelItem) item.CompanyName)
</td>
<td>
@Html.DisplayFor(Function(modelItem) item.ContactName)
</td>
<td>
@Html.DisplayFor(Function(modelItem) item.ContactTitle)
</td>
<td>
@Html.DisplayFor(Function(modelItem) item.Address)
</td>
<td>
@Html.DisplayFor(Function(modelItem) item.City)
</td>
<td>
@Html.DisplayFor(Function(modelItem) item.Country)
</td>
<td>
@Html.DisplayFor(Function(modelItem) item.Phone)
</td>
<td>
@Html.DisplayFor(Function(modelItem) item.Fax)
</td>
</tr>
Next
</table>
</body>
</html>
CustomersController.vb:
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Entity
Imports System.Linq
Imports System.Net
Imports System.Web
Imports System.Web.Mvc
Imports WebApplication1_testdirectory
Imports PagedList
Namespace Controllers
Public Class CustomersController
Inherits System.Web.Mvc.Controller
Private db As New NorthwindEntities1
' GET: Customers
Function Index(ByVal sortOrder As String, searchString As String, countryString As String) As ActionResult
ViewBag.CurrentSort = sortOrder
ViewBag.NameSortParm = If(String.IsNullOrEmpty(sortOrder), "name_desc", String.Empty)
ViewBag.TitleSortParm = If(sortOrder = "Title", "title_desc", "Title")
ViewBag.CountryFilter = If(String.IsNullOrEmpty(countryString), String.Empty, countryString)
Dim customers = From c In db.Customers Select c
If Not String.IsNullOrEmpty(searchString) And Not String.IsNullOrEmpty(countryString) Then
customers = customers.Where(Function(c) (c.ContactTitle.ToUpper().Contains(searchString.ToUpper()) _
Or c.ContactName.ToUpper().Contains(searchString.ToUpper())) And
(c.Country.ToUpper().Contains(countryString.ToUpper())))
ElseIf Not String.IsNullOrEmpty(searchString) And String.IsNullOrEmpty(countryString) Then
customers = customers.Where(Function(c) (c.ContactTitle.ToUpper().Contains(searchString.ToUpper()) _
Or c.ContactName.ToUpper().Contains(searchString.ToUpper())))
ElseIf String.IsNullOrEmpty(searchString) And Not String.IsNullOrEmpty(countryString) Then
customers = customers.Where(Function(c) (c.Country.ToUpper().Contains(countryString.ToUpper())))
End If
Select Case sortOrder
Case "name_desc"
customers = customers.OrderByDescending(Function(c) c.ContactName)
Case "title_desc"
customers = customers.OrderBy(Function(c) c.ContactTitle)
Case Else
customers = customers.OrderBy(Function(c) c.ContactName)
End Select
Return View(customers.ToList())
End Function
Protected Overrides Sub Dispose(ByVal disposing As Boolean)
If (disposing) Then
db.Dispose()
End If
MyBase.Dispose(disposing)
End Sub
End Class
End Namespace
I used this site (https://www.completecsharptutorial.com/asp-net-mvc5/html-actionlink-complete-example-aspnet-mvc5.php) to help with the ActionLinks.