I am getting the following exception in my sqlite query:
The expression contains undefined function call lower()
I've got a very simple SQLite typed dataset in VS2012.
I'm parsing a large amount of data and want to check if a row already exists prior to adding a new row.
I'm using the .select() method to return an array of datarows. If the length of the array is zero I add a new new. Simple!
All has been working well, but I have added a check to prevent duplicate rows being added if the case is wrong. I am storing filenames.
The select statement is:
"lower(FileName) = '" + EscapeFileName(strFileName).ToLower() + "'"
However I get the above error on the lower(FileName). If I remove this is works fine, but obviously the check is not comparable.
The statement works as expected in SQLiteAdmin.
If I understand correctly, you have a .NET DataTable (the result of a query) and you are calling DataTable.Select(string) passing in a where clause.
This where clause is processed by .NET, not by SQLite (as pointed out by Tim). You cannot use SQLite (or any other DBMS) function in the Select function for the DataTable. You can use these functions:
- CONVERT – converts particular expression to a specified .NET Framework type
- LEN – gets the length of a string
- ISNULL – checks an expression and either returns the checked expression or a replacement value
- IIF – gets one of two values depending on the result of a logical expression
- TRIM – removes all leading and trailing blank characters like \r, \n, \t, ‚ ‘
- SUBSTRING – gets a sub-string of a specified length, starting at a specified point in the string
See DataView RowFilter Syntax [C#] for more information.