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
3 3424
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
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
%>
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Bennett Haselton |
last post by:
I'm working on an ASP.Net project where I want to test code on a local
machine using a local database as a back-end, and then export it to
the production machine where it uses the hosting...
|
by: Shawn Mehaffie |
last post by:
I have the following class that I've wirtten to take a Dataset and
automatically export it to either XML, ASCII or Tab delimited file. The
reason I wrote it they way I did was that I don't want to...
|
by: Matt |
last post by:
I have an ASP page that calls ASP routines that I created that execute
a database query and return the results to a recordset. I then iterate
through the recordset and display the data in a table....
|
by: Do Park via SQLMonster.com |
last post by:
Hello all,
I don?t often export data from a table. I am wondering how you export data
from a table. I?d like to know how you export in real world.
Do you export data from a table to a flat...
|
by: maricel |
last post by:
Is there anybody out there who have any idea why EXPORT is relatively slower
when putting the output file on a network drive - map drive from onother PC
compared to putting it on my local PC drive...
|
by: Janne Ruuttunen |
last post by:
Hello DB2 people,
I'm having problems exporting >= 250000 lobs to IXF files with the
LOBSINFILE option, using a legacy DB2 2.1 system on Win NT.
If I don't specify a path for the lobs,...
|
by: Shawn Mehaffie |
last post by:
I have the following class that I've wirtten to take a Dataset and automatically export it to either XML, ASCII or Tab delimited file. The reason I wrote it they way I did was that I don't want to...
|
by: chaitu |
last post by:
Hi,
Can anybody tell me what the error code for Export is. Like i get the
message
SQL3104N The Export utility is beginning to export data to file...
|
by: smaczylo |
last post by:
Hello, I've recently been asked to work with Microsoft Access, and
while I feel quite comfortable with Excel, I'm at a complete loss with
databases. If someone could help me with this issue I'm...
|
by: kkshansid |
last post by:
i cannot export my sql server database to msaccess database which i usually did successfully 3weeks ago pls help me to find the error
so that i can correct it in future
error is
- Validating...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |