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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users