public void ParseFederalXLS(string a_filename)
{
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + a_filename + ";Extended Properties=\"Excel 8.0;HDR=YES;\"";
OleDbConnection xlsConnection = new OleDbConnection(connectionString);
xlsConnection.Open();
//OleDbCommand commandtest = new OleDbCommand("SELECT * FROM [2004 Election$]", connection);
OleDbDataAdapter _allData = new OleDbDataAdapter("SELECT * FROM [2004 Election Results$]", xlsConnection);
DataSet ds = new DataSet();
_allData.Fill(ds);
var table = ds.Tables[0];
int i = 1;
OleDbConnection _connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data/db.mdb"); //!testing
foreach (DataRow row in table.Rows)
{
try
{
_connection.Open();
string command = "UPDATE FederalElectorate SET TermsInPower=" + (2004 - int.Parse(row.ItemArray[4].ToString())) / 4 + ", TPP = "+ row.ItemArray[2] +" WHERE ElectorateName='"+ row.ItemArray[0] +"';";
OleDbCommand updateCommand = new OleDbCommand(command, _connection);
updateCommand.ExecuteNonQuery();
}
finally
{
_connection.Close();
}
i++;
}
}