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
