|
| |
|
|
sentinel
Posts: 566
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
 |
BOE / EOF Error When Exporting to Excel - 11/7/2005 11:09:39
Hey all...
I have a page that list database results. At the bottom of the page i have a form that exports results to excel.
My form looks like this.
<form method="POST" action="csv_export.asp">
<p align="center" style="margin-top: 0px; margin-bottom: 0px">
<input type="submit" value="Export To Excel" name="B1" style="color: #0000FF; font-size: 8pt; border: 1px solid #0000FF; background-color: #FFFFFF; float:left"></p>
<p align="center" style="margin-top: 0px; margin-bottom: 0px">
</p>
<input type="hidden" name="exportquery" value="SELECT * FROM district WHERE (DPS = '::DPS::')">
</form>
When I click teh export button it asks me to save or open. If i hit save I get a can not save error. If i hit open I receive this error
Error Type:
ADODB.Recordset (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/csv_export.asp, line 17
Here is my csv_export.asp page
<%
Dim RS, SQL, Conn, myDSN
myDSN="DSN=Wagner MySQL;DRIVER={MySQL ODBC 3.51 Driver};UID=root;PWD=password; option=3"
set conn=server.createobject("adodb.connection")
conn.open myDSN
SQL = myQuery
Set RS = Conn.Execute(Request.form("exportQuery"))
Dim F, Head
For Each F In RS.Fields
Head = Head & ", " & F.Name
Next
Head = Mid(Head,3) & vbCrLf
call Response.AddHeader("Content-Disposition", "attachment;filename=exportdata.csv")
Response.ContentType = "application/download"
Response.Write Head
Response.Write RS.GetString(,,", ",vbCrLf,"")
conn.close
set conn=nothing
%>
can you see whatthe issue is?
Thanks
_____________________________
No matter where you go, there you are.
|
|
|
|
sentinel
Posts: 566
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: BOE / EOF Error When Exporting to Excel - 11/7/2005 16:00:42
when i put an actual value in it works.
i tried
DPS = '" & ("DPS") & "'
but that had no effect either.
_____________________________
No matter where you go, there you are.
| |
|
|
|
sentinel
Posts: 566
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: BOE / EOF Error When Exporting to Excel - 11/7/2005 16:02:01
My new export code is
<%
Dim RS, SQL, Conn, myDSN
myDSN="dsn=Wagner MySQL;DRIVER={MySQL ODBC 3.51 Driver};UID=root;PWD=password; option=3"
set conn=server.createobject("adodb.connection")
conn.open myDSN
SQL =Request.form("exportQuery")
Set RS = Conn.Execute(Request.form("exportQuery"))
Dim F, Head
For Each F In RS.Fields
Head = Head & ", " & F.Name
Next
Head = Mid(Head,3) & vbCrLf
call Response.AddHeader("Content-Disposition", "attachment;filename=exportdata.csv")
Response.ContentType = "application/download"
Response.Write Head
Response.Write RS.GetString(,,", ",vbCrLf,"")
conn.close
set conn=nothing
%>
and the new form code looks like this
<%
myvar="SELECT dist, dps FROM district WHERE (DPS = '::DPS::')"
%>
<form method="POST" action="csv_export.asp">
<p align="center" style="margin-top: 0px; margin-bottom: 0px">
<input type="submit" value="Mt. Prospect to Excel" name="B1" style="color: #0000FF; font-size: 8pt; border: 1px solid #0000FF; background-color: #FFFFFF; float:left"></p>
<p align="center" style="margin-top: 0px; margin-bottom: 0px">
<textarea rows="2" name="S1" cols="20"><%=myvar%></textarea></p>
<input type="hidden" name="exportquery" value="<%=myvar%>">
</form>
i still get eof / bof errors though. I cant figure out why?
_____________________________
No matter where you go, there you are.
| |
|
|
|
rdouglass
Posts: 9206
From: Biddeford, ME USA
Status: offline
|
RE: BOE / EOF Error When Exporting to Excel - 11/7/2005 16:19:11
Are you absolutely sure there should be results?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
sentinel
Posts: 566
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: BOE / EOF Error When Exporting to Excel - 11/7/2005 16:42:07
If i run the query inmysql query browser with a hard coded value for DPS i will get results.
_____________________________
No matter where you go, there you are.
| |
|
|
|
rdouglass
Posts: 9206
From: Biddeford, ME USA
Status: offline
|
RE: BOE / EOF Error When Exporting to Excel - 11/7/2005 16:44:00
quote:
Set RS = Conn.Execute(Request.form("exportQuery"))
What happens when you change that line to:
Set RS = Conn.Execute("SELECT * FROM district")
Any results? I see one issue when you're trying to pass a DRW request "thingy" (::DPS::) to a straight ASP connection - it doesn't know what to make of those ::'s.
The line I posted above should take all that out of the equation temporarily.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
sentinel
Posts: 566
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: BOE / EOF Error When Exporting to Excel - 11/7/2005 16:47:26
Gives me a SYLK error when opening the export file.
I just took out the Select * and put in
Select dist, dps from district.
< Message edited by sentinel -- 11/7/2005 16:53:34 >
_____________________________
No matter where you go, there you are.
| |
|
|
|
Spooky
Posts: 26599
Joined: 11/11/1998
From: Middle Earth
Status: offline
|
RE: BOE / EOF Error When Exporting to Excel - 11/7/2005 16:55:02
"SYLK"?
| |
|
|
|
sentinel
Posts: 566
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: BOE / EOF Error When Exporting to Excel - 11/7/2005 17:00:48
SYLK: File format is not valid to be exact
_____________________________
No matter where you go, there you are.
| |
|
|
|
sentinel
Posts: 566
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: BOE / EOF Error When Exporting to Excel - 11/7/2005 17:01:44
why wouldnt this work?
Set RS = Conn.Execute("SELECT dist, dps FROM district where DPS = '" & ("DPS") & "' ")
_____________________________
No matter where you go, there you are.
| |
|
|
|
danrendrag
Posts: 24
Joined: 6/2/2004
From: Atlanta, Georgia USA
Status: offline
|
RE: BOE / EOF Error When Exporting to Excel - 11/7/2005 18:26:51
Shouldn't this:
quote:
SQL =Request.form("exportQuery")
Set RS = Conn.Execute(Request.form("exportQuery"))
Be this:
SQL = Request.Form("exportQuery")
Set RS = Conn.Execute SQL
| |
|
|
|
sentinel
Posts: 566
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: BOE / EOF Error When Exporting to Excel - 11/7/2005 22:32:44
quote:
Set RS = Conn.Execute("SELECT dist, dps FROM district")
that sql does return records.
It's when i try to pass the dps from a hyperlink using SELECT dist, dps FROM district where DPS = 'DPS'" when i get nothing
_____________________________
No matter where you go, there you are.
| |
|
|
|
sentinel
Posts: 566
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: BOE / EOF Error When Exporting to Excel - 11/8/2005 8:13:22
The response to using dps = 'dps' is the following
ADODB.Recordset (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/csv_export.asp, line 18
Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)
Page:
POST 191 bytes to /csv_export.asp
POST Data:
B1=Mt.+Prospect+to+Excel&
S1=SELECT+dist%2C+dps+FROM+district+WHERE+%28DPS+%3D++%27%3A%3ADPS%3A%3A%27%29
&exportquery=SELECT+dist%2C+dps+FROM+district+WHERE+%28DPS+%3D++%27%3A%3ADPS%3A%3A%27%29
I set text field (S1) in the form on the drw page that takes the <%=myvar%> so I could see exactly what SQL is being passed to it and the hidden exportquery field. The SQL is exactly the same as what is being used in the FPDRW results "SELECT dist, dps FROM district where DPS = 'DPS'"
If i run it without the where DPS = 'DPS' I get an entire dump of the database. which is not what i want. I wish to sort it by the data within the 'DPS' field.
Thanks...
_____________________________
No matter where you go, there you are.
| |
|
|
|
danrendrag
Posts: 24
Joined: 6/2/2004
From: Atlanta, Georgia USA
Status: offline
|
RE: BOE / EOF Error When Exporting to Excel - 11/8/2005 10:50:58
I have duplicated that error on my machine.
It appears to be due to a malformed and/or missing element in the SELECT statement.
Do as Spooky suggested above - output the content of your "exportquery" variable & you'll most likely find your problem.
| |
|
|
|
sentinel
Posts: 566
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: BOE / EOF Error When Exporting to Excel - 11/8/2005 11:34:55
Spooky...
I did what you said
SQL = Request.Form("exportQuery")
response.write SQL
response.end
Set RS = Conn.Execute SQL
and this is what I was presented with.
SELECT dist, dps FROM district where dps = 'dps'
_____________________________
No matter where you go, there you are.
| |
|
|
|
sentinel
Posts: 566
Joined: 5/4/2005
From: Chicago, Illinois
Status: offline
|
RE: BOE / EOF Error When Exporting to Excel - 11/8/2005 16:29:21
Yes viewing the source is exactly the same
_____________________________
No matter where you go, there you are.
| |
|
|
|
janhes
Posts: 1
Joined: 11/21/2005
Status: offline
|
RE: BOE / EOF Error When Exporting to Excel - 11/21/2005 6:30:24
Does the first column of data start with ID? If so this is what gives the SYLK error. Surround the data with quotes.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
k¥ 
