I have a bit of a really weird issue going on. When I upload an excel file, either XLS('97-'03) or XLSX('07-'10) to my test machine and to two test servers, the import works great, it uploads and processes.
When I upload the same file to a different server, the file uploads, but I get a "Cannot find table 0" error. Here's some Visual Basic code:
..................................
EXCEL '07-'10
..................................
Dim strConn As String = _
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + uploadFile + ";Extended Properties=""Excel 12.0 Xml;" + _
"HDR=" + IIf(chkHeader.Checked = True, "YES", "NO") + ";IMEX=1"""
da = New OleDb.OleDbDataAdapter("SELECT * FROM [" + sheetName + "$]", strConn)
da.Fill(ds)
..................................
OR EXCEL '97-'03
..................................
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + uploadFile + _
";Extended Properties=""Excel 8.0;HDR=" + IIf(chkHeader.Checked = True, "YES", "NO") + ";IMEX=1;ImportMixedTypes=Text"""
da = New OleDb.OleDbDataAdapter("SELECT * FROM [" + sheetName + "$]", strConn)
da.Fill(ds)
..................................
BOTH CASES COME TO THIS NEXT BLOCK
..................................
XLSView.DataSource = Nothing
ds.Tables(0).Columns.Add(New DataColumn("Message")) ' <----- PRETTY SURE IT CRASHES HERE
XLSView.AutoGenerateColumns = True
XLSView.DataSource = ds.Tables(0).DefaultView()
XLSView.DataBind()
XLSView.Visible = True
Is there some setting I need to set somewhere or is there something else I should try? I appreciate any help you may have.
Thank you for your reply. I guess I should have mentioned that I did indeed enclose the "crashing block" in a try/catch block. It is an IndexOutOfRangeException... but I don't know of other information besides that. All I know is that on that one server,
zero tables are somehow being extracted, but on the others, the correct one table is being extracted.
Sorry it took a couple days to respond; I couldn't get to the remote machine until now.
I placed a try block around "ds.tables(0)" etc. and if that fails, it tried "ds.tables(1)" (and placed that under a try block so if it fails, it's a "soft crash"). Unfortunately, that didn't work either. I now have a "cannot find table 1" error, which
is still an IndexOutOfRangeException.
Any more ideas you may have is appreciated. Thanks again.
I actually do wonder if the OleDbDataAdapter is actually drawing the table correctly. The funny thing is that this program loads TXT files with the greatest of ease, even on the trouble-making server, using the same JET 4.0 driver as regular XLS files,
which fail in an IndexOutOfRangeException.
The directories have security = "Everybody has full control"... OK, that's not the best way to handle things, but that's another discussion for another time.
I know I installed the ACE driver, otherwise, there should have been an error previously. Hmmm... what other things could cause trouble here?
1. ACE.OLEDB.12.0 can load both XLS and XLSX, so I would comment out the references to the old JET driver as a test.
I would uninstall both drivers, reboot server, and get the latest ACE driver from microsoft installed fresh.
2. as a test, try hardcoding the SHEET name to eliminate that.
I had some "funny" things happen to my Oracle Database Control program recently, I tried all kinds of things standard to fixing the control repository, etc. No luck. And then, I just happened to be reading about a Microsoft update affecting RSA tokens..
and hmmm... recent update applied and I am having certificate problems with Oracle. Uninstalled the update, and all the tail chasing errors resolved.
3. Re-read abou the IMEX and other settings to be sure they are not the cause. I have had trouble with these.
4. ACE OLEDB is driving me nuts right now treating Excel TEXT numeric as a real NUMBER and truncating leading ZEROs... GRRRR.
Make sure sheet name without space. Give simple name and try.
lobstercaster
I have a bit of a really weird issue going on. When I upload an excel file, either XLS('97-'03) or XLSX('07-'10) to my test machine and to two test servers, the import works great, it uploads and processes.
When I upload the same file to a different server, the file uploads, but I get a "Cannot find table 0" error. Here's some Visual Basic code:
..................................
EXCEL '07-'10
..................................
Dim strConn As String = _
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + uploadFile + ";Extended Properties=""Excel 12.0 Xml;" + _
"HDR=" + IIf(chkHeader.Checked = True, "YES", "NO") + ";IMEX=1"""
da = New OleDb.OleDbDataAdapter("SELECT * FROM [" + sheetName + "$]", strConn)
da.Fill(ds)
..................................
OR EXCEL '97-'03
..................................
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + uploadFile + _
";Extended Properties=""Excel 8.0;HDR=" + IIf(chkHeader.Checked = True, "YES", "NO") + ";IMEX=1;ImportMixedTypes=Text"""
da = New OleDb.OleDbDataAdapter("SELECT * FROM [" + sheetName + "$]", strConn)
da.Fill(ds)
..................................
BOTH CASES COME TO THIS NEXT BLOCK
..................................
XLSView.DataSource = Nothing
ds.Tables(0).Columns.Add(New DataColumn("Message")) ' <----- PRETTY SURE IT CRASHES HERE
XLSView.AutoGenerateColumns = True
XLSView.DataSource = ds.Tables(0).DefaultView()
XLSView.DataBind()
XLSView.Visible = True
Is there some setting I need to set somewhere or is there something else I should try? I appreciate any help you may have.
Thanks,
Pete
exceptionexcel
Encourage me by Mark as "ANSWER" if above code really help full. Don't forgot to "Report Abuse" in case violates Terms and Conditions
lobstercaste...
0 Points
4 Posts
Excel 1997-2010 / "Cannot find table 0"
Nov 06, 2012 04:18 PM|LINK
I have a bit of a really weird issue going on. When I upload an excel file, either XLS('97-'03) or XLSX('07-'10) to my test machine and to two test servers, the import works great, it uploads and processes.
When I upload the same file to a different server, the file uploads, but I get a "Cannot find table 0" error. Here's some Visual Basic code:
.................................. EXCEL '07-'10 .................................. Dim strConn As String = _ "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + uploadFile + ";Extended Properties=""Excel 12.0 Xml;" + _ "HDR=" + IIf(chkHeader.Checked = True, "YES", "NO") + ";IMEX=1""" da = New OleDb.OleDbDataAdapter("SELECT * FROM [" + sheetName + "$]", strConn) da.Fill(ds) .................................. OR EXCEL '97-'03 .................................. strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + uploadFile + _ ";Extended Properties=""Excel 8.0;HDR=" + IIf(chkHeader.Checked = True, "YES", "NO") + ";IMEX=1;ImportMixedTypes=Text""" da = New OleDb.OleDbDataAdapter("SELECT * FROM [" + sheetName + "$]", strConn) da.Fill(ds) .................................. BOTH CASES COME TO THIS NEXT BLOCK .................................. XLSView.DataSource = Nothing ds.Tables(0).Columns.Add(New DataColumn("Message")) ' <----- PRETTY SURE IT CRASHES HERE XLSView.AutoGenerateColumns = True XLSView.DataSource = ds.Tables(0).DefaultView() XLSView.DataBind() XLSView.Visible = TrueIs there some setting I need to set somewhere or is there something else I should try? I appreciate any help you may have.
Thanks,
Pete
exception excel
Lannie
Contributor
3738 Points
728 Posts
Re: Excel 1997-2010 / "Cannot find table 0"
Nov 06, 2012 06:25 PM|LINK
Enclose in
Try
Catch ex as Exception
End Try
to attempt to capture exception error information in greater detail
lobstercaste...
0 Points
4 Posts
Re: Excel 1997-2010 / "Cannot find table 0"
Nov 06, 2012 06:57 PM|LINK
Lannie,
Thank you for your reply. I guess I should have mentioned that I did indeed enclose the "crashing block" in a try/catch block. It is an IndexOutOfRangeException... but I don't know of other information besides that. All I know is that on that one server, zero tables are somehow being extracted, but on the others, the correct one table is being extracted.
Thanks,
Pete
Lannie
Contributor
3738 Points
728 Posts
Re: Excel 1997-2010 / "Cannot find table 0"
Nov 07, 2012 12:52 AM|LINK
ds.Tables(1).Columns.Add(New DataColumn("Message"))
what does TABLES(1) return if the problem index out of range??
and if that works
I bet you can set the index ranging base programmatically
lobstercaste...
0 Points
4 Posts
Re: Excel 1997-2010 / "Cannot find table 0"
Nov 09, 2012 02:31 PM|LINK
Lannie,
Sorry it took a couple days to respond; I couldn't get to the remote machine until now.
I placed a try block around "ds.tables(0)" etc. and if that fails, it tried "ds.tables(1)" (and placed that under a try block so if it fails, it's a "soft crash"). Unfortunately, that didn't work either. I now have a "cannot find table 1" error, which is still an IndexOutOfRangeException.
Any more ideas you may have is appreciated. Thanks again.
Lannie
Contributor
3738 Points
728 Posts
Re: Excel 1997-2010 / "Cannot find table 0"
Nov 09, 2012 07:47 PM|LINK
I am sure you have been programming long enough to know that sometimes the returned error
is merely a symptom of the problem and not the cause of the problem.
Especially when we are all sitting around scratching our heads... What the COO-COO.. #*$^@^$(
IndexOutOfRangeException may not really the problem, just a downstream end result.
Are we sure the offending server has all the permissions, drivers, MS Office components, OLEDB components, etc. that it needs?
lobstercaste...
0 Points
4 Posts
Re: Excel 1997-2010 / "Cannot find table 0"
Nov 09, 2012 08:26 PM|LINK
Too true, Lannie... too true...
I actually do wonder if the OleDbDataAdapter is actually drawing the table correctly. The funny thing is that this program loads TXT files with the greatest of ease, even on the trouble-making server, using the same JET 4.0 driver as regular XLS files, which fail in an IndexOutOfRangeException.
The directories have security = "Everybody has full control"... OK, that's not the best way to handle things, but that's another discussion for another time.
I know I installed the ACE driver, otherwise, there should have been an error previously. Hmmm... what other things could cause trouble here?
Lannie
Contributor
3738 Points
728 Posts
Re: Excel 1997-2010 / "Cannot find table 0"
Nov 10, 2012 02:49 AM|LINK
1. ACE.OLEDB.12.0 can load both XLS and XLSX, so I would comment out the references to the old JET driver as a test.
I would uninstall both drivers, reboot server, and get the latest ACE driver from microsoft installed fresh.
2. as a test, try hardcoding the SHEET name to eliminate that.
I had some "funny" things happen to my Oracle Database Control program recently, I tried all kinds of things standard to fixing the control repository, etc. No luck. And then, I just happened to be reading about a Microsoft update affecting RSA tokens.. and hmmm... recent update applied and I am having certificate problems with Oracle. Uninstalled the update, and all the tail chasing errors resolved.
3. Re-read abou the IMEX and other settings to be sure they are not the cause. I have had trouble with these.
4. ACE OLEDB is driving me nuts right now treating Excel TEXT numeric as a real NUMBER and truncating leading ZEROs... GRRRR.
1Madhu
Member
317 Points
119 Posts
Re: Excel 1997-2010 / "Cannot find table 0"
Nov 12, 2012 11:46 AM|LINK
Hi,
Make sure sheet name without space. Give simple name and try.
exception excel
Regards,
Madhu,India