Export dynamic query / SP to Excel via browser...

Hi there.

I have some views on my  SQL server that I show on my web application in a simple table, and I have the feature to click an Excel icon to open the output in Excel.
I do also have some dynamic queries/Stored Procedures where I don't know the number of columns. In the views above I know the columns and wrote a code like this:

<%@ Language=VBScript %>
<!--#include virtual="/includes/conxxxxxxasp" -->
<% 
dim Conn,RS1
Set Conn = Server.CreateObject("ADODB.Connection")
Set RS1 = Server.CreateObject("ADODB.RecordSet")
strConn = MM_conmcd_STRING
Conn.Open strConn 
sql1 = "SELECT var1, var2, var3, var4, var5, var6 FROM tbl_xxxxx ORDER BY var1 ASC"
RS1.Open sql1, Conn 
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment; filename=xxxx_export.xls" 
%>
<table border="1" width="100%">
<tr>
<td bgcolor="#000080"><font color="#FFFFFF">var1</font></td>
<td bgcolor="#000080"><font color="#FFFFFF">var2</font></td>
<td bgcolor="#000080"><font color="#FFFFFF">var3</font></td>
<td bgcolor="#000080"><font color="#FFFFFF">var4</font></td>
<td bgcolor="#000080"><font color="#FFFFFF">var5</font></td>
<td bgcolor="#000080"><font color="#FFFFFF">var6</font></td>
</tr>
<%Do While Not RS1.eof%>
<tr>
<td><%=RS1("var1")%></td>
<td><%=RS1("var2")%></td>
<td><%=RS1("var3")%></td>
<td><%=RS1("var4")%></td>
<td><%=RS1("var5")%></td>
<td><%=RS1("var6")%></td>
</tr>
<%
RS1.Movenext
Loop
%>
</table>
<%
RS1.Close
Conn.Close
Set Conn = Nothing
Set RS1 = Nothing
%>

Open in new window


How should I do it with an unknown number of columns? I use classic asp/VB.

Best regards

Ulrich
ullenulleAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
try look for ADODB Recordset's fields.count property for the looping:

The Fields Collection
https://docs.microsoft.com/en-us/sql/ado/guide/data/the-fields-collection

or refer to the fields collection directly:

Extract Data from the Recordset
https://www.w3schools.com/asp/ado_recordset.asp
ullenulleAuthor Commented:
Ok... say that I write the first part of the page like this:

<%@ Language=VBScript %>
<!--#include virtual="/includes/conxxxxxxasp" -->
<% 
dim Conn,RS1
Set Conn = Server.CreateObject("ADODB.Connection")
Set RS1 = Server.CreateObject("ADODB.RecordSet")
strConn = MM_conmcd_STRING
Conn.Open strConn 
sql1 = "Select * from tbl_xxxxx ORDER BY var1 ASC"
RS1.Open sql1, Conn 
for each x in rs.fields
  response.write(x.name)
  response.write(" = ")
  response.write(x.value)
next
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment; filename=xxxx_export.xls" 
%>
<table border="1" width="100%">
<tr>
<td bgcolor="#000080"><font color="#FFFFFF">first column</font></td>
<td bgcolor="#000080"><font color="#FFFFFF">second column</font></td>
.... ???
.... ???
</tr>
<%Do While Not RS1.eof%>
<tr>
<td><%=RS1("var1")%></td>
<td><%=RS1("var2")%></td>
.... ???
.... ???
</tr>
<%
RS1.Movenext
Loop
%>
</table>
<%
RS1.Close
Conn.Close
Set Conn = Nothing
Set RS1 = Nothing
%>

Open in new window


How do I insert the unknown column names in the table?
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
something like this should worked:

<%@ Language=VBScript %>
<!--#include virtual="/includes/conxxxxxxasp" -->
<% 
dim Conn,RS1
Set Conn = Server.CreateObject("ADODB.Connection")
Set RS1 = Server.CreateObject("ADODB.RecordSet")
strConn = MM_conmcd_STRING
Conn.Open strConn 
sql1 = "Select * from tbl_xxxxx ORDER BY var1 ASC"
RS1.Open sql1, Conn 

Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment; filename=xxxx_export.xls" 
%>
<table border="1" width="100%">
<tr>
<%
for each x in rs1.fields
%>
<td bgcolor="#000080"><font color="#FFFFFF"><%=x.name%></font></td>
<%
next
 %>

</tr>
<%Do While Not RS1.eof%>
<tr>
<%
for each x in rs1.fields
%>
  <td><%=x.value%></td>
<%
next
 %>

</tr>
<%
RS1.Movenext
Loop
%>
</table>
<%
RS1.Close
Conn.Close
Set Conn = Nothing
Set RS1 = Nothing
%>

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
any further assistance is needed here?
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
as proposed
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.