Export data

[emissions]

Help, needed with exporting data from a rs...

I've got a page, that queries a table in the database, and displays the results in a table...

Now I'm looking to export that data to a PDF file... I have a button that, passes the data to the file... but everytime, if returns BOF or EOF is true... as the recordset is closed...

I need to check if the RS.EOF or RS.BOF then reopen the connection to export the results... can anyone help me... Really confused...

 

[damber]

Your question is a little unclear

this checks the BOF/EOF before doing the export - is that what you want ?

if not(oRS.BOF and oRS.EOF) then
 'do the export
else
 'do whatever
end if

If not, what do you mean 'reopen the connection to export the results' - BOF/EOF means there are no records, are you saying that you've closed the connection and not cached the recordset ?



A smile is worth a thousand kind words. So smile, it's easy!

 

[DotNetGnat]

can you show the action code that you wrote for the button...

-DNG

 

[emissions]

'reopen the connection to export the results' as the connection is closed and not cached...

 

[DotNetGnat]

try something like this:

If objRS.State <> "0"
'your recordset is open
else
your recordset is closed.
end if

similarly with the connection object...

If objconn.State<>"0"
then...

-DNG

 

[emissions]

The code for the button is;

<% If Not rsStatus.EOF Or Not rsStatus.BOF Then %>
<a href="<%= Request.ServerVariables("SCRIPT_NAME") %>?excel=y"><img src="../img/table/print_button.gif" width="86" height="27" border="0" align="bottom"></a>
<% End If ' end Not rsStatus.EOF Or NOT rsStatus.BOF %>

With the main controller code for the export is;

<%
DIM objRS, line, filename, DateString

If Request.QueryString("excel")="y" Then

If rsStatus.EOF OR rsStatus.BOF Then

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.ActiveConnection = MM_Assist_STRING
objRS.Source = "SELECT dbo.tblJobInstructions#1.JobNumber, dbo.tblJobInstructions#1.StatusDate, dbo.tblSites.SiteName, dbo.tblSites.SiteNotes, dbo.tblFeatures.FeatureName, dbo.tblJobCategory1.JobCategory1Description, dbo.tblJobInstructions#1.JobStatus, dbo.tblJobInstructions#1.ContractIDKey FROM dbo.tblJobInstructions#1 INNER JOIN dbo.tblSites ON dbo.tblJobInstructions#1.[Site Code] = dbo.tblSites.SiteCode INNER JOIN dbo.tblFeatures ON dbo.tblJobInstructions#1.[Feature Code] = dbo.tblFeatures.FeatureCode INNER JOIN dbo.tblJobCategory1 ON dbo.tblJobInstructions#1.Category1ID = dbo.tblJobCategory1.JobCategory1ID WHERE (dbo.tblJobInstructions#1.ContractIDKey = '16') AND (dbo.tblJobInstructions#1.JobStatus = '" + Replace(rsStatus__VarStatus, "'", "''") + "')"
objRS.CursorType = 0
objRS.CursorLocation = 2
objRS.LockType = 1
objRS.Open()

DateString = Date()
DateString = Replace(Datestring, "/", "")

line=objRS.GetString(,,"[_comma_]","[_linebreak_]","")
line=Replace(line,",","¸" )
line=Replace(line,vbCrlf," ")
line=Replace(line,"[_linebreak_]",vbCr)
line=Replace(line,"[_comma_]",",")

Response.Buffer = TRUE
Response.ContentType = "application/pdf"
Response.AddHeader "Content-Disposition", "attachment; filename=" &Datestring& ".pdf"
Response.CacheControl="no-cache"
Response.Write(line)
Response.End()
End if

End if
%>

 

[Sheco]

That isnt a button...

Thats a href link...

A link back to the same page, except with a QueryString...

A QueryString with one item named Excel....

And if the value of Excel is 'y' you want to return a PDF.

 

[Sheco]

For testing purposes, try commenting out that big SQL statement and replace it with something short and simple like:
[tt]objRS.Source = "SELECT * FROM dbo.tblJobInstructions"[/tt]