关键字搜索的参数查询 [英] Parameter query for keyword search

问题描述

我正在尝试为Inventory Item搜索创建参数查询。



似乎我做错了，因为它在Query builder窗口（Visual studio）中运行'Execute Query'时返回错误。有人可以帮我找到问题吗？



以下是详细信息



错误1：SQL语法遇到的错误



'@'附近的Where子句出错。

无法解析查询文本



错误2：SQL执行错误



错误消息：没有给出一个或多个必需参数的值



我尝试过：

 SELECT产品。[产品名称]，产品。 [产品代码]，[品牌销售]。[品牌名称]，[主要组别]。[组名]，类别。[Catogory名称]，SubCatogory。[SubCatogory名称] 
 FROM（（（（（（产品INNER JOIN 
类别ON产品。[Category-ID] = Category.ID）INNER JOIN 
 [销售品牌] ON产品。[品牌名称] = [品牌销售] .ID）INNER JOIN 
 [主要组] ON产品。[Group-ID] = [主要组] .ID和类别。[Group-ID] = [主要组] .ID）INNER JOIN 
类别Category_1 ON产品。[Category-ID] = Category_1.ID AND [Main Group] .ID = Category_1。[Group-ID]）INNER JOIN 
 SubCatory ON产品。[SubCategory-ID] = SubCatogory.ID AND Category.ID = SubCatogory。[Catogory-ID] AND Category_1.ID = SubCatogory。[Catogory-ID]）
 WHERE（产品。[产品名称] LIKE'％'+ @ Searchbox +'％'）

解决方案

您的代码看起来像这样（警告 - 未经测试）

  Dim  sql  As   String  =   SELECT产品。[产品名称]，产品。[产品代码]，[待售品牌]。[品牌名称]，[主要组别]。[组名]，类别。[Catogory名称]，SubCatogory。[SubCatogory名称] 
 FROM（（（（产品INNER JOIN 
类别ON产品。[Category-ID] = Category.ID）INNER JOIN 
 [销售品牌] ON产品。[品牌名称] = [品牌销售] .ID）INNER JOIN 
 [主要群体] ON产品。[Group-ID] = [Main Group] .ID AND Category。[Group-ID] = [Main Group] .ID）INNER JOIN 
类别Category_1 ON产品。[Category-ID] = Category_1 .ID和[Main Group] .ID = Category_1。[Group-ID]）INNER JOIN 
 SubCatogory ON Products。[SubCategory-ID] = SubCatogory.ID AND Category.ID = SubCatogory。[Catogory-ID] AND Category_1.ID = SubCatogory。[Catogory-ID]）
 WHERE（产品。[产品名称] LIKE @Searchbox） 
  Dim  sqlcmd 作为 SqlCommand = yourconnection.CreateCommand（）
 sqlcmd.CommandText = sql 

然后在变量中包含％ - 注意 sql 是单个字符串，没有连接

 sqlcmd.Parameters.AddWithValue（  @ Searchbox，  ％ + searchbox.Text +  ％）

或者，如果您知道LIKE子句可以找到的值，则可以从参数数组中构建sql并且使用IN子句 - 请参阅参数化查询中的使用SQLIN（） - Microsoft：ASP.NET - Tek-Tips [ ^ ]



或者你可以将@Searchbox作为参数传递给存储过程并生成一些动态的sql并运行它。



或（如果您知道值）将表格传递给SP - 请参阅使用表格 - 有价值的参数在SQL Server和.NET中 [ ^

I am trying to create a parameter query for Inventory Item search.

Seems like I have done something wrong as it returns errors while running 'Execute Query' from Query builder window(Visual studio). Can somebody help me to find the issue?

Below are the details

Error 1: SQL Syntax Errors Encountered

Error in Where clause near '@'.
Unable to parse query text

Error 2: SQL Execution Error

Error Message: No value given for one or more required parameters

What I have tried:

SELECT        Products.[Product Name], Products.[Product code], [Brands For Sales].[Brand Name], [Main Group].[Group Name], Category.[Catogory Name], SubCatogory.[SubCatogory Name]
FROM            (((((Products INNER JOIN
                         Category ON Products.[Category-ID] = Category.ID) INNER JOIN
                         [Brands For Sales] ON Products.[Brand Name] = [Brands For Sales].ID) INNER JOIN
                         [Main Group] ON Products.[Group-ID] = [Main Group].ID AND Category.[Group-ID] = [Main Group].ID) INNER JOIN
                         Category Category_1 ON Products.[Category-ID] = Category_1.ID AND [Main Group].ID = Category_1.[Group-ID]) INNER JOIN
                         SubCatogory ON Products.[SubCategory-ID] = SubCatogory.ID AND Category.ID = SubCatogory.[Catogory-ID] AND Category_1.ID = SubCatogory.[Catogory-ID])
WHERE        (Products.[Product Name] LIKE '%' + @Searchbox + '%')

解决方案

Your code could look something like this (warning - untested)

Dim sql As String = "SELECT        Products.[Product Name], Products.[Product code], [Brands For Sales].[Brand Name], [Main Group].[Group Name], Category.[Catogory Name], SubCatogory.[SubCatogory Name]
FROM            (((((Products INNER JOIN
                         Category ON Products.[Category-ID] = Category.ID) INNER JOIN
                         [Brands For Sales] ON Products.[Brand Name] = [Brands For Sales].ID) INNER JOIN
                         [Main Group] ON Products.[Group-ID] = [Main Group].ID AND Category.[Group-ID] = [Main Group].ID) INNER JOIN
                         Category Category_1 ON Products.[Category-ID] = Category_1.ID AND [Main Group].ID = Category_1.[Group-ID]) INNER JOIN
                         SubCatogory ON Products.[SubCategory-ID] = SubCatogory.ID AND Category.ID = SubCatogory.[Catogory-ID] AND Category_1.ID = SubCatogory.[Catogory-ID])
WHERE        (Products.[Product Name] LIKE @Searchbox)"
Dim sqlcmd As SqlCommand = yourconnection.CreateCommand()
sqlcmd.CommandText = sql

Then include the "%" in the variable - note that sql is a single string, there is no concatenation

sqlcmd.Parameters.AddWithValue("@Searchbox", "%" + searchbox.Text + "%")

Alternatively if you know the values that would be found by the LIKE clause you can build up the sql from a parameter array and use an IN clause instead - see Using SQL "IN()" in parameterized query - Microsoft: ASP.NET - Tek-Tips[^]

Or you could pass @Searchbox as a parameter to a stored procedure and generate some dynamic sql and run that.

Or (if you know the values) pass a table to the SP - see Using Table-Valued Parameters in SQL Server and .NET[^]

这篇关于关键字搜索的参数查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！