Importing Excel to screen with ASP

Zamees · March 29th, 2004, 05:07 PM

Having loads of troubles with this.

Heres the code I have now...

PHP Code:


			
Test

<%

'## Create Connection and Recordsets

[s][s][s][s]Dim Conn, RS[/s][/s][/s][/s]

[s][s][s][s]Set Conn = CreateObject("ADODB.Connection")[/s][/s][/s][/s]

[s][s][s][s]Set RS = CreateObject("ADODB.Recordset")[/s][/s][/s][/s]

'## The Data to extract

[s][s][s][s]szSQL = "select * from 'EM JULY-04'"[/s][/s][/s][/s]

'## Create and open the connection

[s][s][s] [/s][/s][/s]

[s][s][s][s]Conn.Open "Driver={Driver do Microsoft Excel(*.xls)};" & "DBQ=" & Server.MapPath("test.xls") & ";"[/s][/s][/s][/s]

'## set the cursor to be static.

[s][s][s][s]rs.cursortype = 3 ' adStatic.[/s][/s][/s][/s]



'## open the recordset

[s][s][s][s]rs.open szSQL, Conn[/s][/s][/s][/s]



'## Disconnect recordset, eliminate connection

[s][s][s][s]rs.activeconnection = nothing[/s][/s][/s][/s]

[s][s][s][s]Conn.Close[/s][/s][/s][/s]

[s][s][s][s]Set Conn=nothing[/s][/s][/s][/s]

'## iterate through the recordset.

'while not rs.eof

'  response.write RS("name") & " -- "

'  response.write RS("location")  & " -- "

'  response.write RS("Coordinator")  & " -- "

'  response.write RS("Date")  & " -- "

'  response.write RS("Time")  & "<BR>"

'  rs.movenext

'wend

RS.close

Set RS=nothing

%>

Page is just coming up as 500 internal server error. im not even able to see the "Test Hi" listed at the begining of the page. Anyone got any ideas of what needs to be changed or added. Just trying to do a simple read excel and display to screen. As i've never done this before im a bit clueless as to the correct connection string or whatever else i may need.

acdsky · March 30th, 2004, 04:46 AM

Hi

I created a blank sheet with and used your code, the only change I made was to close the connections after looping through the recordset and also the query (Code Below) Do you get any other error thats more specific about what the problem is e.g Line # or something?

<%
'## Create Connection and Recordsets
    Dim Conn, RS
    Set Conn = CreateObject("ADODB.Connection")
    Set RS = CreateObject("ADODB.Recordset")
'## The Data to extract
    szSQL = "select * from [Sheet1$]"
'## Create and open the connection

    Conn.Open "Driver={Driver do Microsoft Excel(*.xls)};" & "DBQ=" & Server.MapPath("Book2.xls") & ";"
'## set the cursor to be static.
    rs.cursortype = 3 ' adStatic.

'## open the recordset
    rs.open szSQL, Conn
'## iterate through the recordset.
while not rs.eof
  response.write RS("name")
  rs.movenext
wend
RS.close
Set RS=nothing
Conn.Close
Set Conn=nothing
%>