I'm trying to use the OleDb CSV parser to load some data from a CSV file and insert it into a SQLite database, but I get an exception with the OleDbAdapter.Fill method and it's frustrating:
An unhandled exception of type
'System.Data.ConstraintException'
occurred in System.Data.dll
Additional information: Failed to
enable constraints. One or more rows
contain values violating non-null,
unique, or foreign-key constraints.
Here is the source code:
public void InsertData(String csvFileName, String tableName)
{
String dir = Path.GetDirectoryName(csvFileName);
String name = Path.GetFileName(csvFileName);
using (OleDbConnection conn =
new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
dir + @";Extended Properties=""Text;HDR=No;FMT=Delimited"""))
{
conn.Open();
using (OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM " + name, conn))
{
QuoteDataSet ds = new QuoteDataSet();
adapter.Fill(ds, tableName); // <-- Exception here
InsertData(ds, tableName); // <-- Inserts the data into the my SQLite db
}
}
}
class Program
{
static void Main(string[] args)
{
SQLiteDatabase target = new SQLiteDatabase();
string csvFileName = "D:\\Innovations\\Finch\\dev\\DataFeed\\YahooTagsInfo.csv";
string tableName = "Tags";
target.InsertData(csvFileName, tableName);
Console.ReadKey();
}
}
The "YahooTagsInfo.csv" file looks like this:
tagId,tagName,description,colName,dataType,realTime
1,s,Symbol,symbol,VARCHAR,FALSE
2,c8,After Hours Change,afterhours,DOUBLE,TRUE
3,g3,Annualized Gain,annualizedGain,DOUBLE,FALSE
4,a,Ask,ask,DOUBLE,FALSE
5,a5,Ask Size,askSize,DOUBLE,FALSE
6,a2,Average Daily Volume,avgDailyVolume,DOUBLE,FALSE
7,b,Bid,bid,DOUBLE,FALSE
8,b6,Bid Size,bidSize,DOUBLE,FALSE
9,b4,Book Value,bookValue,DOUBLE,FALSE
I've tried the following:
Removing the first line in the CSV file so it doesn't confuse it for real data.
Changing the TRUE/FALSE realTime flag to 1/0.
I've tried 1 and 2 together (i.e. removed the first line and changed the flag).
None of these things helped...
One constraint is that the tagId is supposed to be unique. Here is what the table look like in design view:
Can anybody help me figure out what is the problem here?