App Hanging after populating Excel from .NET using oleDb
I have a method that works fine on most machines, except my boss's. We're both on Win7, and both running Excel 2010 (exact same version in fact).
His executes fully, no errors, then about 3seconds later the application stops responding.
This is written in VS2010 on my machine, he has VS2005 installed, but never used (I tried to open this in debug but he's missing SP1). Only oddity I've noticed.
Here's some abridged code, if it triggers anything I should be looking for. This code is called from a button click event.
Only thing I can think is I'm closing/disposing incorrectly?
His executes fully, no errors, then about 3seconds later the application stops responding.
This is written in VS2010 on my machine, he has VS2005 installed, but never used (I tried to open this in debug but he's missing SP1). Only oddity I've noticed.
Here's some abridged code, if it triggers anything I should be looking for. This code is called from a button click event.
Only thing I can think is I'm closing/disposing incorrectly?
Public Shared Sub Format(ByVal FileName As String)
Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""" & FileName & """;Extended Properties=""Excel 12.0;HDR=YES;MAXSCANROWS=15;READONLY=FALSE"""
Dim conObj As New OleDbConnection(connString)
conObj.Open()
Dim readQuery = "SELECT * FROM [Sheet1$]"
Dim conCommand As New OleDbCommand("", conObj)
conCommand.CommandText = readQuery
Dim conReader As OleDbDataReader = conCommand.ExecuteReader()
While (conReader.Read())
...
End While
conReader.Close()
Dim createDimCommand As String = "CREATE TABLE [CatalogInfo] (" & _
"[Arrester Rating (kV, rms)] decimal(5,0), " & _
"[Catalog Number] char(255)," & _
"[Figure 5 Dim. ""A""(" & distUnit.ToString & ")] decimal(10,1), " & _
"[Figure 4 Dim. ""B"" Minimum Phase-To-Ground Clearance (" & distUnit.ToString & ")] decimal(10,1), " & _
"[Figure 4 Dim. ""C"" Minimum Phase-to-Phase Cearance (" & distUnit.ToString & ")] decimal(10,1), " & _
"[Creepage Distance (" & distUnit.ToString & ")] decimal(10,1), [1.2/50 Impulse (kV, crest)] decimal(10,1), " & _
"[60Hz, dry 60 seconds (kV, rms)] decimal(10,1)," & _
"[60Hz, wet 10 seconds (kV, rms)] decimal(10,1), [Weight(" & weightUnit.ToString & ")] decimal(10,1))"
If (tables.Contains("CatalogInfo")) Then
conCommand.CommandText = "DROP TABLE [CatalogInfo]"
conCommand.ExecuteNonQuery()
End If
conCommand.CommandText = createDimCommand
conCommand.ExecuteNonQuery()
conObj.Close()
conCommand.Dispose()
If MsgBox("Sucecssfully generated catalog sheet." & vbCrLf & "Do you want to view it?", MsgBoxStyle.YesNo) = vbYes Then
Process.Start(FileName)
End If
end Sub
You can address problem as follows:
1) Environment check:
Make sure your boss PC has the same Database Engine 2010 driver that you have.
You can downloaded from:
http://www.microsoft.com/en-us/download/details.aspx?id=13255
Also you need to check if you have same .NET framework and same Office version (including updates).
2) Code missing property info at connString
I notice that you connection string does not set DataSource property (shown as empty in your code), this property must be set to the spreadsheet location as shown in this link:
http://www.connectionstrings.com/excel-2007#ace-oledb-12-0
Question:
Is the FileName the path to your xlsx file?
Note: I assume that you are saving to the same location in both PCs thus your filename should be correct., Right? Other wise use System.IO.File.Exists method to check at the start of your method.
1) Environment check:
Make sure your boss PC has the same Database Engine 2010 driver that you have.
You can downloaded from:
http://www.microsoft.com/en-us/download/details.aspx?id=13255
Also you need to check if you have same .NET framework and same Office version (including updates).
2) Code missing property info at connString
I notice that you connection string does not set DataSource property (shown as empty in your code), this property must be set to the spreadsheet location as shown in this link:
http://www.connectionstrings.com/excel-2007#ace-oledb-12-0
Question:
Is the FileName the path to your xlsx file?
Note: I assume that you are saving to the same location in both PCs thus your filename should be correct., Right? Other wise use System.IO.File.Exists method to check at the start of your method.
ASKER
@Andy, Yes, I do. And Excel opens and he can view the data.
@Mas, yes the filename is the full path to the file. Its generated from a file brower in the calling function. The file path works, as the Excel file does open, with correct info.
There are no errors, the call stack completes, then hangs.
Today it happened on my computer, it just took a lot longer, maybe 5minutes. I clicked debug and the code wasn't running, nothing broke, so theres no infinite loop or process waiting.
I also noticed that after closing the workbook, and running this again on the same book, it stopped updating the book, like this method of populating a book does more than just puts values into the cells. I deleted some values, saved and closed the book to confirm the code was making changes, I got an error to the effect that "Data had been deleted". Like this method leaves query data behind or something.
@Mas, yes the filename is the full path to the file. Its generated from a file brower in the calling function. The file path works, as the Excel file does open, with correct info.
There are no errors, the call stack completes, then hangs.
Today it happened on my computer, it just took a lot longer, maybe 5minutes. I clicked debug and the code wasn't running, nothing broke, so theres no infinite loop or process waiting.
I also noticed that after closing the workbook, and running this again on the same book, it stopped updating the book, like this method of populating a book does more than just puts values into the cells. I deleted some values, saved and closed the book to confirm the code was making changes, I got an error to the effect that "Data had been deleted". Like this method leaves query data behind or something.
>>@Andy, Yes, I do.
OK. So that check was at the end of the function and everything seems to work OK. (Maybe the problem is elsewhere in the app).
Now try to comment out the Process.Start(FileName) line and run again. I know that excel won't be started BUT if the program still hangs then we know this is a red herring you have been chasing here.
OK. So that check was at the end of the function and everything seems to work OK. (Maybe the problem is elsewhere in the app).
Now try to comment out the Process.Start(FileName) line and run again. I know that excel won't be started BUT if the program still hangs then we know this is a red herring you have been chasing here.
ASKER
If I click no on msgbox, and skip opening the file, it still hangs.
The calling code to this function isn't the issue, it's a simple button click and browse file then opens this and thats it. I use it often.
I'm fairly sure now it has something to do with populating excel with this method.
The calling code to this function isn't the issue, it's a simple button click and browse file then opens this and thats it. I use it often.
I'm fairly sure now it has something to do with populating excel with this method.
ASKER
@Mas, your #2 suggestion isn't correct. Datasource isn't empty, you have to scroll right. It matches the XLSX Conn String shown in your link.
Ok, the problem is on line 31, this line contains "DROP TABLE" SQL statement that is not supported by the Excel Ole Db driver. (this driver does not suport any delete operation in general)
Alternative: Use Excel interop to delete the table, check:
http://stackoverflow.com/questions/6053732/oledb-for-excel-drop-table-sheetname-doent-delete-sheet
Note: Your program only works when CatalogInfo sheet does not exist.
Alternative: Use Excel interop to delete the table, check:
http://stackoverflow.com/questions/6053732/oledb-for-excel-drop-table-sheetname-doent-delete-sheet
Note: Your program only works when CatalogInfo sheet does not exist.
ASKER
The link you sent says DROP TABLE won't delete the sheet, it just clears it. That's all it needs to do (in case the new data has less rows than a previous execution). It does give me a place to look though, so I'll see if it gets more stable with alternatives.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
>>I have a method that works fine on most machines, except my boss's.
Is this DROP TABLE the real problem? If it only fails on one machine I would suspect it isn't.
Anyway - to test start with an excel that would not require the DROP TABLE command, comment that section out then run. Does it still hang ?
Is this DROP TABLE the real problem? If it only fails on one machine I would suspect it isn't.
Anyway - to test start with an excel that would not require the DROP TABLE command, comment that section out then run. Does it still hang ?
ASKER
@mas, you're adding more caveats than is needed in this case. All the program is doing is dumping a query result to a sheet. So all is needed is to clear the sheet and dump data, no formatting, no changing headers.
It's extremely intermittent. I've run it over and over on mine and its not hanging anymore. Meanwhile my boss's totally crashed and wont run at all. I've given him the link to update his Oledb driver, but I think he may need an admin to come install. If so, that won't be until Thursday.
My next choice is to drop this oledb method all together and rewrite it using automation. The whole reason its not using automation is oledb doesn't require office components to run the application when not using this utility (very few users do). I could use late binding, but I really hate debugging that method.
It's extremely intermittent. I've run it over and over on mine and its not hanging anymore. Meanwhile my boss's totally crashed and wont run at all. I've given him the link to update his Oledb driver, but I think he may need an admin to come install. If so, that won't be until Thursday.
My next choice is to drop this oledb method all together and rewrite it using automation. The whole reason its not using automation is oledb doesn't require office components to run the application when not using this utility (very few users do). I could use late binding, but I really hate debugging that method.
there is another choice: Does it need to be an xlsx file at all, you can generate a csv file? (AS there is no need for formatting, etc.).
A csv file can be opened in Excel without any issues and read/generate a csv file is very easy using File.ReadAllLines and File.WriteAllLines, check:
http://www.dotnetperls.com/file
Note: If CSV file does not work for you then use NPOI as it is Office independent code alternative .
A csv file can be opened in Excel without any issues and read/generate a csv file is very easy using File.ReadAllLines and File.WriteAllLines, check:
http://www.dotnetperls.com/file
Note: If CSV file does not work for you then use NPOI as it is Office independent code alternative .
ASKER
There's more than 1 sheet involved. I've just cut out all but 1 in this example.
Have you tried my previous suggestion? (If it does turn out to be DROP TABLE command after all could you delete the excel file from disc and create a new one / copy a blank one ?)
ASKER
Waiting on my boss to get back from a trip. Hopefully, tomorrow, but I'm sure hes busy since hes been gone all week. I've got ideas to test now, though.
ASKER
I couldn't figure out the issue, and decided to stop wasting time and went with full Automation, if someone doesn't have Excel installed, I'll switch it to late binding.
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for MrBullwinkle's comment #a38962139
for the following reason:
The answer explains it.
Accepted answer: 0 points for MrBullwinkle's comment #a38962139
for the following reason:
The answer explains it.
My post 38901228 provides the answer to your question. For obvious reasons code could not be provided as I was not sure at the time whether it was an extra environmental issue or not.
My educated guess is that it works in your PC by coincidence (depending if the sheet exist) and when it has go to production (e.g. your boss PC) it fails becaue he may have a workbook with the sheet to update, thus the error.
In my post I gave you the choice of NPOI to avoid having Excel dependency.
My educated guess is that it works in your PC by coincidence (depending if the sheet exist) and when it has go to production (e.g. your boss PC) it fails becaue he may have a workbook with the sheet to update, thus the error.
In my post I gave you the choice of NPOI to avoid having Excel dependency.
ASKER
Sorry, I initially skipped that answer as it was defeating the purpose of my OP, but when I gave up on that, it became the answer.
MsgBox("Process_Start: " + FileName)
Process.Start(FileName)
MsgBox("After Process_Start")
End If
Do you see both messages on the problem PC, or just the first ?
Is the FileName containing a 'correct' path ?