带有文本字段C＃.NET的SQL SELECT语句 [英] SQL SELECT statement with text field C# .NET

问题描述

我有一个SQL数据库，它包含一个名为（users）的表，名称字段被定义为文本。

在C＃代码中我想在数据库中搜索这样的特定名称：

 SQLCommand c =  new  SQLCommand（连接）; 
 c.CommandType = CommandType.Text; 
 c.CommandText =   SELECT * FROM users WHERE name = \' + textbox1 .text +   \'; 
 adapter.Fill（dataset1，  users）; 

当我执行它时返回此错误：



数据类型text和varchar在等于运算符中不兼容。



有什么想法吗？

解决方案

我建​​议您在后面的代码中使用存储过程代替命令文本。 />


请参阅：从存储过程中返回数据 [ ^ ]

如何：执行返回行的存储过程 [ ^ ]



SP应如下所示：

  SELECT  * 
  FROM  [用户] 
  WHERE  [name] = @ username 

按方式：不要使用保留字 [ ^ ]作为字段名称等。

< blockquote> c.CommandText =SELECT * FROM users WHERE name Like'+ textbox1.text +';

或

c.CommandText =SELECT * FROM用户WHERE CONVERT（VARCHAR，name）='+ textbox1.text +';



文本已弃用更改为varchar（max）将更容易工作

I have an SQL database, it contains a table named (users), the name field is defined as text.
In the C# code I want to search the database for a specific name like this:

SQLCommand c = new SQLCommand(connection);
c.CommandType = CommandType.Text;
c.CommandText = "SELECT * FROM users WHERE name=\'" + textbox1.text + "\'";
adapter.Fill(dataset1,"users");

When I execute it returns this error:

The data types text and varchar are incompatible in the equal to operator.

Any ideas?

解决方案

I would suggest you to use Stored procedure instead command text in a code behind.

Please, see: Return Data from a Stored Procedure[^]
How to: Execute a Stored Procedure that Returns Rows[^]

SP should looks like this one:

SELECT *
FROM [users]
WHERE [name]=@username

By The Way: Do not use reserved words[^] as a name of fields, etc.

c.CommandText = "SELECT * FROM users WHERE name Like '" + textbox1.text + "'";
or
c.CommandText = "SELECT * FROM users WHERE CONVERT(VARCHAR, name) ='" + textbox1.text + "'";

text is deprecated Changing to varchar(max) will be easier to work.

这篇关于带有文本字段C＃.NET的SQL SELECT语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！