Solved

Failed to open a rowset.

Posted on 2004-03-23
16
32,540 Views
Last Modified: 2012-05-04
I have a crystal report which I created to show details from one table on a database. I am using the crystal viewer on an ASP.Net page to view the report and it works fine until I switch to a different database. I want to use the same report to look at both databases - one of which is our test server - and not have to maintain 2 copies of the report pointing to each server.  Here is the code I have now:

Dim myreport As New CrystalDecisions.CrystalReports.Engine.ReportDocument
Dim i As Integer
Dim logOnInfo As New CrystalDecisions.Shared.TableLogOnInfo

myreport.Load(Server.MapPath("Reports/TestReport6.rpt"))

' Loop through every table in the report.
For i = 0 To myReport.Database.Tables.Count - 1
    ' Set the connection information for current table.
    logOnInfo.ConnectionInfo.ServerName = "servername"
    logOnInfo.ConnectionInfo.DatabaseName = "dbname"
    logOnInfo.ConnectionInfo.UserID = "userid"
    logOnInfo.ConnectionInfo.Password = "password"
    myReport.Database.Tables.Item(i).ApplyLogOnInfo(logOnInfo)
Next i

myreport.Refresh()

CRV1.ReportSource = myreport
CRV1.Visible = True

CRV1 is the crystal viewer.  when I use server1/db1 on which the report was developed it is fine but when I change to server2/db2 - a copy of the first database with all of the same users, permissions, etc, I get this error:  Failed to open a rowset.

[InternalException: Failed to open a rowset.]
   .I(String , EngineExceptionErrorID )
   .D(Int16 , Int32 )
   .C(Int16 )
   CrystalDecisions.CrystalReports.Engine.FormatEngine.GetPage(PageRequestContext reqContext)
   CrystalDecisions.ReportSource.LocalReportSourceBase.GetPage(PageRequestContext pageReqContext)
   CrystalDecisions.Web.ReportAgent.u(Boolean a)
   CrystalDecisions.Web.CrystalReportViewer.OnPreRender(EventArgs e)
   System.Web.UI.Control.PreRenderRecursiveInternal()
   System.Web.UI.Control.PreRenderRecursiveInternal()
   System.Web.UI.Control.PreRenderRecursiveInternal()
   System.Web.UI.Page.ProcessRequestMain()


Any ideas what is wrong with this?
0
Comment
Question by:pmcgivern
  • 7
  • 4
  • 2
  • +3
16 Comments
 
LVL 2

Author Comment

by:pmcgivern
ID: 10658305
Should have said - I'm using VS.Net2003 and Crystal Reports 9.
0
 
LVL 1

Expert Comment

by:Krelek200
ID: 10658404
One way is to create two reports (one looks at the Test Server and one looks at the Prod Server) and import them in to a single report.  IF the Test Server's database is similar to the Productions database tables, you can connect them using a parameter and/or a common link between the main and the subreports.

Ben

As for the rowset:

Are you declaring your imports statement?
0
 
LVL 2

Author Comment

by:pmcgivern
ID: 10658749
>> Are you declaring your imports statement?

What is this and what does it do?  I haven't seen it in any of the crystal documentation.

As for creating two reports - does that mean the two will have to be maintained?  We alredy have over 400 reports so I don't think this is an option.
0
 
LVL 1

Expert Comment

by:Krelek200
ID: 10658990
The imports statement is usally declare in VB .Net.  If I understand it correctly if you are using an SQL datasource then you have to tell VB .Net to look at it

imports system.data.sqlclient

The imports statement tells VB .Net where to look for data.

As for creating two reports and maintaining them, you will have to do it unless you redeclare the datasource by choosing a new location each time you change it.  I do the same for some reports that are consistent between the test and production.

Ben
0
 
LVL 2

Author Comment

by:pmcgivern
ID: 10659159
Ah! That imports statement - I thought you were talking about a crystal thing.

The scenario here at the moment is that we are using Protean ERP system and the reports were created for use through that but now our database is being hammered so we're looking to create another mirror db for reports and use the reports through asp.  In protean we can change database and the reports run fine as they are - no need for two separate reports or changes to the reports - just switch and go.  However we don't know how they do this but are trying to reproduce this functionality for the web interface.  So while we know it is possible, we're not sure how.
0
 
LVL 1

Accepted Solution

by:
Krelek200 earned 250 total points
ID: 10659514
0
 
LVL 1

Expert Comment

by:Krelek200
ID: 10659532
You could use a window Form or a dropdown list to switch between the many databases.  The DB has to be the same information though.
0
 
LVL 4

Expert Comment

by:stasau
ID: 10659673
When you change from server1/db1 to server2/db2 you are changing the datasource location.  If your databases had the same name on each server you would be fine with your code above. This is going to sounds really silly but you need to add:

myreport.Database.Tables.Item(i).Location = "nameof table"

if your tables are named the same in both databases then do this:

myreport.Database.Tables.Item(i).Location = myreport.Database.Tables.Item(i).Location

in your loop after you apply the logon info.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 2

Author Comment

by:pmcgivern
ID: 10666999
Thanks for the help - a bit of stumbling aroud but I finally got it using a file dsn.  Here's the code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim myreport As New ReportDocument
    Dim logOnInfo As New TableLogOnInfo
    Dim crTable As Table

    myreport.Load(Server.MapPath("Reports/TestReport4.rpt"))

    myreport.SetDatabaseLogon("C:\Inetpub\wwwroot\ReportViewer\Reports\erp.dsn", "", "userID", "password")

    ' Loop through every table in the report.
    For Each crTable In myreport.Database.Tables
        Dim strLoc As String
        strLoc = crTable.Location
        strLoc = "dbname" & Right(strLoc, Len(strLoc) - InStr(strLoc, ".") + 1)
        logOnInfo.ConnectionInfo.ServerName = "C:\Inetpub\wwwroot\ReportViewer\Reports\erp.dsn"
        logOnInfo.ConnectionInfo.DatabaseName = "dbName"
        logOnInfo.ConnectionInfo.UserID = "userID"
        logOnInfo.ConnectionInfo.Password = "password"
        crTable.ApplyLogOnInfo(logOnInfo)
        crTable.Location = strLoc
    Next

    myreport.Refresh()
    CRV1.ReportSource = myreport
    CRV1.Visible = True
End Sub

By changing the myreport.SetDatabaseLogon and logOnInfo.ConnectionInfo.ServerName to point to the new DSN file I can change the db the report looks at.  The report is created using a file dsn.

Thanks.
0
 

Expert Comment

by:jrmcdona
ID: 10874606
pmcgivern,

not sure if you will still get this.


anyhow...

was this for SQL server? i have the same problem that is given me tons of headaches over and over. we have about 4 different servers that I have to keep the reports update for.

thanks,
jrmcdona
0
 
LVL 2

Author Comment

by:pmcgivern
ID: 10876156
Hi jrmcdona

This was SQL Server.  The file DSN contained the following:

[ODBC]
DB_TYPE=SQL Server
Driver=SQL Server
SERVER=servername
DATABASE=DBName
UseProcForPrepare=0

The code above was in ASP.Net and CRV1 is the crystal viewer - placed on the page.
If you need any more details just add a comment.  

Regards,
Paul McGivern
0
 

Expert Comment

by:jrmcdona
ID: 10879484
Thanks I havent tried the file DSN yet, but I will.

Did this work for servers with different IP addresses and different database names. My databases are exactly the same but it seems having different IP addresses and/or different database names cause some trouble with the way i am currently doing it.

which is this way:
http://www.tek-tips.com/gfaqs.cfm/lev2/4/lev3/100/pid/149/fid/4870

thanks, I will give it a shot.

jrmcdona
0
 
LVL 2

Author Comment

by:pmcgivern
ID: 10879958
I'm using different servers with different names, ip addresses and even different database names now with no problem.  I had to use the file DSN though - I spent days trying it with lots of connections and lots more problems.  The file DSN was the only thing that worked properly.

Just had a look at tek-tips and I had tried that way too and still had problems, but the above solution works well if you don't mind having file DSNs lying about.

Regards,
Paul.
0
 

Expert Comment

by:phil157
ID: 10886547
Thank you stasau

Your code:

myreport.Database.Tables.Item(i).Location = myreport.Database.Tables.Item(i).Location

worked a treat!  Should someone let Crystal know how to use their software....?!

Phil
0
 

Expert Comment

by:Choice-Quote
ID: 12027034
Followed the instructions to the best of my knowledge, now getting the following error;
Server Error in '/reporting' Application.
--------------------------------------------------------------------------------

Error in File C:\Inetpub\wwwroot\reporting\qstats.rpt: Invalid table number.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: CrystalDecisions.CrystalReports.Engine.InvalidArgumentException: Error in File C:\Inetpub\wwwroot\reporting\qstats.rpt: Invalid table number.

Source Error:


Line 52:             logOnInfo.ConnectionInfo.Password = "passwd1"
Line 53:             crTable.ApplyLogOnInfo(logOnInfo)
Line 54:             crTable.Location = strLoc
Line 55:         Next
Line 56:
 

Source File: C:\Inetpub\wwwroot\reporting\WebForm1.aspx.vb    Line: 54

Stack Trace:


[InvalidArgumentException: Error in File C:\Inetpub\wwwroot\reporting\qstats.rpt:
Invalid table number.]
   .I(String , EngineExceptionErrorID ) +406
   .D(Int16 , Int32 ) +537
   .C(Int16 )
   CrystalDecisions.CrystalReports.Engine.Table.(String c)
   CrystalDecisions.CrystalReports.Engine.Table.set_Location(String value)
   reporting.WebForm1.Button1_Click(Object sender, EventArgs e) in C:\Inetpub\wwwroot\reporting\WebForm1.aspx.vb:54
   System.Web.UI.WebControls.Button.OnClick(EventArgs e)
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
   System.Web.UI.Page.ProcessRequestMain() +1277

 


--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573




This is my VB code;


Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared

Public Class WebForm1
    Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

    End Sub
    Protected WithEvents Label1 As System.Web.UI.WebControls.Label
    Protected WithEvents Button1 As System.Web.UI.WebControls.Button
    Protected WithEvents CRV1 As CrystalDecisions.Web.CrystalReportViewer

    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object

    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub

#End Region

    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here

    End Sub


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim myreport As New ReportDocument
        Dim logOnInfo As New TableLogOnInfo
        Dim crTable As Table

        myreport.Load(Server.MapPath("qstats.rpt"))

        myreport.SetDatabaseLogon("C:\Inetpub\wwwroot\reporting\electra.dsn", "", "odbc", "passwd1")

        ' Loop through every table in the report.
        For Each crTable In myreport.Database.Tables
            Dim strLoc As String
            strLoc = crTable.Location
            strLoc = "dbname" & Right(strLoc, Len(strLoc) - InStr(strLoc, ".") + 1)
            logOnInfo.ConnectionInfo.ServerName = "C:\Inetpub\wwwroot\reporting\electra.dsn"
            logOnInfo.ConnectionInfo.DatabaseName = "castle"
            logOnInfo.ConnectionInfo.UserID = "odbc"
            logOnInfo.ConnectionInfo.Password = "passwd1"
            crTable.ApplyLogOnInfo(logOnInfo)
            crTable.Location = strLoc
        Next

        myreport.Refresh()
        CRV1.ReportSource = myreport
        CRV1.Visible = True

    End Sub
End Class


This is my HTML code;

<%@ Register TagPrefix="cr" Namespace="CrystalDecisions.Web" Assembly="CrystalDecisions.Web, Version=9.1.5000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304" %>
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="WebForm1.aspx.vb" Inherits="reporting.WebForm1"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
      <HEAD>
            <title>WebForm1</title>
            <meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
            <meta content="Visual Basic .NET 7.1" name="CODE_LANGUAGE">
            <meta content="JavaScript" name="vs_defaultClientScript">
            <meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
      </HEAD>
      <body MS_POSITIONING="GridLayout">
            <form id="Form1" method="post" runat="server">
                  <cr:crystalreportviewer id="CRV1" style="Z-INDEX: 101; LEFT: 16px; POSITION: absolute; TOP: 120px" runat="server"
                        Width="800px" Height="1200px" HasRefreshButton="True" HasPageNavigationButtons="False" HasGotoPageButton="False"
                        HasDrillUpButton="False" HasSearchButton="False" EnableDrillDown="False" PageToTreeRatio="10" DisplayGroupTree="False"
                        BorderWidth="1px" BorderStyle="Solid" BorderColor="Black" BestFitPage="False"></cr:crystalreportviewer>
                  
                  <asp:Button id="Button1" style="Z-INDEX: 103; LEFT: 160px; POSITION: absolute; TOP: 40px" runat="server"
                        Text="Button"></asp:Button></form>
      </body>
</HTML>

0
 
LVL 2

Author Comment

by:pmcgivern
ID: 12034193
Looks like it could be a problem with the report itself.  Try recreating the report and try it again - or try creating a simple test report to see if the code works ok.  It could also be a version problem between the version of crystal the report was created in and the version used by visual studio.  Your code looks ok though.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now