Export data to CSV

[cookieathome]

Hi Guys,
Please help. I am trying to export data from an Access Database, to a csv file.
I found this item below, and it works for me 99%.
http://bytes.com/forum/thread53517.html

<object runat="server" progid="Scripting.FileSystemObject"
id="oFSO"></object>
<%
 
Dim oADO, oRS
Dim sOutput
Dim aZIPs, i, sZIP
 
Const OUTPUT_PATH = "D:\Path\"
 
 
sSQL = "SELECT DISTINCT(ZIP) FROM Customers"
Set oADO = Server.CreateObject("ADODB.Connection")
oADO.Open YourConnectionString
Set oRS = oADO.Execute(sSQL)
aZIPs = oRS.GetRows()
oRS.Close : Set oRS = Nothing
 
For i = 0 To UBound(aZIPs, 2)
sZIP = aZIPs(0, i)
sSQL = "SELECT CustID,Firstname,Lastname,Address,City,State,ZIP FROM
Customers WHERE ZIP='" & sZIP & "'"
Set oRS = oADO.Execute(sSQL)
sOutput = oRS.GetString(,,",",vbCrLf)
oRS.Close : Set oRS = Nothing
oFSO.CreateTextFile(OUTPUT_PATH & sZIP & ".csv", True).Write sOutput
Response.Write "<a href=""" & sZIP & ".csv"">Click here to download CSV
for ZIP code " & sZIP & "</a><br>"
Next
 
oADO.Close : Set oADO = Nothing
%>

When I run this, every record in the database is exported.
But, I need the ability to specify which record is exported, preferably with a URL being sent from the previous page (ie mypage.asp?order=123456)
The order is unique to each record.
I think it must be something to do with the for next loop.
If I just remove this, only the first record from the database is 'produced', and I am really stuck!
Any help would be great.

Thank you.
Cookie

[JamieHowarth0]

Hi Cookie,

Try this:

sSQL = "SELECT CustID,Firstname,Lastname,Address,City,State,ZIP FROM
Customers WHERE ZIP='" & sZIP & "' AND OrderID = " & Request.QueryString("order") & ";"

This adds an extra filter to the query so you can then retrieve your customers by their respective Order ID (note: change OrderID to the corresponding field name in your database).

Hope this helps.

medicineworker

[cookieathome]

Hi,
Thank you for the quick reply. It almost works!
This is the URL that is passed. page.asp?Order1=70244690001MQ2

I have tweaked the code to suite my needs, and included your suggestion. I now get the following error.

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Order1=70244690001MQ2'.


Below is the code copied from my site.

<object runat="server" progid="Scripting.FileSystemObject"
id="oFSO"></object>
<%

Dim oADO, oRS
Dim sOutput
Dim aOrder1s, i, sOrder1

Const OUTPUT_PATH = "c:\exportedfile\"


sSQL = "SELECT DISTINCT(Order1) FROM Import"
Set oADO = Server.CreateObject("ADODB.Connection")
oADO.Open "DBQ=" & Server.MapPath("fpdb/importdata.mdb") & ";Driver={Microsoft Access Driver (*.mdb)}"
Set oRS = oADO.Execute(sSQL)
aOrder1s = oRS.GetRows()
oRS.Close : Set oRS = Nothing

For i = 0 To UBound(aOrder1s, 2)
sOrder1 = aOrder1s(0, i)
sSQL = "SELECT * FROM Import WHERE Order1=" & Request.QueryString("Order1") & ";"
Set oRS = oADO.Execute(sSQL)
sOutput = oRS.GetString(,,",",vbCrLf)
oRS.Close : Set oRS = Nothing
oFSO.CreateTextFile(OUTPUT_PATH & sOrder1 & ".csv", True).Write sOutput
Response.Write "<a href=""" & sOrder1 & ".csv"">New File Downloaded " & sOrder1 & "</a><br>"
Next

oADO.Close : Set oADO = Nothing
%>

[JamieHowarth0]

Aha! The problem is found!

Your Order1 field in your database appears to be a string. In SQL, string values must be encapsulated by quote marks, otherwise SQL will treat them as a number, reserved word, or alias for column, table, or whatever. So, your new SQL code should be:

sSQL = "SELECT CustID,Firstname,Lastname,Address,City,State,ZIP FROM Customers WHERE ZIP='" & sZIP & "' AND OrderID = '" & Request.QueryString("order") & "';"
 

Note the use of single quote (or apostrophe) marks after OrderID = .
This will then interpret the QueryString input as a string value and nothing else, which should make your query work.

Hope it helps!

medicineworker