Custom C# script failing in workflow
Hi Everyone,
I'm trying to implement a custom C# Script at the bottom of our workflow that will write the resulting .XLS file into a db table in SQL.
I'm new to C# so it might be fairly obvious what i'm doing wrong but no matter how many times I test & rewrite the code I keep getting this error:
Aborted exception: 'Syntax error, '(' expected
) expected
Syntax error, '(' expected
) expected
'.
Below is the code i've written, I removed some info for security reasons but you get the idea of what it looks like.
If anybody has any ideas or insight it would be greatly appreciated!
I've tried different ideas, including the use of public void {} & removing the Name Spaces all together which then gives an eror that states it does not recognize the OleDbConnection, but the issue seems to stem from the Name Spaces at the top from what I can deduce.
//Name Spaces
using System.Data.OleDb;
using System.Data.SqlClient;
//Variables
string sSQLTable = "HED_EMPLOYEE_DATA";
string myExcelDataQuery = "Select Company,Contact,Email,Address,City,CentralCities,EnterpriseZones,NEZ,CDBG from [Sheet1$]";
//Connection Strings
string sExcelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
"Data Source=C:\\folder\\folder\\folder\\Report_Test10.XLS" +
"Extended Properties=" +
"Excel 12.0;HDR=YES;IMEX=1";
string sSqlConnectionString = "SERVER=ourServer;USER ID=ourID;PASSWORD=ourPW;DATABASE=ourDB;CONNECTION RESET=FALSE";
//Series of commands to bulk copy data from the excel file into our SQL table
OleDbConnection OleDbConn = new OleDbConnection(sExcelConnectionString);
OleDbCommand OleDbCmd = new OleDbCommand(myExcelDataQuery, OleDbConn);
OleDbConn.Open();
OleDbDataReader dr = OleDbCmd.ExecuteReader();
SqlBulkCopy bulkCopy = new SqlBulkCopy(sSqlConnectionString);
bulkCopy.DestinationTableName = sSQLTable;
while (dr.Read())
{
bulkCopy.WriteToServer(dr);
}
OleDbConn.Close();
Thanks!
-
Hi Robin,
The syntax error you are seeing is due to the presence of the two using statements. Unfortunately these aren't allowed within the script. You can remove them and then fully qualify the names inline. It is a bit more work, but I was able to get the following to compile.
//Variables string sSQLTable = "HED_EMPLOYEE_DATA"; string myExcelDataQuery = "Select Company,Contact,Email,Address,City,CentralCities,EnterpriseZones,NEZ,CDBG from [Sheet1$]"; //Connection Strings string sExcelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=C:\\folder\\folder\\folder\\Report_Test10.XLS" + "Extended Properties=" + "Excel 12.0;HDR=YES;IMEX=1"; string sSqlConnectionString = "SERVER=ourServer;USER ID=ourID;PASSWORD=ourPW;DATABASE=ourDB;CONNECTION RESET=FALSE"; //Series of commands to bulk copy data from the excel file into our SQL table <strong>var </strong>OleDbConn = new <strong>System.Data.OleDb.</strong>OleDbConnection(sExcelConnectionString); <strong>var </strong>OleDbCmd = new <strong>System.Data.OleDb.</strong>OleDbCommand(myExcelDataQuery, OleDbConn); OleDbConn.Open(); <strong>var </strong>dr = OleDbCmd.ExecuteReader(); <strong>var </strong>bulkCopy = new <strong>System.Data.SqlClient.</strong>SqlBulkCopy(sSqlConnectionString); bulkCopy.DestinationTableName = sSQLTable; while (dr.Read()) { bulkCopy.WriteToServer(dr); } OleDbConn.Close();--Ryan
0 -
That's awesome thank you!
We have sadly run into a new error:
"Exception has been thrown by the target of an invocation.
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine."
We are now trying to figure out how to get it to read the 32-bit driver on a 64-bit (2008 R2) machine.
We have Microsoft Office 2010 installed, and we un-installed & re-installed it with no luck.
We opened the ODBC Data Source Administrator and tried to delete the Microsfot Excel Drive but it throws the error
"The setup routines for the Microsoft Excel Driver ODBC driver could not be found. Please reinstall the driver."
Errors Found:
"The specified DSN contains an architecture mismatch between the Driver and Application"
Not too sure where to go from here, but i'm researching everything I possibly can.
0 -
Hi Robin
have you thought about using something along the lines of Aspose.Cells from aspose.com?
You could use that the read the spreadsheet and then use it to write to the SQL table without needing a 'client' application being installed onto the server.
Regards
Ralph
0 -
Ralph: Thanks for the suggestion, we already have the software necessary we just need to get it working.
All: Does anyone know how to get the Geocortex Workflow Designer to use the Microsoft Driver located in the (x86), aka 32-bit software, directory?
We know it's there but I think the problem is that it keeps looking for it in the wrong location.
I looked through all the forums I could find online about this issue, which many people seem to have, but nothing has worked so far.
Any suggestions would be awesome! Thanks!
0 -
Hi Robin,
On a 64-bit machine you can use the Geocortex.Workflow.Designer32 .exe executable to run Workflow Designer as a 32-bit application. This will allow it to load 32-bit libraries.
--Ryan
0 -
Hi Ryan,
I'm still unable to get this code to run on our server.
What kind of software setup do you have for it to run?
I'm trying to figure out what's going wrong & what I need to fix but have hit a wall, I'm still getting the error:
"Exception has been thrown by the target of an invocation.
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine."
So I tested it out on a 32-bit machine and it still gave the same error. I even changed it from ACE to Jet and got a "Could not find installable ISAM." error.
Any ideas?
I have Microsoft Office 2010 installed, it's Server 2008 R2 & 64-bit.
0
Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.
Kommentare
6 Kommentare