查询不使用sqlite数据库 [英] Query not working with sqlite database

问题描述

我有一个桌面应用程序，其中我使用c sharp与SQL服务器，当我使用sql server作为数据库时，下面的查询从数据库中获取数据但是当我使用sqlite数据库时，下面的查询确实没有返回.tell对于sql和sqlite，在c sharp中编写内联查询有什么不同，或者MAX和like函数在SQL和SQLITE中的工作方式不同，下面是我的查询。

 com.CommandText =   SELECT MAX（DocumentCode）AS DocumentCode FROM DocumentsHeader WHERE（DocumentCode比如@DocumentCode +'％'）; 
 com.Parameters.Add（  @ DocumentCode，SqlDbType.NVarChar， 18 ）。Value = Passvalue.ToString（）。Trim（）; 
 returnvalue = com.ExecuteScalar（）; 

此查询在使用sql时获取记录，在使用sqlite时它会获取nt当我检查我的Db有记录时记录。

任何人都可以帮我...

解决方案

你应该改变你的参数代码包含SQL通配符。

 com.Parameters.Add（  @ DocumentCode，SqlDbType.NVarChar）.Value = Passvalue.ToString（）。Trim（）+  ％; 

SQL无法正常工作的原因是查询的这一部分

< pre lang =c＃> DocumentCode LIKE @DocumentCode + ' ％'

成为这个SQL

 DocumentCode  LIKE  '  DOCCODE' + ' ％' 

SQL变为字符串连接，而不是带通配符的值。

MAX函数不适用于非数字字段。您的 DocumentCode 字段显然定义为 nvarchar ，这是非数字的。



我的坏。它适用于字符字段。如果我在发言前通过文档验证会有所帮助。我从来没有机会在字符数据上使用MAX。

I have a desktop application in which i have used c sharp with SQL server the below query fetches the data from the database when i am using the sql server as database but when i used sqlite database the below query does return nothing .tell me is there any different in writing inline queries in c sharp for sql and sqlite , or does the MAX or like function works differently in SQL and SQLITE below is my query.

com.CommandText = "SELECT MAX(DocumentCode) AS DocumentCode FROM DocumentsHeader WHERE (DocumentCode LIKE @DocumentCode + '%')";
com.Parameters.Add("@DocumentCode", SqlDbType.NVarChar, 18).Value = Passvalue.ToString().Trim();
returnvalue = com.ExecuteScalar();

This query fetches record when using sql and when using sqlite it does nt fetching record while i check that my Db has the record .
any one can assists me ...

解决方案

You should alter your parameter code to include the SQL wildcard.

com.Parameters.Add("@DocumentCode", SqlDbType.NVarChar).Value = Passvalue.ToString().Trim() + "%";

The reason that your SQL wasn't working is that this part of the query

DocumentCode LIKE @DocumentCode + '%'

Becomes this SQL

DocumentCode LIKE 'DOCCODE' + '%'

The SQL becomes a string concatenation, not a value with a wildcard.

The MAX function does not work on non-numeric fields. Your DocumentCode field is apparently defined as nvarchar, which is non-numeric.

My bad. It DOES work with character fields. It would help if I verified with the documentation before speaking. I've never had occasion to use MAX on character data.

这篇关于查询不使用sqlite数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！