Re: [sqlite] Result error in System.Data.SQlite COLLATE NOCASE

[Simon Slavin]

On 12 Mar 2017, at 7:21pm, andrea-...@libero.it wrote:

> I've a simple select statement that have expected result in DB Browser for 
> SQLite (82 rows).Same statement return an error in VB.NET using 
> System.Data.SQLite (164 rows)

Please try the same SELECT command using the SQLite Command Line Shell:



downloadable as part of the 'Precompiled Binaries' for your platform:

https://www.sqlite.org/download.html

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Result error in System.Data.SQlite COLLATE NOCASE

[andrea-...@libero.it]

Hello everybody, 
I've a simple select statement that have expected result in DB Browser for 
SQLite (82 rows).Same statement return an error in VB.NET using 
System.Data.SQLite (164 rows)
Statement is: SELECT  * FROM  [tblDictionary] INNER JOIN [tblLanguage] ON 
[tblDictionary].[idLanguage] = [tblLanguage].[idLanguage] WHERE 
[tblLanguage].[LanguageName] like 'Italian'
In VB.NET I use SQLiteDataReader and rows contain LanguageName Italian (as 
expected) and Indonesian (???)
I've tried with following WHERE clause, all with the same problem:WHERE 
UPPER([tblLanguage].[LanguageName]) = UPPER('Italian')WHERE 
[tblLanguage].[LanguageName] = 'Italian'WHERE [tblLanguage].[LanguageName] = 
'Italian'  COLLATE NOCASE
To replicate problem, following database:   CREATE TABLE `tblLanguage` (
`idLanguage`integer NOT NULL PRIMARY KEY AUTOINCREMENT, 
`LanguageName`  varchar(30) NOT NULL);  CREATE TABLE `tblDictionary` (  
`idDictionary`  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
`idLanguage`integer NOT NULL,   `KeyValue`  nvarchar(300) 
NOT NULL, `DataValue` nvarchar(2000) NOT NULL,
FOREIGN KEY(`idLanguage`) REFERENCES `tblLanguage`(`idLanguage`));  
INSERT INTO tblLanguage (LanguageName) VALUES ('Indonesian');   INSERT INTO 
tblLanguage (LanguageName) VALUES ('Italian');  INSERT INTO tblDictionary 
(idLanguage, KeyValue, DataValue) VALUES (1, 'Delete', 'Hapus');  INSERT 
INTO tblDictionary (idLanguage, KeyValue, DataValue) VALUES (1, 'Edit', 
'Edit'); INSERT INTO tblDictionary (idLanguage, KeyValue, DataValue) VALUES (2, 
'Delete', 'Cancella');   INSERT INTO tblDictionary (idLanguage, KeyValue, 
DataValue) VALUES (2, 'Edit', 'Modifica');

VB CODE:
Public Function SelectByLanguage(ByVal strLanguage As String) As 
DataTable  Dim connection As SQLiteConnection = New 
SQLiteConnection("Data Source=H:\test.db;")Dim selectStatement As 
String _= "SELECT " _ " 
[tblDictionary].[idDictionary] " _ "
,[tblLanguage].[LanguageName] " _ "
,[tblDictionary].[KeyValue] " _ "
,[tblDictionary].[DataValue] " _ "FROM " _ "  
   [tblDictionary] " _ "INNER JOIN [tblLanguage] ON 
[tblDictionary].[idLanguage] = [tblLanguage].[idLanguage] " _ 
"WHERE [tblLanguage].[LanguageName] like 'Italian'"Dim selectCommand As 
New SQLiteCommand(selectStatement, connection)selectCommand.CommandType 
= CommandType.Text   Dim dt As New DataTableTry
connection.Open()Dim reader As SQLiteDataReader = 
selectCommand.ExecuteReader()If reader.HasRows Then
dt.Load(reader)End Ifreader.Close()Catch ex As 
SQLiteExceptionMsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
Catch ex As ExceptionMsgBox(ex.Message, MsgBoxStyle.Critical, 
"Error")Finallyconnection.Close()   End Try 
Return dt   End Function
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users