Link to home
Start Free TrialLog in
Avatar of Mezillinu
MezillinuFlag for Malta

asked on

No error message available, result code:

I am importing an axcel sheet with 2 columns and it is giving me this error, could any body please help me out with this issue please?

this code is under a button which triggers this event when finding an excel sheet from a computer with a normal file finder control.

Exception Type: System.Data.OleDb.OleDbException
Exception Message: No error message available, result code:
E_FAIL(0x80004005).
Exception Source: System.Data
Exception Target Site: .ctor

what do i need to change?

thanks for the help!
Dim dt As New DataTable
                dt.TableName = "Imported File"
                Dim da As System.Data.OleDb.OleDbDataAdapter
                Dim strConnstr, strImportfolder, strFilename As String
                Dim myDataset As New DataSet
                strImportfolder = MapPath("files")
                strFilename = "tbl_affiliate_demo.xls"
                'this is the file to be imported  
                Dim dttableNamestr As New DataTable
                Dim tablename As String = ""
                strConnstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + MapPath("files/fpostedfile.xls") + ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
                milestone = 1
                Conn = New OleDbConnection(strConnstr)
                Conn.Open()
                milestone = 2
                dttableNamestr = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "TABLE"})
                milestone = 3
                tablename = dttableNamestr.Rows(0).Item(2)
                da = New OleDbDataAdapter("select * from [" + tablename + "] ", Conn)
                da.Fill(dt)
 
                If checkColumns(dt) = True Then
                    milestone = 4
                    Conn.Close()
                    importedidsgrid.Visible = True
                    importedidsgrid.DataSource = dt
                    importedidsgrid.DataBind()
                    milestone = 5
                    SaveTrans(dt)
                                      
 
                    Me.lblStatus.Text = "Import Completed Successfully"
                Else
                    Me.lblStatus.Text = "Incorrect Headers! Must be : Affiliate ID | Account ID"
                    Me.lblStatus.ForeColor = Color.Red
                    Exit Sub
                End If

Open in new window

Avatar of samtran0331
samtran0331
Flag of United States of America image

Have you tried debugging to see which line breaks the code?
Might also want to use Try/Catch blocks to see if you can catch any exceptions.
Avatar of Bruce_1975
Bruce_1975

Also here is a link that describes making the call to GetOleDbSchemaTable:
http://www.devnewsgroups.net/group/microsoft.public.dotnet.framework.adonet/topic56566.aspx

According to the article, you should change that line of code to:
dttableNamestr = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, Nothing})

Open in new window

Avatar of Mezillinu

ASKER

the problem is that this sometimes breaks, and sometimes not and it does not make sense :s

I have done some research and it appears that this can happen
1)when the excel sheet you are trying to import is blocked for access (opened by a user)
2)connection string not specified correctly

but these are not my causes for sure...

any ideas?
I see you don't close the connection in the Else statement. Maybe it is an issue with the file being in use.
nah, i did not copy that part of code, but i do an if statament like if the current connections state is open , then close it afterwards

so that should not be an issue either... :s

any more ideas?

could the code snippet you showed me above be the reason? notice that it sometimes works and other times it gives an error that is what is wierd from it
dttableNamestr = Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, Nothing})

Open in new window

I wish I knew more about this, I have only used Office Interop to interact with Excel. I see a lot questions about oledb connections to excel - which may be a clue it is problematic...
Here a couple last shots in the dark:
Are you calling ReleaseObjectPool? http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbconnection.releaseobjectpool(VS.71).aspx

Do you handle the InfoMessage event to see what the Provider is doing when it doesn't work?
http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbconnection.infomessage(VS.71).aspx
ASKER CERTIFIED SOLUTION
Avatar of samtran0331
samtran0331
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi Samtran - I am sure that the column names that I have in the excel sheet to extract are not programming words.

For the excel sheet to be imported to my datatable, the columns have to be with specific headers, so if the user decides to input different headers - I will not let this.

But the columns are for sure not programming keywords
The problem may be security problem.
If the application is hosted in another machine or server, then it's not allowing opening an excel file from a remote client.
Try putting the excel file in the bin folder of the application then upload it...