mercredi 29 avril 2015

C# program throwing exception when adding data to sqlite database

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!

Aucun commentaire:

Enregistrer un commentaire