[SOLVED] Spreadsheet connection in vb web app

Spreadsheet connection in vb web app

My client wants their price list on their website. I'm trying to use the spreadsheet (.xlsx) as a data source so as to limit processing of the data before it appears on the website.

The site is running on IIS 7. The server is Windows Server 2008. I've updated the .NET Framework on it to 4.0 as well.

It doesn't have any Office packages installed on it, and I have a hunch thats where my problem lies, but I'd really like to be sure.

Heres the error text I'm getting:

System.InvalidOperationException: The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine. at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper) at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.OleDb.OleDbConnection.Open() at Products.Page_Load(Object sender, EventArgs e) in C:\HostingSpaces\webbuddies\waterinc.webbuddies.co.za\wwwroot\Products.aspx.vb:line 14

Heres my code:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
' Here's the connection string as defined in web.config:
' <connectionStrings>
'     <add name="xlsx" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=App_Data/Pricelist.xlsx;Extended Properties=Excel 8.0;" />
' </connectionStrings>
    Dim con As New OleDbConnection(ConfigurationManager.ConnectionStrings("xlsx").ConnectionString)

    ' The spreadsheet has 4 sheets in it, 1 for each category on the client's pricelist.
    Try
        con.Open()

        Dim dsWater As DataSet
        Dim daWater As New OleDbDataAdapter
        daWater.SelectCommand = New OleDbCommand("SELECT * FROM Water", con)

        Dim dsJuice As DataSet
        Dim daJuice As New OleDbDataAdapter
        daJuice.SelectCommand = New OleDbCommand("SELECT * FROM FruitJiuce", con)

        Dim dsMix As DataSet
        Dim daMix As New OleDbDataAdapter
        daMix.SelectCommand = New OleDbCommand("SELECT * FROM MuffinMix", con)

        Dim dsMisc As DataSet
        Dim daMisc As New OleDbDataAdapter
        daMisc.SelectCommand = New OleDbCommand("SELECT * FROM Miscellaneous", con)

        daWater.Fill(dsWater, "Water")
        daJuice.Fill(dsJuice, "FruitJuice")
        daMix.Fill(dsMix, "MuffinMix")
        daMisc.Fill(dsMisc, "Miscellaneous")

        rptWater.DataSource = dsWater : rptWater.DataBind()
        rptJuices.DataSource = dsJuice : rptJuices.DataBind()
        rptMixes.DataSource = dsMix : rptMixes.DataBind()
        rptMisc.DataSource = dsMisc : rptJuices.DataBind()

        con.Close()
    Catch ex As Exception
        errorMessage = ex.ToString
        lblResponse.Text = "Could not connect to one or more data sources required to display the " & _
            "pricelist. Please contact the webmaster." & vbCrLf & errorMessage
        lblResponse.ForeColor = Drawing.Color.Red
    End Try
End Sub

EDIT 1
Since posting I have installed the 2007 office driver on the server from the link in the 1st answer, and I have modified my connection string as per the 2nd answer:

<connectionStrings>
    <add name="xlsx" connectionString="Provider=Microsoft.ACE.OLEDB.12.0; Data Source=App_Data/Pricelist.xlsx; Extended Properties=Excel 12.0 Xml; HDR=YES;"/>
</connectionStrings>

Solution