asked on

Crystal Report ASP Stream to PDF "Failed to retrieve data from the database"

I have a Crystal Report (version XI) that I launch from a VB.NET Web App. It has worked for months. Then, yesterday, it suddently stopped working. I redeployed my files to the web server, thinking maybe something got corrupted out there, but it didn't help. The code I am using to run the Crystal Report (that worked fine for a long time) is as follows:

Private Sub ShowReportEmbedded()
Dim oRpt As sl_List4
oRpt = New sl_List4
oRpt.SetDatabaseLogon(strUser, strPassword)

sSQL.Length = 0
sSQL.Append(" {SL_LIST.ListSite} = '" & Request.Item("slSite").ToString & "' ")
sSQL.Append("AND {SL_LIST.ListRev} = '" & Request.Item("slRev").ToString & "' ")
sSQL.Append("AND date({SL_LIST.ShipListDate}) = date('" & CDate(Left(Request.Item("slDate").ToString, 2) & "-" & Mid(Request.Item("slDate").ToString, 3, 3) & "-" & Right(Request.Item("slDate").ToString, 2)) & "') ")

oRpt.RecordSelectionFormula = sSQL.ToString

Dim st As System.IO.MemoryStream
st = oRpt.ExportToStream(CrystalDecisions.[Shared].ExportFormatType.PortableDocFormat)

Response.ClearContent()
Response.ClearHeaders()
Response.ContentType = "application/PDF"
Response.AddHeader("Content-Disposition", "inline; filename=list.pdf")
Response.BinaryWrite(st.ToArray)
Response.Flush()
Response.Close()
End Sub

The error says: Failed to retrieve data from the database.

Any ideas?

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

frodoman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AptDev

ASKER

After reading your question "What changed" it suddenlty hit me that I had added a field to that table. A little more exploration showed me the line that was causing the problem was the line about dates. I just worked with it until it worked. Although I have no idea how the table changed to require the new code, I simply changed from a date to a datetime and it worked flawlessly. Thank you both for your comments that lead me in that direction. Here is the only change I made to the above code to get it to work:

sSQL.Append("AND {SL_LIST.ShipListDate} = datetime('" & CDate(Left(Request.Item("slDate").ToString, 2) & "-" & Mid(Request.Item("slDate").ToString, 3, 3) & "-" & Right(Request.Item("slDate").ToString, 2)) & "') ")

Thank you both for your endless help on this forum!

Mike McCracken

Anytime you change the table you need to VERIFY THE DATABASE in Crystal. Under the DATABASE menu

Glad i could help

mlmcc

Crystal Report ASP Stream to PDF &quot;Failed to retrieve data from the database&quot;

Crystal Report ASP Stream to PDF "Failed to retrieve data from the database"