For this example we will create a Comma Separated Values (CSV) file with our information in, and in the first case simply provide a link to this file and in the second we will automatically trigger a download of this file to the local computer.
So, we need to get all of our data out of the datasource. for this example I am going to use the getString function which returns a recordset as a single string, although for more complex processes you may need to extract each element of the recordset in turn to perform an action on it, such as adding to a total, formatting etc.
Dim dbConn
Dim SQLStatement
Dim results
sub db_connect
' Create a connection object
set dbConn = server.createobject("ADODB.connection")
' Open a connection using the following criteria - DSN Name, UserName, Password
dbConn.open "myDSN", "username", "password"
end sub
sub db_disconnect
dbConn.close
end sub
db_connect
SQLStatement="SELECT * FROM myTable"
set results=dbConn.execute(SQLStatement)
' if no results found
if results.EOF then
' no results
Response.Write("No Data Found")
' otherwise
else
Dim ResultsString
Dim objFileSystemObject
Dim objTextStream
Dim filename
' grab the string, using a comma as a record separator and a tab as a record separator
ResultsString = results.GetString(2, , ",", vbTab)
' create a link to the local file system on the server
Set objFileSystemObject = Server.CreateObject("Scripting.FileSystemObject")
' Create a text file and dump the contents of the string to the file,
' using the file name export with the current date and time as a way to seperate out differing users
filename="export" & CLng(Now)
Set objTextStream = objFileSystemObject.CreateTextFile(filename & ".csv", 2, True)
objTextStream.Write(ResultsString)
' Clean up
objTextStream.Close
Set objTextStream = Nothing
Set objFileSystemObject = Nothing
' return a link to the file to the browser
Response.Write("The file <a href='" & filename &".csv'>" & filename & ".csv</a> is now available to download")
end if
In the final section of this article, we summarise the different approaches and provide links for futher reading on the subject.