Hi,
I have a program that checks a folder for Excel files, imports their contents via SQL to a database table, then moves the Excel file to a 'Completed' folder.
This worked flawlessly last year. Unfortunatley, I am now getting the following error:
The data uploads just fine. It is just moving the file afterwards that is the problem.
Here is the relevant code:
Let me know if you need any more information.
Thanks so much.
I have a program that checks a folder for Excel files, imports their contents via SQL to a database table, then moves the Excel file to a 'Completed' folder.
This worked flawlessly last year. Unfortunatley, I am now getting the following error:
System.IO.IOException: The process cannot access the file because it is being used by another process.
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.__Error.WinIOError()
at System.IO.File.InternalMove(String sourceFileName, String destFileName, Boolean checkHost)
at System.IO.File.Move(String sourceFileName, String destFileName)
at UploadAttendanceLogtoSQLTaible.Program.InsertExcelRecords(String filePath, String destinationPath) in C:\Users\**Redacted**\Google Drive\Teaching\Software\UploadAttendanceLog\UploadAttendanceLogtoSQLTable\Program.cs:line 159
The data uploads just fine. It is just moving the file afterwards that is the problem.
Here is the relevant code:
C#:
private static void InsertExcelRecords(string filePath, string destinationPath)
{
LogWriter Log = new LogWriter("");
try
{
// ExcelConn(_path);
string constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", filePath);
OleDbConnection Econ = new OleDbConnection(constr);
string Query = string.Format("Select [Student ID],[Activity],[Session Name],[Date],[Time],[Status],[Sub-Session Name] FROM [{0}]", arg0: "data_RawAttendanceLog$");
OleDbCommand Ecom = new OleDbCommand(Query, Econ);
Econ.Open();
//Create one dataset and fill this data set with this selected items, using oledbdataadpter
DataSet ds = new DataSet();
OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ);
Econ.Close();
oda.Fill(ds);
DataTable Exceldt = ds.Tables[0];
for (int i = Exceldt.Rows.Count - 1; i >= 0; i--)
{
if (Exceldt.Rows[i]["Student ID"] == DBNull.Value)
{
Exceldt.Rows[i].Delete();
}
}
Exceldt.AcceptChanges();
//creating object of SqlBulkCopy
string csDestination = "server = " + GetValueFromFile("server") + "; database = " + GetValueFromFile("database") + "; Trusted_Connection=True"; // User ID = *redacted*; Password = ; Trusted_Connection=True";
using (SqlConnection con = new SqlConnection(csDestination))
{
SqlBulkCopy objbulk = new SqlBulkCopy(con);
//assigning Destination table name
objbulk.DestinationTableName = GetValueFromFile("DestinationTableName");
//Mapping Table column
objbulk.ColumnMappings.Add("[Student ID]", "StudentID");
objbulk.ColumnMappings.Add("[Activity]", "Activity");
objbulk.ColumnMappings.Add("[Session Name]", "SessionName");
objbulk.ColumnMappings.Add("[Date]", "SessionDate");
objbulk.ColumnMappings.Add("[Time]", "SessionTime");
objbulk.ColumnMappings.Add("[Status]", "SessionStatus");
objbulk.ColumnMappings.Add("[Sub-Session Name]", "SubSessionName");
//inserting Datatable Records to DataBase
SqlConnection sqlConnection = new SqlConnection();
con.Open();
objbulk.WriteToServer(Exceldt);
Console.WriteLine("Upload successful.");
Log.LogWrite("Upload successful.");
Econ.Close();
Log.LogWrite("Moving " + filePath + " to " + destinationPath);
File.Move(filePath, destinationPath);
}
}
catch (Exception ex)
{
if (ex.Message.Contains("network-related"))
{
Console.WriteLine("Cancelled. No Network access.");
return;
}
else
{
Console.WriteLine(string.Format("Upload has not been imported due to: {0}", ex.Message));
Log.LogWrite(string.Format("Upload has not been imported due to: {0}", ex.Message));
Console.WriteLine("Press any key to close...");
ShowWindow(GetConsoleWindow(), SW_SHOW);
Console.ReadKey();
}
}
}
}
Let me know if you need any more information.
Thanks so much.