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

[code=asp]
<object runat="server" progid="Scripti ng.FileSystemOb ject"
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.CreateOb ject("ADODB.Con nection")
oADO.Open YourConnectionS tring
Set oRS = oADO.Execute(sS QL)
aZIPs = oRS.GetRows()
oRS.Close : Set oRS = Nothing

For i = 0 To UBound(aZIPs, 2)
sZIP = aZIPs(0, i)
sSQL = "SELECT CustID,Firstnam e,Lastname,Addr ess,City,State, ZIP FROM
Customers WHERE ZIP='" & sZIP & "'"
Set oRS = oADO.Execute(sS QL)
sOutput = oRS.GetString(, ,",",vbCrLf)
oRS.Close : Set oRS = Nothing
oFSO.CreateText File(OUTPUT_PAT H & sZIP & ".csv", True).Write sOutput
Response.Write "<a href=""" & sZIP & ".csv"">Cli ck here to download CSV
for ZIP code " & sZIP & "</a><br>"
Next

oADO.Close : Set oADO = Nothing
%>[/code]

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?orde r=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:
[code=asp]
sSQL = "SELECT CustID,Firstnam e,Lastname,Addr ess,City,State, ZIP FROM
Customers WHERE ZIP='" & sZIP & "' AND OrderID = " & Request.QuerySt ring("order") & ";"[/code]

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=7024469 0001MQ2'.


Below is the code copied from my site.

<object runat="server" progid="Scripti ng.FileSystemOb ject"
id="oFSO"></object>
<%

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

Const OUTPUT_PATH = "c:\exportedfil e\"


sSQL = "SELECT DISTINCT(Order1 ) FROM Import"
Set oADO = Server.CreateOb ject("ADODB.Con nection")
oADO.Open "DBQ=" & Server.MapPath( "fpdb/importdata.mdb" ) & ";Driver={Micro soft Access Driver (*.mdb)}"
Set oRS = oADO.Execute(sS QL)
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.QuerySt ring("Order1") & ";"
Set oRS = oADO.Execute(sS QL)
sOutput = oRS.GetString(, ,",",vbCrLf)
oRS.Close : Set oRS = Nothing
oFSO.CreateText File(OUTPUT_PAT H & 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:
[code=sql]
sSQL = "SELECT CustID,Firstnam e,Lastname,Addr ess,City,State, ZIP FROM Customers WHERE ZIP='" & sZIP & "' AND OrderID = '" & Request.QuerySt ring("order") & "';"
[/code]
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