Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

this is my very first question, so excuse me if I am missing something or doing something wrong :)

Project
I am writing a .NET Application (Visual C# 2012 Express) which uses the ADO.NET System.Data.SQLite and has the task to search for SQLite databases in a specified folder. After that you can choose one or multiple DBs from a listview which will then get exported to HTML/CSV. Export in this case means:
- all data from database is getting read (tables/columns)
- null/empty databases are getting dropped before final export
- null/empty tables are getting dropped before final export
- null/empty columns are getting dropped before final export

Problem
To drop empty columns I am using the following SQLite statement for the CommandText:

object validation = null;
query.CommandText = String.Format("SELECT COUNT({0}) FROM {1} WHERE {0} IS NOT NULL AND {0} <> '';", header, table);
validation = query.ExecuteScalar();

Unfortunately this is causing ExecuteScalar() to throw an exception on one database, but works fine for all others.

If I am 'single quoting' arguments like so...

object validation = null;
query.CommandText = String.Format("SELECT COUNT('{0}') FROM '{1}' WHERE '{0}' IS NOT NULL AND '{0}' <> '';", header, table);
validation = query.ExecuteScalar();

... there will be no exception, but then null/empty columns in other databases are not getting dropped because the ExecuteScalar() result is not correct.

EDIT: Table: raw_contacts | Header: sort_key
bei System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
bei System.Data.SQLite.SQLiteCommand.BuildNextCommand()
bei System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)
bei System.Data.SQLite.SQLiteDataReader.NextResult()
bei System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
bei System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
bei System.Data.SQLite.SQLiteCommand.ExecuteScalar(CommandBehavior behavior)
bei System.Data.SQLite.SQLiteCommand.ExecuteScalar()  

EDIT: header and table are strings which are gathered from a foreach-loop out of a string[].

So this seems just like a syntax fault to me. If you need more information or code, just tell me.
But what to do now? Who can help me?

Thank You!
Ben-X

share|improve this question
1  
What are the values of header and table when the exception is thrown? –  Dirk Feb 25 at 13:28
    
I added Console.WriteLine("Table: " + table + " | " + "Header: " + header); to my code and updated my post with the last line I got right before the exception. –  Ben-X Feb 25 at 13:53
    
Set a breakpoint at that place, examine the SQL when the exception occurs. Also examine the exception throws as it might give useful hints other than that stacktrace you posted. And finally you could use another tool to test the SQL statement that causes the exception to see if the statement is the problem or something with the C# code. –  Dirk Feb 25 at 15:03
    
Thanks, I tried to use a simple SELECT on that header in raw_contacts with sqlite-3.exe (SQLite ODBC Driver for Win64) and the prog says: Error: no such collation sequence: PHONEBOOK –  Ben-X Feb 25 at 16:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.