BOE / EOF Error When Exporting to Excel (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

sentinel -> 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



Spooky -> RE: BOE / EOF Error When Exporting to Excel (11/7/2005 15:53:09)

What are you trying to achieve with this?

'::DPS::'

What happens when you perform the same action with an actual value ? 

<input type="hidden" name="exportquery" value="SELECT * FROM district WHERE (DPS ='myvalue')">



sentinel -> 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.



sentinel -> 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?



rdouglass -> RE: BOE / EOF Error When Exporting to Excel (11/7/2005 16:19:11)

Are you absolutely sure there should be results?



Spooky -> RE: BOE / EOF Error When Exporting to Excel (11/7/2005 16:26:42)

Try this : 

<%
myvar="SELECT dist, dps FROM district WHERE (DPS =  'DPS')"
%>


DPS should be a value contained in the column?



sentinel -> RE: BOE / EOF Error When Exporting to Excel (11/7/2005 16:39:11)

Spooky,

Yes, DPS is a value in a column.

WHERE (DPS = 'DPS')

did not work either [:@]

Gave me this error: 

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



Spooky -> RE: BOE / EOF Error When Exporting to Excel (11/7/2005 16:40:21)

You can run the same SQL via phpmyadmin and get results?



sentinel -> 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.



rdouglass -> 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.



sentinel -> 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.




Spooky -> RE: BOE / EOF Error When Exporting to Excel (11/7/2005 16:55:02)

"SYLK"?



sentinel -> RE: BOE / EOF Error When Exporting to Excel (11/7/2005 17:00:48)

SYLK: File format is not valid to be exact



sentinel -> 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") & "' ")





danrendrag -> 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



Spooky -> RE: BOE / EOF Error When Exporting to Excel (11/7/2005 19:25:39)

In theory, this would : 

 Set RS = Conn.Execute("SELECT dist, dps FROM district where DPS = 'DPS'")


Do you have any other values that you can test? I still find it strange that you returned no records from : 

 Set RS = Conn.Execute("SELECT dist, dps FROM district")


Some name or data is wrong.

Dan - it doesnt really make a difference as it means the same thing.
It would be an idea to do this to see what is being passed over : 


SQL = Request.Form("exportQuery")
response.write SQL
response.end
Set RS = Conn.Execute SQL



sentinel -> 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



Spooky -> RE: BOE / EOF Error When Exporting to Excel (11/8/2005 1:05:26)

Whats the response when you do it the other way?
I want to see the SQL it is trying to use (as opposed to what we think its using) 

SQL = Request.Form("exportQuery")
response.write SQL
response.end
Set RS = Conn.Execute SQL



sentinel -> 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...



danrendrag -> 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 -> 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'



Spooky -> RE: BOE / EOF Error When Exporting to Excel (11/8/2005 13:38:50)

If you "view source" of that page does it give you the same line?



sentinel -> RE: BOE / EOF Error When Exporting to Excel (11/8/2005 16:29:21)

Yes viewing the source is exactly the same



janhes -> 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.



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.125