Result error in System.Data.SQlite COLLATE NOCASE

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Result error in System.Data.SQlite COLLATE NOCASE

andrea-dev@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 DataTable        Try            connection.Open()            Dim reader As SQLiteDataReader = selectCommand.ExecuteReader()            If reader.HasRows Then                dt.Load(reader)            End If            reader.Close()        Catch ex As SQLiteException            MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")        Catch ex As Exception            MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")        Finally            connection.Close() End Try Return dt End Function
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Result error in System.Data.SQlite COLLATE NOCASE

Simon Slavin-3

On 12 Mar 2017, at 7:21pm, [hidden email] 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:

<https://www.sqlite.org/cli.html>

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

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

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users