如何通過單擊VB.net中Web應用程式上的鏈接來過濾SQL查詢表？-有解無憂

在用戶單擊側邊欄中的鏈接后，我無法通過推送 SQL 查詢來過濾表。我正在使用示例資料庫 Northwind 來創建這個應用程式。我已經實作了搜索功能和排序功能。用戶應該能夠使用排序、搜索和過濾的任意組合。

這是我在創建此應用程式的大部分程序中所遵循的教程（減去分頁；我將所有內容都保留在一頁上）。我還洗掉了編輯、添加和洗掉條目的功能。我只想能夠查看串列視圖中的條目。

下面是我的代碼 - 我已經到了我認為它會過濾但按下側選單上的鏈接時沒有任何反應的地步。文本被推送到 URL 的末尾，僅此而已。

索引.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 class="sidebar">
        <!--profile image & text-->
        <!--menu item-->
        <br />
        <br />
        <br />
        <div class="profile">
            <img src="logo.jpg">
            <h3>Directory</h3>
            <p>Filter by Locations:</p>
        </div>


        <ul>
            <li>
                <a href="#" class="active">
                    <span class="icon"><i class="fas fa-home"></i></span>
                    <span class="item">All Locations</span>
                </a>
            </li>
            <li>
                <a href="#Argentina">
                    <span class="icon"><i class="fas fa-desktop"></i></span>
                    <span class="item">Argentina</span>
                </a>
            </li>
            <li>
                <a href="#Austria">
                    <span class="icon"><i class="fas fa-user-friends"></i></span>
                    <span class="item">Austria</span>
                </a>
            </li>
            <li>
                <a href="#Belgium">
                    <span class="icon"><i class="fas fa-tachometer-alt"></i></span>
                    <span class="item">Belgium</span>
                </a>
            </li>
            <li>
                <a href="#Brazil">
                    <span class="icon"><i class="fas fa-database"></i></span>
                    <span class="item">Brazil</span>
                </a>
            </li>
            <li>
                <a href="#Canada">
                    <span class="icon"><i class="fas fa-chart-line"></i></span>
                    <span class="item">Canada</span>
                </a>
            </li>            
            <li>
                <a href="#France">
                    <span class="icon"><i class="fas fa-cog"></i></span>
                    <span class="item">France</span>
                </a>
            </li>
            <li>
                <a href="#Germany">
                    <span class="icon"><i class="fas fa-cog"></i></span>
                    <span class="item">Germany</span>
                </a>
            </li>           
            <li>
                <a href="#Portugal">
                    <span class="icon"><i class="fas fa-cog"></i></span>
                    <span class="item">Portugal</span>
                </a>
            </li>
                      
            <li>
                <a href="#USA">
                    <span class="icon"><i class="fas fa-cog"></i></span>
                    <span class="item">USA</span>
                </a>
            </li>
            <li>
                <a href="#Venezuela">
                    <span class="icon"><i class="fas fa-cog"></i></span>
                    <span class="item">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 class="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>

客戶控制器.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

uj5u.com熱心網友回復：

我想出了我需要改變什么才能在側邊欄過濾器中創建鏈接。他們需要成為ActionLinks才能在 SQL 表中實際過濾。從此改變：

<a href="#Argentina">

對此：

@Html.ActionLink("Argentina", "Index", New With {.sortOrder = ViewBag.NameSortParm, .currentFilter = ViewBag.CurrentFilter, .countryString = "Argentina"})

我還需要修復If控制器中的陳述句以允許搜索和過濾。從此改變：

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

對此：

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

這是更新的代碼：

索引.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 class="sidebar">
        <!--profile image & text-->
        <!--menu item-->
        <br />
        <br />
        <br />
        <div class="profile">
            <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 class="icon"><i class="fas fa-home"></i></span>
                    <span class="item"></span>
            </li>
            <li>
                    @Html.ActionLink("Argentina", "Index", New With {.sortOrder = ViewBag.NameSortParm, .currentFilter = ViewBag.CurrentFilter, .countryString = "Argentina"})
                    <span class="icon"><i class="fas fa-desktop"></i></span>
                    <span class="item"></span>
            </li>
            <li>
                    @Html.ActionLink("Austria", "Index", New With {.sortOrder = ViewBag.NameSortParm, .currentFilter = ViewBag.CurrentFilter, .countryString = "Austria"})
                    <span class="icon"><i class="fas fa-user-friends"></i></span>
                    <span class="item"></span>
            </li>
            <li>
                @Html.ActionLink("Belgium", "Index", New With {.sortOrder = ViewBag.NameSortParm, .currentFilter = ViewBag.CurrentFilter, .countryString = "Belgium"})
                <span class="icon"><i class="fas fa-tachometer-alt"></i></span>
                <span class="item"></span>
            </li>
            <li>
                @Html.ActionLink("Brazil", "Index", New With {.sortOrder = ViewBag.NameSortParm, .currentFilter = ViewBag.CurrentFilter, .countryString = "Brazil"})
                <span class="icon"><i class="fas fa-database"></i></span>
                <span class="item"></span>
                
            </li>
            <li>
                @Html.ActionLink("Canada", "Index", New With {.sortOrder = ViewBag.NameSortParm, .currentFilter = ViewBag.CurrentFilter, .countryString = "Canada"})
                <span class="icon"><i class="fas fa-chart-line"></i></span>
                <span class="item"></span>
                     
            <li>
                @Html.ActionLink("France", "Index", New With {.sortOrder = ViewBag.NameSortParm, .currentFilter = ViewBag.CurrentFilter, .countryString = "France"})
                <span class="icon"><i class="fas fa-cog"></i></span>
                <span class="item"></span>
            </li>
            <li>
                @Html.ActionLink("Germany", "Index", New With {.sortOrder = ViewBag.NameSortParm, .currentFilter = ViewBag.CurrentFilter, .countryString = "Germany"})
                <span class="icon"><i class="fas fa-cog"></i></span>
                <span class="item"></span>
            </li>           
            <li>
                @Html.ActionLink("Portugal", "Index", New With {.sortOrder = ViewBag.NameSortParm, .currentFilter = ViewBag.CurrentFilter, .countryString = "Portugal"})
                <span class="icon"><i class="fas fa-cog"></i></span>
                <span class="item"></span>
            </li>
                      
            <li>
                @Html.ActionLink("USA", "Index", New With {.sortOrder = ViewBag.NameSortParm, .currentFilter = ViewBag.CurrentFilter, .countryString = "USA"})
                <span class="icon"><i class="fas fa-cog"></i></span>
                <span class="item"></span>     
            </li>
            <li>
                @Html.ActionLink("Venezuela", "Index", New With {.sortOrder = ViewBag.NameSortParm, .currentFilter = ViewBag.CurrentFilter, .countryString = "Venezuela"})
                <span class="icon"><i class="fas fa-cog"></i></span>
                <span class="item"></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 class="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>

客戶控制器.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

我使用這個網站（https://www.completecsharptutorial.com/asp-net-mvc5/html-actionlink-complete-example-aspnet-mvc5.php）來幫助解決ActionLinks.

轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/459309.html

標籤：VB.net

上一篇：通過文本查找控制元件并使用Ctype分配自定義屬性或項是字典中的控制元件

下一篇：ByVal表現得像ByRef