C# program throwing exception when adding data to sqlite database

Question

So I have this program that I am working on for my university. The program takes data from a webpage (in this case Reddit), and adds it into a SQlite server. Everything seems to be running smoothly except the part where it inserts the data into a table. Here is the code:

    class Program
{

    static string connString = @"Data Source=C:\SQLite\mydatabase;Version=3;";
    static SQLiteConnection conn = new SQLiteConnection(connString);
    public SQLiteDataAdapter da = new SQLiteDataAdapter();
    DataTable dt = new DataTable();



    static void Main(string[] args)
    {
        FirefoxDriver driver = new FirefoxDriver();
        driver.Navigate().GoToUrl("http://www.reddit.com/");;
        Console.WriteLine("collecting data");
        String date = DateTime.Now.ToString("M/d/yyyy");
        String title1 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[1]/div[2]/p[1]/a").Text;
        String title2 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[3]/div[2]/p[1]/a").Text;
        String title3 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[5]/div[2]/p[1]/a").Text;
        String title4 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[7]/div[2]/p[1]/a").Text;
        String title5 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[9]/div[2]/p[1]/a").Text;
        String title6 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[11]/div[2]/p[1]/a").Text;
        String title7 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[13]/div[2]/p[1]/a").Text;
        String title8 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[15]/div[2]/p[1]/a").Text;
        String title9 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[17]/div[2]/p[1]/a").Text;
        String title10 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[19]/div[2]/p[1]/a").Text;

        String user1 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[1]/div[2]/p[2]/a[1]").Text;
        String user2 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[3]/div[2]/p[2]/a[1]").Text;
        String user3 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[5]/div[2]/p[2]/a[1]").Text;
        String user4 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[7]/div[2]/p[2]/a[1]").Text;
        String user5 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[9]/div[2]/p[2]/a[1]").Text;
        String user6 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[11]/div[2]/p[2]/a[1]").Text;
        String user7 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[13]/div[2]/p[2]/a[1]").Text;
        String user8 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[15]/div[2]/p[2]/a[1]").Text;
        String user9 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[17]/div[2]/p[2]/a[1]").Text;
        String user10 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[19]/div[2]/p[2]/a[1]").Text;

        String subreddit1 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[1]/div[2]/p[2]/a[2]").Text;
        String subreddit2 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[3]/div[2]/p[2]/a[2]").Text;
        String subreddit3 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[5]/div[2]/p[2]/a[2]").Text;
        String subreddit4 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[7]/div[2]/p[2]/a[2]").Text;
        String subreddit5 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[9]/div[2]/p[2]/a[2]").Text;
        String subreddit6 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[11]/div[2]/p[2]/a[2]").Text;
        String subreddit7 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[13]/div[2]/p[2]/a[2]").Text;
        String subreddit8 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[15]/div[2]/p[2]/a[2]").Text;
        String subreddit9 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[17]/div[2]/p[2]/a[2]").Text;
        String subreddit10 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[19]/div[2]/p[2]/a[2]").Text;

        String comments1 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[1]/div[2]/ul/li[1]/a").Text;
        String comments2 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[3]/div[2]/ul/li[1]/a").Text;
        String comments3 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[5]/div[2]/ul/li[1]/a").Text;
        String comments4 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[7]/div[2]/ul/li[1]/a").Text;
        String comments5 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[9]/div[2]/ul/li[1]/a").Text;
        String comments6 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[11]/div[2]/ul/li[1]/a").Text;
        String comments7 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[13]/div[2]/ul/li[1]/a").Text;
        String comments8 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[15]/div[2]/ul/li[1]/a").Text;
        String comments9 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[17]/div[2]/ul/li[1]/a").Text;
        String comments10 = driver.FindElementByXPath("//*[@id=\"siteTable\"]/div[19]/div[2]/ul/li[1]/a").Text;


        Console.WriteLine("Data Collected. Writing to flat file");
        //driver.Close();

        //SQLiteCommand RedditPageTable = new SQLiteCommand("create table RedditPageData(ID INT, Date varchar(25), title varchar(300), user varchar(50), subreddit varchar(50), comments varchar(50));", conn);
        //conn.Open();
        //RedditPageTable.ExecuteNonQuery();
        //conn.Close();


        using (StreamWriter writer = new StreamWriter("inputstream1.txt", true) ){
            writer.WriteLine(title1 + ";" + user1 + ";" + subreddit1 + ";" + comments1 + ";" + title2 + ";" + user2 + ";" + subreddit2 + ";" + comments2 + ";" + 
                title3 + ";" + user3 + ";" + subreddit3 + ";" + comments3 + ";" + title4 + ";" + user4 + ";" + subreddit4 + ";" + comments4 + ";" + title5 + ";" + 
                user5 + ";" + subreddit5 + ";" + comments5 + ";" + title6 + ";" + user6 + ";" + subreddit6 + ";" + comments6 + ";" + user7 + ";" + title7 + ";" + 
                subreddit7 + ";" + comments7 + ";" + title8 + ";" + user8 + ";" + subreddit8 + ";" + comments8 + ";" + title9 + ";" + user9 + ";" + subreddit9 + ";" + 
                comments9 + ";" + title10 + ";" + user10 + ";" + subreddit10 + ";" + comments10);               
        }

        StringBuilder sb1 = new StringBuilder();
        using (StreamReader sr1 = new StreamReader("inputstream.txt"))
        {
            String line;
            // Read and display lines from the file until the end of 
            // the file is reached.
            while ((line = sr1.ReadLine()) != null)
            {
                sb1.AppendLine(line);
            }
        }
        string allines1 = sb1.ToString();
        int counter1 = 0;
        int ID = 0;
        string[] dataSplit1 = allines1.Split(';');
        while (true)
        {                              
            counter1 += 1;
            String titleEnter = dataSplit1[counter1];
            counter1 += 1;
            String userEnter = dataSplit1[counter1];
            counter1 += 1;
            String subredditEnter = dataSplit1[counter1];
            counter1 += 1;
            String commentsEnter = dataSplit1[counter1];
            ID++;
            Console.WriteLine(counter1);

            SQLiteCommand InsertRedditInfo = new SQLiteCommand("INSERT INTO RedditPageData(ID, Date, title, user, subreddit, comments) VALUES('"+ ID + "','" + date + "','" + titleEnter + "','" + userEnter + "','" + subredditEnter + "','" + commentsEnter + "')", conn);
            conn.Open();
            InsertRedditInfo.ExecuteNonQuery();
            conn.Close();    

            if (counter1 == 39)
            {
                break;
            }
        }



        Console.WriteLine("Data written successfully");
    }


}

And here are the details of the exception:

System.Data.SQLite.SQLiteException was unhandled
  HResult=-2147467259
  Message=SQL logic error or missing database
near "s": syntax error
  Source=System.Data.SQLite
  ErrorCode=1
  StackTrace:
       at System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
       at System.Data.SQLite.SQLiteCommand.BuildNextCommand()
       at System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)
       at System.Data.SQLite.SQLiteDataReader.NextResult()
       at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
       at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior)
       at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
       at ConsoleApplication5.Program.Main(String[] args) in c:\Users\Chambers\Documents\Visual Studio 2012\Projects\ConsoleApplication5\ConsoleApplication5\Program.cs:line 125
       at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:

It says there could be a syntax error or a missing database, but I'm sure that the database is there, and I can't seem to find the syntax error. Any pair of fresh eyes would help!

Here is the list of string that is written to the flat file to be added to the Sqlite server.

I am a pilot that was on the first American C-17 aid flight into Nepal. This is what we brought.;Mr_Sacapuntas;/r/pics;1508 comments;Sony OFFICIALLY developing 21 Jump Street Men In Black Crossover;erythrasma;/r/movies;625 comments;King of Sandy Shores (xpost /r/GrandTheftAutoV_PC);TaintedSquirrel;/r/gaming;173 comments;The perfect comeback;TuxK;/r/funny;653 comments;Hula Hooping for Men;Turbo_Sandwich;/r/gifs;209 comments;Golf like announcer at crowdless MLB game;_tx;/r/videos;645 comments;monolithburger;a 60 second meditation tool to help clear your mind;/r/InternetIsBeautiful;1105 comments;TIL that Martin Luther King Jr's assassin, James Earl Ray was once erroneously honoured during a parade which intended to praise the actor James Earl Jones. Lauderdale, Florida accidentally created a plaque in January 2002 which read 'Thanks James Earl Ray for keeping the dream alive'.;silsilaa;/r/todayilearned;656 comments;Jon Stewart has purchased a farm in New Jersey with the intention of spending his "retirement" to provide a sanctuary for farm animals rescued from cruelty (x-post from r/DailyShow);EnviroHawk;/r/television;1220 comments;Four month old baby pulled alive from Nepal rubble 22 hours after parents lost him during earthquake;theflamingskull;/r/worldnews;390 comments

You need to show us the final sql statement that is being compiled. — Ehsan
– Ehsan, Commented Apr 30, 2015 at 2:25

Community · Accepted Answer · 2017-05-23 11:58:28Z

2

Given the lack of query parameters or sanitation on the data being inserted, the most likely culprit is a bad string being formed when you inject your values. See the post below to see a sample of how to use parameters with SqlLiteCommand.

How to add parameters to a Sqllitecommand

We would, as Ehsan mentioned, need to see the final string to be sure this is the real culprit, though.

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered Apr 30, 2015 at 2:31

Travis

6995 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zain Rizvi · Accepted Answer · 2015-04-30 02:32:31Z

0

Try simplifying the problem. Instead of inserting 10 users, titles, subreddits, and comments, just do one. Better yet, just try to insert a single user and nothing else. If that works just do a single title, and so on and so forth, until you hit this error again.

Then you'll have a lot less code to look at and inspect to find your bug

answered Apr 30, 2015 at 2:32

Zain Rizvi

24.8k25 gold badges100 silver badges138 bronze badges

7 Comments

Ron Beyer Over a year ago

The SQL statement isn't the problem, its the unfiltered data being string concatenated into it.

Zain Rizvi Over a year ago

@RonBeyer You might be right. In that case a better approach for him to take may be to insert the data one row at a time, and log output of what data he's inserting. When the insert fails then it's time to inspect that particular row and share it with the rest of Stack Overflow (I'm trying to make the problem easier to debug)

Chambers Volk Over a year ago

I was thinking that might be the issue. Part of the assignment is that the data has to be imported from a flat file. Any ideas on how to clean it up?

Chambers Volk Over a year ago

also, The strings seem to be okay, the code makes it through the part where it parses through the file for strings, but fails on the first time it tries to add the first set of strings (titles, users, subreddits, comments)

Chambers Volk Over a year ago

Development! it makes it through three sets of strings. So I think you're right in saying that it hits a bad string in that mix. I'll post the list of strings that it's going through

|

Chambers Volk · Accepted Answer · 2015-04-30 04:27:45Z

0

Figured it out! one of the strings contained an ', and that was throwing the error. Thanks to @Ron Bayer and @Travis that pushed me in the right direction with this.

answered Apr 30, 2015 at 4:27

Chambers Volk

619 bronze badges

Collectives™ on Stack Overflow

C# program throwing exception when adding data to sqlite database

3 Answers 3

Comments

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related