[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Write to Excel using ADO from ASP

Posted on 2004-10-28
6
Medium Priority
?
360 Views
Last Modified: 2011-04-14
Hi All

I am trying to export data from my asp page using ADO to an Excel worksheet. This is what i have so far

<% @LANGUAGE="VBSCRIPT" %>

<%

Dim sql
Dim Rs

set conn=Server.CreateObject("ADODB.Connection")

conn.open = "Driver={Sybase System 11};Srvr=DEV-DATABASE;DB=AAAA_0500;UID=username;PWD=PASSWORD"

If Conn.State = 0 Then

response.write "Connection Failed"

End If

conn.cursorlocation = 3


sql = "select Study_site_no,Study_pat_id,visit from report"

   Set Rs = Server.CreateObject("ADODB.Recordset")

      Rs.CursorType = adOpenDynamic
      Rs.CursorLocation = 3
      Rs.Open sql,Conn,3,1


Response.ContentType = "application/vnd.ms-excel"

Response.AddHeader "Content-Disposition", "attachment; filename=Enquiry_List.xls"

if Rs.eof <> true then

response.write "<table border=1>"

while not Rs.eof

response.write "<tr><td>" & Rs.fields("ID") & "</td><td>" & 

Rs.fields("Study_site") & "</td><td>" & Rs.fields("Study_Pat") & "</td><td>" & Rs.fields("Visit") & 

"</td></tr>"
Rs.movenext
wend
response.write "</table>"
end if
set rs=nothing
Conn.close


I am getting a compilation error at response.write "<tr><td>" & Rs.fields("ID") & "</td><td>" &
-----------------------------------------------------------^

Any help is highly appreciated..

If this code is wrong and if there are some good examples it would be great.

Thanks
Ram
0
Comment
Question by:sramkris
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 1

Expert Comment

by:s_moken
ID: 12442044
You can't end a line with '&'

if this line is supposed to continue onto the next line then put an '_' on the end, and get rid of the blank lines.

e.g.
response.write "<tr><td>" & Rs.fields("ID") & "</td><td>" & _
Rs.fields("Study_site") & "</td><td>" & Rs.fields("Study_Pat") & "</td><td>" & Rs.fields("Visit") & _
"</td></tr>"


all that text is now effectively on the same line
0
 
LVL 1

Author Comment

by:sramkris
ID: 12448725
Hi

I changed my code to what you had but i get an erroe expected end of statement

response.write "<tr><td>" & Rs.fields("Study_Site") & "</td><td>" Rs.fields("Study_Pat") "</td></tr>"

Not sure what is wrong here

Thanks
0
 
LVL 1

Accepted Solution

by:
s_moken earned 200 total points
ID: 12451012

mmm not sure how you got that line

anyway if you look at the line in your first post which is fine;

Rs.fields("Study_site") & "</td><td>" & Rs.fields("Study_Pat") & "</td><td>" & Rs.fields("Visit") & 

and this line you just posted
 "<tr><td>" & Rs.fields("Study_Site") & "</td><td>" Rs.fields("Study_Pat") "</td></tr>"

where are the '&' symbols between on either side of the  Rs.fields("Study_Pat") ???
"<tr><td>" & Rs.fields("Study_Site") & "</td><td>" & Rs.fields("Study_Pat") & "</td></tr>"
-------------------------------------------------------------|----------------------------|
                                                                            ^                                  ^

Its quite OK for all this to be on the same line, like so;

response.write "<tr><td>" & Rs.fields("ID") & "</td><td>" & Rs.fields("Study_site") & "</td><td>" & Rs.fields("Study_Pat") & "</td><td>" & Rs.fields("Visit") & "</td></tr>"

but it appeared that you had separated them out into three lines, thats fine just so long as the continuation is on the immediate next line and a '_' (underscore) is used at the end, to indicate  continue on to the next line

As shown below this is all taken as one line
response.write "<tr><td>" & Rs.fields("ID") & "</td><td>" & Rs.fields("Study_site") _
& "</td><td>" & Rs.fields("Study_Pat") & "</td><td>" & Rs.fields("Visit") & "</td></tr>"

remove the '_' from the end of the first line and it will be taken as two separate lines.



0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:sramkris
ID: 12466382
Hi smoken

I will accpet this as the answer but i just wanted to ask one more thing. If its possible can you tell me how do i get the column name to print before i print the data.
in the excel sheet.

Thanks a lot
Ram



0
 
LVL 1

Expert Comment

by:s_moken
ID: 12469788
Well anything that goes into your html table will be exported to the excel spreedsheet, so you just have to put the Column Names as the first row in your table e.g.
 
response.write "<tr><td>Column1</td><td>Column2</td><td>Column3</td></tr>......etc"
if you use <th> then the headers will be Bold and Centered in Excel.
See line 2 below on how it would appear in your code.
simple as that

response.write "<table border=1>"
response.write "<tr><th>Column1</th><th>Column2</th><th>Column3</th></tr>"
while not Rs.eof
response.write "<tr><td>" & Rs.fields(0) & "</td><td>" & _
Rs.fields(1) & "</td><td>" & Rs.fields(2) & "</td><td>" & Rs.fields(3) & _
"</td></tr>"
Rs.movenext
wend

0
 
LVL 1

Author Comment

by:sramkris
ID: 12486395
Hi s_moken

I have posted another question an extension of the same problem could you please look in to that and help me out.

Thanks
Ram
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question