Logic Error inserting values into sqlite from csv

scott oliver

Hello all. I am sure this has been asked before and I hope its an easy answer. I have some code that inserts information from a csv into sqlite. The csv has 9 colums but the table has 10 becuase I added a datetime column that needs to be added. I found some snips that seem to work, but I am not sure how to insert the date with it. I am trying not to have to break the csv into an array or something over complicated. below is the code and this is the web address of the instruction I found .

http://www.techbrothersit.com/2016/04/c-how-to-import-or-load-text-file-to.html

while ((line = SourceFile.ReadLine()) != null)
{
//skip the header row
if (counter > 0)
{
//prepare insert query
string query = "Insert into " + TableName +
" Values ('" + line.Replace(filedelimiter, "','") + "')";
//execute sqlcommand to insert record
SQLiteCommand myCommand = new SQLiteCommand(query, sqlConn);
myCommand.ExecuteNonQuery();
}
counter++;
}

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh5y ago
Accepted answer
var restSharpClient = new RestClient("https://www.otcmarkets.com");

var downloadRequest = new RestRequest("research/stock-screener/api/downloadCSV?pageSize=20",
               Method.GET);

IRestResponse response = restSharpClient.Execute(downloadRequest);
//Console.WriteLine(response.Content);

string[] strSeparators = new string[] { "\r\n" };
string[] Lines = response.Content.Split(strSeparators, StringSplitOptions.None);

string[] Fields;
foreach(var line in Lines.Skip(1))
{
Fields = line.Split(new char[] { ',' });

    string query = "Insert Into dbo.regist (Symbol, Security_Name, Tier, Price, Change, Vol,Sec_Type,Country,State,Date) " +
    "VALUES (@F1, @F2,@F3, @F4,@F5, @F6,@F7, @F8,@F9, @F10) ";

    using (SQLiteConnection con=new SQLiteConnection(cs))
{
SQLiteCommand cmd = new SQLiteCommand(query, con);
        // add parameters and their values
cmd.Parameters.AddWithValue("@F1", Fields[0]);
cmd.Parameters.AddWithValue("@F2", Fields[1]);
cmd.Parameters.AddWithValue("@F3", Fields[2]);
cmd.Parameters.AddWithValue("@F4", Fields[3]);
cmd.Parameters.AddWithValue("@F5", Fields[4]);
cmd.Parameters.AddWithValue("@F6", Fields[5]);
cmd.Parameters.AddWithValue("@F7", Fields[6]);
cmd.Parameters.AddWithValue("@F8", Fields[7]);
cmd.Parameters.AddWithValue("@F9", Fields[8]);
cmd.Parameters.AddWithValue("@F110", System.DateTime.Now);

        // open connection, execute command and close connection
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
}
}
Note :- if Parameters.AddWithValue causes problem as it always does with numeric types then use
cmd.Parameters.Add("@F1", DbType.TypeOfValue).Value = Fields[0]
scott oliver5y ago
Ok I got it. I had to move the date to the top. Its the first column in the db. I have not check the db to see if the data is in there properly but I am no longer getting errors. Thanks again.
scott oliver5y ago
Thank you sir I appreciate the help. I am getting an error though. On execute I am getting "{"unknown error\r\nInsufficient parameters supplied to the command"}" Not sure what this means. I know its a sql error but beyond that