全文编程查询不返回结果-SQL Server-参数问题 [英] Full-Text Programmatic Query not returning results - SQL Server - issue with Parameter

问题描述

全文编程查询不返回结果-SQL Server

我在SQL Server 2008中使用全文本搜索，并通过C#2008应用程序进行连接.我有一个到数据库的连接字符串，该字符串已经成功地与常规SELECT语句和存储过程一起使用了很多次.我正在尝试对特定表实施全文搜索，并且已经为全文搜索配置了目录和索引.

但是，我正在以编程方式进行似乎不起作用的全文查询.我的代码向参数添加了正确的值，但是只要我使用参数，查询就不会返回任何结果.如果我继续将搜索词粘贴到参数所在的位置，并将其保留在引号内，则可以从SSMS中完全相同的查询中获取结果.但是，当我运行代码时，我的SqlDataReader返回没有任何结果.

这是代码生成的内容(不返回任何结果):

Full-Text Programmatic Query not returning results - SQL Server

I am using full-text search in SQL Server 2008 and am connecting through a C# 2008 application. I have a connection string to my database which I''ve used successfully many times with regular SELECT statements and stored procedures. I''m trying to implement full-text search for a particular table, and I already configured my catalog and index for full-text search.

However, I''m programmatically making a full-text query that doesn''t seem to work. My code adds the correct value to the parameter but the query won''t return any results as long as I use the parameter. If I go ahead and paste the search word into where the parameter is and keep it within quotes, I get results from the exact same query in SSMS. But when I run the code, my SqlDataReader comes back with no results.

Here is what the code generates (which returns no results) :

Set NOCOUNT ON SELECT TOP 5 * FROM 
(SELECT JobID, ROW_NUMBER() OVER (ORDER BY createdDate) AS num 
  FROM Jobs 
  WHERE isActive=''true'' 
    AND ( CONTAINS(([JobDuties]), ''FORMSOF(INFLECTIONAL, \"@Argument0\")'')) ) AS a 
          WHERE num > 0

但是，当我进入SSMS并像这样手动粘贴自变量时(有效):

Yet, when I go to SSMS and paste an argument in manually like this (it works) :

Set NOCOUNT ON SELECT TOP 5 * FROM 
(SELECT JobID, ROW_NUMBER() OVER (ORDER BY createdDate) AS num 
  FROM Jobs 
  WHERE isActive=''true'' 
    AND ( CONTAINS(([JobDuties]), ''FORMSOF(INFLECTIONAL, "develop")'')) ) AS a 
          WHERE num > 0

因此，我知道问题与论点有关，但我无法弄清楚.我在其他查询中以编程方式完全相同的方式添加参数，并且它可以正常工作.

我还测试了该应用程序，以仍然使用\转义符将双引号传递给查询，它仍然有效.我只是想不出为什么它不能用作参数.有什么线索吗?

谢谢!!

So, I know the problem has to do with the argument but I can''t figure it out. I have other queries where I add arguments the exact same way programmatically and it works.

I also tested the application to pass the search string right into the query still using the \" escapes for double quotes and it works. I just can''t figure why it won''t work as a parameter. Any clues?

Thanks!!

推荐答案

确保您的@ Argument0变量足够大以容纳您的文本字段.如果不是，则表明查询为何无法按预期工作.

Make sure that your @Argument0 variable is large enough to hold your text field. If it is not, then that would indicate why the query won''t work as expected.

这篇关于全文编程查询不返回结果-SQL Server-参数问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！