Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > .NET > Other .NET > ADO.NET
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
ADO.NET For discussion about ADO.NET.  Topics such as question regarding the System.Data namespace are appropriate.  Questions specific to a particular application should be posted in a forum specific to the application .
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ADO.NET section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old January 17th, 2005, 03:39 AM
Authorized User
 
Join Date: Aug 2003
Location: HCM, HCM, Vietnam.
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default Get data from Excel file using ADO.NET

THIS IS MY CODE

Private Sub GetDataFromXLS()
        Dim strConn As String
        Dim objConn As OleDbConnection
        Dim sql As String

        strConn = _
              "Provider=Microsoft.Jet.OLEDB.4.0; " & _
              "Data Source=C:\Data.xls;" & _
              "Extended Properties=Excel 8.0;HDR=Yes;"

        objConn = New OleDbConnection(strConn)
        objConn.Open()

        sql = "SELECT ProductCode, ProductName FROM [Data$]"
        '-Data is name of sheet in Data.xls.

        Dim objCommand As New OleDbCommand(sql, objConn)

        Dim objAdapter As New OleDbDataAdapter(objCommand)

        Dim objDS As New DataSet

        objAdapter.Fill(objDS)

        With Me.gridProduct
            .DataSource = objDS.Tables(0).DefaultView
            .DataBind()
        End With

        objConn.Close()
    End Sub

I use Excel 2003.

I don't understand where's wrong in above, but it showed error following:


Line 50: strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data.xls;Extended Properties=Excel 8.0;HDR=Yes;"
Line 51: objConn = New OleDbConnection(strConn)
Line 52: objConn.Open()
Line 53:
Line 54: sql = "SELECT ProductCode, ProductName FROM [Data$]"

[OleDbException (0x80004005): Unspecified error]
   System.Data.OleDb.OleDbConnectionInternal.Initiali zeProvider() +135
   System.Data.OleDb.OleDbConnectionInternal..ctor(Ol eDbConnection conn, OleDbConnectionString constr) +84
   System.Data.OleDb.OleDbConnectionFactory.CreateCon nection(DbConnectionOptions options, Object providerInfo, DbConnectionBase owningObject) +55
   System.Data.ProviderBase.DbConnectionFactory.Creat eNonPooledConnection(DbConnectionBase owningConnection, DbConnectionPoolGroup poolGroup) +26
   System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnectionBase owningConnection) +51
   System.Data.ProviderBase.DbConnectionClosed.Open(D bConnectionBase outerConnection) +189
   System.Data.ProviderBase.DbConnectionBase.Open() +62
   GUIVP.ImportData.btnView_Click(Object sender, EventArgs e) in E:\Inetpub\wwwroot\GUIVP\Admin\ImportData.aspx.vb: 52
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
   System.Web.UI.WebControls.Button.RaisePostBackEven t(String eventArgument) +232
   System.Web.UI.WebControls.Button.System.Web.UI.IPo stBackEventHandler.RaisePostBackEvent(String eventArgument) +5
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler sourceControl, String eventArgument) +11
   System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection postData) +31
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5157


Please help me in this situation.
Thank you alot.

L.V.L
__________________
L.V.L
Reply With Quote
  #2 (permalink)  
Old February 8th, 2005, 09:14 PM
Registered User
 
Join Date: Feb 2005
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by lam_lvl
 THIS IS MY CODE

Private Sub GetDataFromXLS()
        Dim strConn As String
        Dim objConn As OleDbConnection
        Dim sql As String

        strConn = _
              "Provider=Microsoft.Jet.OLEDB.4.0; " & _
              "Data Source=C:\Data.xls;" & _
              "Extended Properties=Excel 8.0;HDR=Yes;"

        objConn = New OleDbConnection(strConn)
        objConn.Open()

        sql = "SELECT ProductCode, ProductName FROM [Data$]"
        '-Data is name of sheet in Data.xls.

        Dim objCommand As New OleDbCommand(sql, objConn)

        Dim objAdapter As New OleDbDataAdapter(objCommand)

        Dim objDS As New DataSet

        objAdapter.Fill(objDS)

        With Me.gridProduct
            .DataSource = objDS.Tables(0).DefaultView
            .DataBind()
        End With

        objConn.Close()
    End Sub

I use Excel 2003.

I don't understand where's wrong in above, but it showed error following:


Line 50:  strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data.xls;Extended Properties=Excel 8.0;HDR=Yes;"
Line 51:  objConn = New OleDbConnection(strConn)
Line 52:  objConn.Open()
Line 53:
Line 54:  sql = "SELECT ProductCode, ProductName FROM [Data$]"

[OleDbException (0x80004005): Unspecified error]
   <B style="color:black;background-color:#ffff66">System.Data.OleDb.OleDbConnectionIn ternal.InitializeProvider</B>() +<B style="color:black;background-color:#A0FFFF">135</B>
   System.Data.OleDb.OleDbConnectionInternal..ctor (OleDbConnection conn, OleDbConnectionString constr) +84
   System.Data.OleDb.OleDbConnectionFactory.Create Connection(DbConnectionOptions options, Object providerInfo, DbConnectionBase owningObject) +55
   System.Data.ProviderBase.DbConnectionFactory.Cr eateNonPooledConnection(DbConnectionBase owningConnection, DbConnectionPoolGroup poolGroup) +26
   System.Data.ProviderBase.DbConnectionFactory.Ge tConnection(DbConnectionBase owningConnection) +51
   System.Data.ProviderBase.DbConnectionClosed.Ope n(DbConnectionBase outerConnection) +189
   System.Data.ProviderBase.DbConnectionBase.Open( ) +62
   GUIVP.ImportData.btnView_Click(Object sender, EventArgs e) in E:\Inetpub\wwwroot\GUIVP\Admin\ImportData.aspx.vb: 52
   System.Web.UI.WebControls.Button.OnClick(EventA rgs e) +111
   System.Web.UI.WebControls.Button.RaisePostBackE vent(String eventArgument) +232
   System.Web.UI.WebControls.Button.System.Web.UI. IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +5
   System.Web.UI.Page.RaisePostBackEvent(IPostBack EventHandler sourceControl, String eventArgument) +11
   System.Web.UI.Page.RaisePostBackEvent(NameValue Collection postData) +31
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5157


Please help me in this situation.
Thank you alot.

L.V.L
Reply With Quote
  #3 (permalink)  
Old February 8th, 2005, 09:16 PM
Registered User
 
Join Date: Feb 2005
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi I am also getting the same problem. If you/any one got the solution please let me know.
Thanks,
Sanjay..

Reply With Quote
  #4 (permalink)  
Old February 9th, 2005, 05:04 PM
Registered User
 
Join Date: Feb 2005
Location: Québec, QC, Canada.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to gcere
Default

Hi,

Your connection string is not correct.

Try this one..

       strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Classeur1.xls;Extended Properties=""Excel 8.0;HDR=Yes;"""

Bye.

Geoffrey Céré
Epsilon Technologies
Reply With Quote
  #5 (permalink)  
Old February 10th, 2005, 07:14 PM
Registered User
 
Join Date: Feb 2005
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi
I am trying to extract data from Excel file & insert into SQL data table. My program works when I run it through VS.NET but fails when I use IIS server. Please help me..

Exception Details: System.Data.OleDb.OleDbException: Unspecified error

Source Error:


Line 34: myCommand.Fill(myDataSet, "ExcelInfo");
Line 35: DataGrid1.DataSource = myDataSet.Tables["ExcelInfo"].DefaultView;
Line 36: DataGrid1.DataBind();


Source File: c:\Telemarketing\UploadExcel.aspx Line: 34

Stack Trace:


[OleDbException (0x80004005): Unspecified error]
   System.Data.OleDb.OleDbConnectionInternal.Initiali zeProvider() +135
   System.Data.OleDb.OleDbConnectionInternal..ctor(Ol eDbConnection conn, OleDbConnectionString constr) +84
   System.Data.OleDb.OleDbConnectionFactory.CreateCon nection(DbConnectionOptions options, Object providerInfo, DbConnectionBase owningObject) +55
   System.Data.ProviderBase.DbConnectionFactory.Creat eNonPooledConnection(DbConnectionBase owningConnection, DbConnectionPoolGroup poolGroup) +26
   System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnectionBase owningConnection) +51
   System.Data.ProviderBase.DbConnectionClosed.Open(D bConnectionBase outerConnection) +189
   System.Data.ProviderBase.DbConnectionBase.Open() +62
   System.Data.Common.DbDataAdapter.FillInternal(Data Set dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +130
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +144
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +86
   ASP.UploadExcel_aspx.uploadme(String ss) in c:\Telemarketing\UploadExcel.aspx:34
   ASP.UploadExcel_aspx.Button1_Click(Object Source, EventArgs e) in c:\Telemarketing\UploadExcel.aspx:120
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
   System.Web.UI.WebControls.Button.RaisePostBackEven t(String eventArgument) +232
   System.Web.UI.WebControls.Button.System.Web.UI.IPo stBackEventHandler.RaisePostBackEvent(String eventArgument) +5
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler sourceControl, String eventArgument) +11
   System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection postData) +31
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint



My Original CODE:

    string strConn;
    // ss is the excel file name with the path
    string filename=ss ;
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
    "Data Source=" + filename + ";Extended Properties=Excel 8.0;HDR=Yes;";
    //You must use the $ after the object you reference in the spreadsheet
    OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM
                                 [list$]", strConn);
    DataSet myDataSet = new DataSet();
    myCommand.Fill(myDataSet, "ExcelInfo");
    DataGrid1.DataSource = myDataSet.Tables["ExcelInfo"].DefaultView;
    DataGrid1.DataBind();

Reply With Quote
  #6 (permalink)  
Old February 3rd, 2006, 07:58 PM
Authorized User
 
Join Date: Jun 2005
Location: , , .
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I used gcere connection string and still get the error anyone got a fix yet?

Reply With Quote
  #7 (permalink)  
Old March 18th, 2008, 01:32 PM
Registered User
 
Join Date: Nov 2006
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This is the solution:
http://www.themssforum.com/Ado/Syste...n-Unspecified/

Reply With Quote
  #8 (permalink)  
Old February 27th, 2014, 05:19 AM
Registered User
Points: 21, Level: 1
Points: 21, Level: 1 Points: 21, Level: 1 Points: 21, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Feb 2014
Posts: 7
Thanks: 0
Thanked 0 Times in 0 Posts
Default RE: Get data from Excel file using ADO.NET

Quote:
Originally Posted by yzer View Post
First unfortunately this link does not work anymore.

Second I struggled with this issue quite some time and I wanted to share some info about it.
As you mentioned this issue occurs on IIS server and there are couple of things that can be the problem.
For example:
- Do you have a read permissions over the folder that the file is in?
- If you have an ASP.NET app than check your web.config file, does it define impersonate to true?
I read somewhere that because when .NET reads an excel file, it creates a temp folder to store the data, and that temp folder can only be access by the ASP.NET account. So if you impersonate as another user on the computer / network and tried to use ASP.NET to read the data in the excel file, you will not be able to. Read more here under the Set Impersonation section: http://support.microsoft.com/kb/827190.

Also you can try using an alternative approach, I would recommend you this VB.NET excel component. It’s fast and furious ;)
Here is how you can export Excel data to a DataTable in VB.NET:
Code:
' Load Excel file.
Dim workbook = ExcelFile.Load("Workbook.xls")

' Select active worksheet from the file.
Dim worksheet = workbook.Worksheets.ActiveWorksheet

' Extract the data from the worksheet to newly created DataTable starting at 
' first row and first column for 10 rows or until the first empty row appears.
Dim dataTable = worksheet.CreateDataTable(New CreateDataTableOptions() With
    {
        .StartRow = 0,
        .StartColumn = 0,
        .NumberOfRows = 10,
        .ExtractDataOptions = ExtractDataOptions.StopAtFirstEmptyRow
    })
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem getting Excel data with ADO 2.7 xoc Excel VBA 2 April 18th, 2007 10:37 AM
Using ADO to query data in Excel sheet Fails with maaron Excel VBA 4 June 9th, 2005 02:56 PM
Referencing a closed excel file using ADO. cunninb Excel VBA 0 January 10th, 2005 12:29 AM
Update Excel File Using ADO .Net briancronin123 ADO.NET 0 June 3rd, 2004 03:20 AM



All times are GMT -4. The time now is 02:19 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.