Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

ASP read Excel range error

  • 18-06-2010 10:24AM
    #1
    Registered Users, Registered Users 2 Posts: 691 ✭✭✭


    Hi I am trying to do a simple asp page that reads from a excel file.

    At present I am getting the following error

    Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

    ODBC driver does not support the requested properties.

    /dream.asp, line 85

    <%
    ' Selected constants from adovbs.inc
    Const adOpenStatic = 3
    Const adLockPessimistic = 2
    
    Dim cnnExcel
    Dim rstExcel
    Dim I
    Dim iCols
    
    ' This is all standard ADO except for the connection string.
    ' You can also use a DSN instead, but so it'll run out of the
    ' box on your machine I'm using the string instead.
    Set cnnExcel = Server.CreateObject("ADODB.Connection")
    cnnExcel.Open "DBQ=" & Server.MapPath("DREAM.xls") & ";" & _
    	"DRIVER={Microsoft Excel Driver (*.xls)};"
    
    ' Same as any other data source.
    ' FYI: TestData is my named range in the Excel file
    Set rstExcel = Server.CreateObject("ADODB.Recordset")
    rstExcel.Open "SELECT * FROM Table;", cnnExcel, _
    	adOpenStatic, adLockPessimistic
    
    ' Get a count of the fields and subtract one since we start
    ' counting from 0.
    iCols = rstExcel.Fields.Count
    %>
    <table border="1">
    	<thead>
    		<%
    		' Show the names that are contained in the first row
    		' of the named range.  Make sure you include them in
    		' your range when you create it.
    		For I = 0 To iCols - 1
    			Response.Write "<th>"
    			Response.Write rstExcel.Fields.Item(I).Name
    			Response.Write "</th>" & vbCrLf
    		Next 'I
    		%>
    	</thead>
    	<%
    	rstExcel.MoveFirst
    
    	' Loop through the data rows showing data in an HTML table.
    	Do While Not rstExcel.EOF
    		Response.Write "<tr>" & vbCrLf
    		For I = 0 To iCols - 1
    			Response.Write "<td>"
    			Response.Write rstExcel.Fields.Item(I).Value
    			Response.Write "</td>" & vbCrLf
    		Next 'I
    		Response.Write "</tr>" & vbCrLf
    
    		rstExcel.MoveNext
    	Loop
    	%>
    </table>
    
    <%
    rstExcel.Close
    Set rstExcel = Nothing
    
    cnnExcel.Close
    Set cnnExcel = Nothing
    %>
    

    is my code

    my excel file is called DREAM.xls in root The sheet is called League and the Range is called Table.

    Any ideas?

    1Love


Advertisement