Expand|Select|Wrap|Line Numbers
- <%@ Language=VBScript %>
- <!-- #include file="../incfiles/adovbs.inc" -->
- <%
- 'Declare some variables
- dim Conn,RS1
- '==================CONNECTION CODE TO SQL SERVER=======================
- 'Create a connection object to the database
- Set Conn = Server.CreateObject("ADODB.Connection")
- 'Create a recordset object
- Set RS1 = Server.CreateObject("ADODB.RecordSet")
- 'Feed the connection string into a variable called strConn
- strConn = "Provider=SQLOLEDB;Data Source=MYSERVERNAME;Initial Catalog=MYDATABASENAME;"&_
- "User Id=USERNAME;Password=PASSWORD;"
- 'Feed the connection string into the connection object
- Conn.Open strConn
- '==========================CONNECTION CODE=============================
- 'Create your sql statement
- sql1 = "Select * from tblMYTABLE WHERE ID < 10000"
- 'Obtain the data using all the work done above
- RS1.Open sql1, Conn
- 'Test to see if we have a recordset coming back from the database and trap the error if there is no data
- If RS1.eof Then
- %>
- <html>
- <body>
- <table border="0" width="100%" cellspacing="0" cellpadding="2">
- <tr>
- <td> <font face="Arial" color="#FF0000" size="2">No Records Match Your Search</font></td>
- </tr>
- </table>
- <%
- 'If there is data carry on
- Else
- %>
- <!--
- We will need to fire the data we gather to the next page which produces the Excel sheet so here
- we use a form to do so instead of formulating the sql statement again when we export to excel
- I pass the statement above inside a hidden field called 'InpSQL' below, this makes sure that
- what they see on this page is exactly what they get in Excel.
- -->
- <form method="POST" action="02extoex.asp">
- <table border=0 width="100%" cellspacing="0" cellpadding="2">
- <tr>
- <td>
- <input type="submit" value="Export To Excel Spread Sheet" name="B1" style="font-size: 8pt">
- <input type="hidden" name="InpSQL" size="63" value="<%=sql1%>"></td>
- </tr>
- </table>
- </form>
- <!-- This area is for display to the user and shows how the data to be exported will look -->
- <table border="1" width="100%" cellspacing="0" cellpadding="2" bordercolor="#000000">
- <tr>
- <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field1Header</font></td>
- <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field2Header</font></td>
- <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field3Header</font></td>
- <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field4Header</font></td>
- <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field5Header</font></td>
- <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field6Header</font></td>
- <td bgcolor="#000080"><font face="Arial" size="2" color="#FFFFFF">Field7Header</font></td>
- </tr>
- <%
- 'Whilst there are records to show keep going
- Do While Not RS1.eof
- %>
- <!--
- Note the use of in each field this makes sure the data looks OK in
- a bordered table if the return is null.
- -->
- <tr>
- <td><font face="Arial" size="2"><%=RS1("Field1Result")%> </font></td>
- <td><font face="Arial" size="2"><%=RS1("Field2Result")%> </font></td>
- <td><font face="Arial" size="2"><%=RS1("Field3Result")%> </font></td>
- <td><font face="Arial" size="2"><%=RS1("Field4Result")%> </font></td>
- <td><font face="Arial" size="2"><%=RS1("Field5Result")%> </font></td>
- <td><font face="Arial" size="2"><%=RS1("Field6Result")%> </font></td>
- <td><font face="Arial" size="2"><%=RS1("Field7Result")%> </font></td>
- </tr>
- <%
- 'Loop through each record and write it to the screen
- RS1.Movenext
- Loop
- End If
- %>
- </table>
- </body>
- </html>
- <%
- 'Clean up and close the connections and recordset objects
- RS1.Close
- Conn.Close
- Set Conn = Nothing
- Set RS1 = Nothing
- %>
Expand|Select|Wrap|Line Numbers
- <%@ Language=VBScript %>
- <!-- #include file="../incfiles/adovbs.inc" -->
- <%
- 'Declare some variables
- dim Conn,RS1
- '==================CONNECTION CODE TO SQL SERVER=======================
- 'Create a connection object to the database
- Set Conn = Server.CreateObject("ADODB.Connection")
- 'Create a recordset object
- Set RS1 = Server.CreateObject("ADODB.RecordSet")
- 'Feed the connection string into a variable called strConn
- strConn = "Provider=SQLOLEDB;Data Source=MYSERVERNAME;Initial Catalog=MYDATABASENAME;"&_
- "User Id=USERNAME;Password=PASSWORD;"
- 'Feed the connection string into the connection object
- Conn.Open strConn
- '==========================CONNECTION CODE=============================
- 'Obtain the sql statement which we fed into the hidden field in the last page
- sql1 = Request("InpSQL")
- 'If you aren't happy with doing that repeat your sql statement here
- 'sql1 = "Select * from tblMYTABLE WHERE ID < 10000"
- 'The assumption here is that if we saw data in the last page the data exists so there is no need to
- 'test again for errors so we just go for the data
- RS1.Open sql1, Conn
- 'This is the the code which tells the page to open Excel and give it the data to display
- Response.ContentType = "application/vnd.ms-excel"
- 'You can give the spreadsheet a name at the point its produced
- Response.AddHeader "Content-Disposition", "attachment; filename=MYSPREADSHEETNAME.xls"
- %>
- <!--
- Note that I have formatted the output header here to a dark blue background and white text
- this will be reflected in the spreadsheet when its produced and you could extend this to your own tastes of course.
- -->
- <table border="1" width="100%">
- <tr>
- <td bgcolor="#000080"><font color="#FFFFFF">Field1Header</font></td>
- <td bgcolor="#000080"><font color="#FFFFFF">Field2Header</font></td>
- <td bgcolor="#000080"><font color="#FFFFFF">Field3Header</font></td>
- <td bgcolor="#000080"><font color="#FFFFFF">Field4Header</font></td>
- <td bgcolor="#000080"><font color="#FFFFFF">Field5Header</font></td>
- <td bgcolor="#000080"><font color="#FFFFFF">Field6Header</font></td>
- <td bgcolor="#000080"><font color="#FFFFFF">Field7Header</font></td>
- </tr>
- <%Do While Not RS1.eof%>
- <tr>
- <td><%=RS1("Field1Result")%></td>
- <td><%=RS1("Field2Result")%></td>
- <td><%=RS1("Field3Result")%></td>
- <td><%=RS1("Field4Result")%></td>
- <td><%=RS1("Field5Result")%></td>
- <td><%=RS1("Field6Result")%></td>
- <td><%=RS1("Field7Result")%></td>
- </tr>
- <%
- RS1.Movenext
- Loop
- %>
- </table>
- <%
- RS1.Close
- Conn.Close
- Set Conn = Nothing
- Set RS1 = Nothing
- %>