REFRESHING DATA IN A CRYSTAL REPORT VIA VB

[jodo]

Using CR V8.5, VB 6, OCX.
I'm trying to retrieve data from a CR with a data range that is specified by a user on a VB form. It works, finally; however, I'm still not getting all of my data back unless I manually go into CR and refresh the data. I've tried going to Report Options under the File Menu and unchecking Save Data with Report. When I run the report after unchecking this, I get Run-Time error '20599', cannot open SQL server.

I've also tried discarding the saved data with this:
frmMain.crptTCHistory.DiscardSavedData = True
But, I get the same Run-Time Error.

Any help is appreciated.
Thank you.

 

[kenhamady]

See faq766-1006 which will tell you that the error is good news. That means it is trying to make a connection to the database and is failing. You are probably not making a connection to the database in your application before you run the report. If you are using the OCX look at the Connect or LogOnInfo commands. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[jodo]

I have read your comments and they make sense, but I am connecting via the .connect property and I'm still receiving another error as you said I probably would. But now I am receiving a DLL error (Error Detected by database DLL - Run Time Error '20534') I've researched this error and it says: Detects replication agents that are not logging history actively. I'm not sure how to correct this problem. I've attached my code, is there something wrong with it?

Function ReportOptions(ByVal finalsql)
Dim PathName, strSelect, strSelectionFormula As String
Dim strSubHeading As String
PathName = App.Path + "\reports\"

frmMain.crptTCHistory.Connect
= "UID=omardba;PWD=dba;DSN=gisdb1"
frmMain.crptTCHistory.Destination = crptToWindow
frmMain.crptTCHistory.WindowTitle = "TROUBLE CALL REPORT"
frmMain.crptTCHistory.ReportFileName = PathName + "crptTCHistory.rpt"

'Passes the startdate and the enddate to CR so it can populate the date range on the report
strSubHeading = "'" & frmMain.cbStartDate & " through " & frmMain.cbEndDate & "'"
frmMain.crptTCHistory.Formulas(0) = "SubHeading=" & strSubHeading

strSelectionFormula = "{TC_HISTORY3.CREATED} in Date(" & _
Format(frmMain.cbStartDate, "yyyy,mm,dd&quot & &quot to Date(" & _
Format(frmMain.cbEndDate, "yyyy,mm,dd&quot & &quot " & finalsql & ""
frmMain.crptTCHistory.ReplaceSelectionFormula (strSelectionFormula)
frmMain.crptTCHistory.Action = 1

End Function

 

[kenhamady]

I am not sure, but I think your argument order is incorrect on the connect. Did you try connect UID/DSN/PWD? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[balves]

I don't believe that the parameter order matters in .Connect, but I could be wrong.

Why don't you strip it down to just connecting and running the report as is. Don't replace the selection formula, etc. See if that runs

 

[jodo]

balves,
I did what you said and it works without any selection formula and it pulls all of the data, but I need the selection formula to work. I don't know why it isn't. I don't have enough experience with CR to troubleshoot. Any ideas? Thanks for your help.

jodo

 

[balves]

strSelectionFormula = "{TC_HISTORY3.CREATED} in Date(" & _
Format(frmMain.cbStartDate, "yyyy,mm,dd&quot & &quot to Date(" & _
Format(frmMain.cbEndDate, "yyyy,mm,dd&quot & &quot " & finalsql & ""

I don't think this is legal SQL. Don't see what "finalsql" is in your code either.

It's usually {your date field} > some_date AND
{your date field} < some_date

I would strip this statement down and get it working for just one clause, like {your date field} > some_date, then add another clause.

 

[jodo]

I pass finalsql to this function (Function ReportOptions(ByVal finalsql)) which is shown above. I definitely need it to check the date criteria that the user selects on the main form, but there is also other options the user could select as criteria as well, and that's what the finalsql is. In the sending function, finalsql is a string that is derived from an array. Other functions are assigned to the array. The other functions test to see if any other criteria are selected, assigns it to the array, tests the array, then concatenates the value with AND (shown below). This is a legal statement because it works as long as the the SAVED DATA WITH REPORT is selected under File->Report Options and File->Options->Reporting. My problem is that I don't want SAVED DATA, I want current, refreshed data, but when I run it with these options unchecked I get Run-Time Error '20534' - Error Detected by Database DLL. Any suggestions?

Thanks.

strSQL(1) = cbTCNumberThere()
strSQL(2) = cbTCStreetThere()
strSQL(3) = cbTCPhoneThere()
strSQL(4) = cbTCNameThere()
strSQL(5) = cbWONumberThere()
finalsql = &quot;&quot;
nada = &quot;&quot;

''Assigns the sql statement with the value that is selected on the form'''
For n = 1 To 6
If strSQL <> &quot;&quot; Then
finalsql = finalsql + &quot;AND &quot; + strSQL
End If
Next

If finalsql <> &quot;&quot; Then
Call ReportOptions(finalsql)
Else
Call ReportOptions(nada)
End If