Help Needed - Connection with Parameters.

[zwieback89]

I am pulling information from the Pubs database. I create an ASP page that shows the list of all the titles. These titles are pulled from the titles table.

The ASP page that opens the report title.rpt is as follows:

<%
reportname = &quot;title.rpt&quot;
<!--#include file=RequiredSteps.asp-->

'provide the server and the database values.
rptServer = &quot;CVWeb&quot;
rptDatabase = &quot;ClinicalVision1&quot;
rptUserid = &quot;sa&quot;
rptPassword = &quot;&quot;

' Set the location
set crtable = session(&quot;oRpt&quot.Database.Tables.Item(1)
crtable.SetLogonInfo CStr(rptServer), CStr(rptDatabase), cstr(rptUserid), cstr(rptPassword)

'to test whether or not connection is made to the server
response.write crtable.testconnectivity

'Set the Title ID parameter
Set session(&quot;ParamCollection&quot = Session(&quot;oRpt&quot.Parameterfields
Set Param1 = session(&quot;ParamCollection&quot.Item(1)
Title = Request.Form(&quot;cmbTitle&quot
Response.Write title
Call Param1.SetCurrentValue (cstr(Title), 12)
session(&quot;oRpt&quot.RecordSelectionFormula = &quot;{titles.title_id} = {?TitleID}&quot;

<!--#include file=MoreRequiredSteps.asp-->
<!--#include file=SmartViewerActiveX.asp-->

-------------------------------------------------
If I comment the code for the setting of location and mention the location in the CR, then I am able to pass my ASP variable to the CR and see the respective title ID in the CR.

Infact testConnectivity returns a value of true. and I am able to write the title ID to this ASP page. So both of them are fine.

But when I use the Setlogon method of providing the Database ServerName, Database Name, UserID and Password, and strip off the the database name, servername from the CR, then I get the following error:

Error detected by database DLL.
-2147192179
Seagate Crystal Reports ActiveX Designer

Does this connection works when we try to pass parameters to the CR from ASP pages ?

Hope to hear more on this. Please tell me what I could have done wrong. It is like I am so near, yet something is eluding me.

Regards, Padmaja.

 

[balves]

I'm not quite sure what isn't working. Where are your stripping off the servername and database name? Brian J. Alves
Email: brian.alves@worldnet.att.net
VB / ASP / Crystal / SQLServer

 

[zwieback89]

Ok, Let me explain the the situation in greater detail.

Scenario 1:
-----------------------------------------------------
I have a page called Titles.asp where I am pulling all the titles from the table titles of the pub database. This page is directed to SetLogOnInfo.asp where I retreive the value of the title ID that I want to view.

In the SetLogOnInfo.asp, I do the following:

<%
reportname = &quot;title.rpt&quot;
<!--#include file=RequiredSteps.asp-->

'Set the Title ID parameter
Set session(&quot;ParamCollection&quot = Session(&quot;oRpt&quot.Parameterfields
Set Param1 = session(&quot;ParamCollection&quot.Item(1)
Title = Request.Form(&quot;cmbTitle&quot
Response.Write title
Call Param1.SetCurrentValue (cstr(Title), 12)
session(&quot;oRpt&quot.RecordSelectionFormula = &quot;{titles.title_id} = {?TitleID}&quot;

<!--#include file=MoreRequiredSteps.asp-->
<!--#include file=SmartViewerActiveX.asp-->

In the CR, I design the report by supplying the servername and the database name. Then I also develop a parameter called TitleID that takes the value from the ASP page.

So when I click on the button 'View Report', I get to see the Title.rpt with that title displayed in the report.

This scenario works fine.

---------------------------------------------------------

2nd Scenario:

(Providing the Database Server and the Database values in the ASP pages.)

1. I pass the Title ID to the Setlogoninfo.asp
2. My code in the SetLogOnInfo.asp is as follows now:

<%
reportname = &quot;title.rpt&quot;
<!--#include file=RequiredSteps.asp-->

'provide the server and the database values.
rptServer = &quot;CVWeb&quot;
rptDatabase = &quot;ClinicalVision1&quot;
rptUserid = &quot;sa&quot;
rptPassword = &quot;&quot;

' Set the location
set crtable = session(&quot;oRpt&quot.Database.Tables.Item(1)
crtable.SetLogonInfo CStr(rptServer), CStr(rptDatabase), cstr(rptUserid), cstr(rptPassword)

'to test whether or not connection is made to the server
response.write crtable.testconnectivity

'Set the Title ID parameter
Set session(&quot;ParamCollection&quot = Session(&quot;oRpt&quot.Parameterfields
Set Param1 = session(&quot;ParamCollection&quot.Item(1)
Title = Request.Form(&quot;cmbTitle&quot
Response.Write title
Call Param1.SetCurrentValue (cstr(Title), 12)
session(&quot;oRpt&quot.RecordSelectionFormula = &quot;{titles.title_id} = {?TitleID}&quot;

<!--#include file=MoreRequiredSteps.asp-->
<!--#include file=SmartViewerActiveX.asp-->

3. Now that I mention the Database Server Name and the Database Name in the ASP page and I am passing it to the report, I go to the Set Location of the Database menu of the Title.rpt There I see dbo.pubs.title in the text box. What I do is cut off the dbo.pubs and leave the name of the table over there. (Is this what you were asking ?)

4. Now I click on the button &quot;View Report&quot;, I get the error:

Error detected by database DLL.
-2147192179
Seagate Crystal Reports ActiveX Designer

I also do a
Response.Write TitleID and
Response.Write textconnectivity

I can see that my titleId is being passed from the title.asp to the Setlogoninfo.asp

And I can see that testconnectivity returns a value of true.

So there is something amiss which I am not able to point as to what I am missing.

Hoping to hear from you soon.

P.S: I know that this might be asking too much. But since this test page pulls from the pubs database, is it possible to send the files to you so that you can see what I could have done wrong ?

Regards,
Padmaja.

 

[balves]

The path for the table is dbname.dbo.tablename. As in Northwind.dbo.Customers. I strip off &quot;Northwind&quot; only.

There's a whole range of reasons for this error. On the Crystal support site you can download a document with all of the reasons: cr_error_detected_by_db_dll.pdf

One prominent reason is if you connect to a database through ASP that does not have the exact same table structures as the database originally used to create the report. Brian J. Alves
Email: brian.alves@worldnet.att.net
VB / ASP / Crystal / SQLServer

 

[zwieback89]

Hi Brian,

In CrystalDecisions, there was no such file as cr_error_detected_by_db_dll.pdf

If you have that file can you mail it to my email address at coolpersona@hotmail.com

Have you ever worked with reports that work from one server and then migrating to another database server. Did you manually change the location of all the report files to point to the new database server or did you choose another route ?

Do let me know of the way I could approach this.

Regards,
Padmaja.

 

[balves]

This file: cr_error_detected_by_db_dll.pdf

is available on this page by entering it in the text box:

http://support.crystaldecisions.com/library/

I've always started building reports on a test server, then put the reports on the live server. The test server is always a copy, table for table, column for column, of what will go live. Brian J. Alves
Email: brian.alves@worldnet.att.net
VB / ASP / Crystal / SQLServer

 

[zwieback89]

Even in my case, I develop against the test server (which is in my office) and then I ship it to UK and other clients who need them. Since the offices there have a different web server, though the tables, column names are the same, the server name has to be changed. With so many reports, it can be tedious to change the server location in some 30 reports. (Using the Database -> Set Location method).

Hence the need to pass servername and database name from the ASP pages so that I make the change of the server name in only 1 place.



Padmaja.

 

[Guest_imported]

I've received this EXACT error when the permissions on the Stored Procedure weren't set for the Role that the user was assigned to. It was a SQL Server 7.0 Server.

It's an easy fix, and might not have been your problem. But you never know.

 

[zwieback89]

Hi Amit,

I was getting the errors because then I was working with CR 8.0 Apparently when CR 8.0 is installed, then it changes the default setting of the ODBC registry values. There were several articles in their knowledge base which mentioned several steps to use in order to restore the ODBC values. I do not remember the article ID, but you might want to look into those articles.

But I took a shortcut. I had just received the upgrade 8.5 which solves all these bugs of version 8.0. So I upgraded, changed the code of the ASP files so that I can view the reports based on an ODBC created in the web server. I looked into the examples of ASPEXMPS85.exe which gives a list of examples to follow in case one uses CR 8.5. Then I did not get the error of &quot;ODBC or DNS not found&quot;. I was able to view the reports with the parameters passed from ASP pages.

Hope this helps,
Padmaja.

 

[fazman]

I am also having the same annoying problem. I am using crystal report 8.0 .. please..please let me know if you had found the solution for this

faz