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