Solved

VS 2008 - Crystal report error on Connection to IIS Server

Posted on 2011-09-22
25
734 Views
Last Modified: 2013-11-07
Dear Experts,

I have an ASP application (VS 2008) that calls a Crystal report  using a CrystalReportViewer (vs 10.5).  Using IIS 6.

In creating my crystal report I used an ODBC (RDO) connection. Locally the application runs no prob. When I tried to execute it from the server I get this error:
Logon failed. Details: 28000:[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. Error in File C:\WINDOWS\TEMP\WEBproject{BF79ACFD-CF43-43EC-9484-F12A92657746}.rpt: Unable to connect: incorrect log on parameters.

How do I pass in a username and password inside the crystal report.  Can I somehow use my DATAConnectionString in my web config file?


Thanks

0
Comment
Question by:cin_champ
  • 14
  • 11
25 Comments
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
You cannot save the logon informationin the report.

You can pass the logon information through the application.

mlmcc
0
 

Author Comment

by:cin_champ
Comment Utility
Can you give me an example?
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
0
 

Author Comment

by:cin_champ
Comment Utility
It's not working for me because I also have parameters. I'm hoping to be able to work on it today and
I'll post my code later
Thanks
Cin
0
 

Author Comment

by:cin_champ
Comment Utility
Mlmcc,

Actually having a hard time figuring out how to use a  SqlConnection and passing the stored proc parameter in code to the report in ASP.net (VS 2008).

Do you have a better example?
Thanks
Cin

0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
0
 

Author Comment

by:cin_champ
Comment Utility
No I don't think so...
Can you explain how the process works?I'm missing the links conceptually and code wise ...

Here's what I have:

1 - Parameters on it's own works works with this report  that calls a Stored Proc.
Public Sub SetCurrentValuesForParameterField()
        Dim f1 As ParameterField = myCrystalReportViewer.ParameterFieldInfo.Item(0)
        Dim f2 As ParameterField = myCrystalReportViewer.ParameterFieldInfo(1)
        Dim f3 As ParameterField = myCrystalReportViewer.ParameterFieldInfo(2)
        Dim v1 As New ParameterDiscreteValue()
        v1.Value = Session("memberID").ToString
        Dim v2 As New ParameterDiscreteValue()
        v2.Value = stdate.SelectedDate
        Dim v3 As New ParameterDiscreteValue()
        v3.Value = enddate.SelectedDate
        f1.CurrentValues.Add(v1)
        f2.CurrentValues.Add(v2)
        f3.CurrentValues.Add(v3)
    End Sub

2- Here's how I can get data
Friend Function getMemSPAs(ByVal cusno As String, ByVal startDt As Date, ByVal EndDt As Date) As DataSet
        Dim ConnectionString As String = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("DATAConnectionString").ConnectionString
        Dim con As New Data.SqlClient.SqlConnection(ConnectionString)
        Dim da As SqlDataAdapter
        Dim cmd As New SqlCommand
        Dim dt As New DataSet
     
        Dim params() As SqlParameter = New SqlParameter(1) {}
        params(0) = New SqlParameter("@fied1", Trim(fied1))
        params(1) = New SqlParameter("@fied2", Trim(fied2))
        params(2) = New SqlParameter("@fied3", Trim(fied3))

        With cmd
            .Connection = con
            .CommandType = CommandType.StoredProcedure
            .CommandText = "usp_getmyRPTData"

            ' For i = 0 To 1
            .Parameters.Add(params(0))
            .Parameters.Add(params(1))
            .Parameters.Add(params(2))
            ' Next

        End With

        da = New SqlDataAdapter(cmd)
        da.Fill(dt)

        con.Close()
        con = Nothing
        cmd = Nothing
        params = Nothing
        da = Nothing

        Return dt


    End Function



What I'm not getting is:
 1) how do I link the Report to the data connection
(from my Web Config file)...  
 2) In ASP.net how do I use/ pass the SqlConnection info to the report? dataset?that calls the stored proc?
3) how to call it all using the crystal  report viewer?


Thanks
Cin

0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Are you trying to use the SP from Crystal or are you trying to pass a dataset to Crystal?

mlmcc
0
 

Author Comment

by:cin_champ
Comment Utility
Which one can I do? I'm getting that first error on the Server when I try to execute it from the report but (not on my machine).
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
It works on the development machine?

Check what user is actually running the report on the server.  It is probably a system user and you may need to give that user database permissions.

mlmcc
0
 

Author Comment

by:cin_champ
Comment Utility
Database permission all set
0
 

Author Comment

by:cin_champ
Comment Utility
mlmcc,

I finally got it to work,
Here's my final code, it's actually pretty simple...
 Friend Function getMemSPAs(ByVal cusno As String, ByVal startDt As DateTime, ByVal EndDt As DateTime) As DataSet


        Dim ConnectionString As String = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("DATAConnectionString").ConnectionString
        Dim con As New Data.SqlClient.SqlConnection(ConnectionString)
        Dim da As SqlDataAdapter
        Dim cmd As New SqlCommand
        Dim dt As New DataSet
   

        Dim params() As SqlParameter = New SqlParameter(2) {}
        params(0) = New SqlParameter("@CusNo", Trim(cusno))
        params(1) = New SqlParameter("@StDate", Trim(startDt))
        params(2) = New SqlParameter("@EndDate", Trim(EndDt))

        With cmd

            .Connection = con
            .CommandType = CommandType.StoredProcedure
            .CommandText = "webgetreport"

            ' For i = 0 To 1
            .Parameters.Add(params(0))
            .Parameters.Add(params(1))
            .Parameters.Add(params(2))
            ' Next

        End With

        da = New SqlDataAdapter(cmd)
        da.Fill(dt)

        con.Close()
        con = Nothing
        cmd = Nothing
        params = Nothing
        da = Nothing

        Return dt


    End Function

   


    Sub BindReport()

        Dim mydata As New DataSet
        Dim ReportPath As String = Server.MapPath("WebMembPromoRecap.rpt")

        Dim myreport As New ReportDocument

       myreport.Load(ReportPath)
        ' Set the SetDataSource property of the Report to the Dataset
        mydata = getMemSPAs(Session("memberID"), stdate.SelectedDate, enddate.SelectedDate)

        ' Set the SetDataSource property of the Report to the Dataset
        myreport.SetDataSource(mydata.Tables(0))
        ' Set the Crystal Report Viewer's property to the  Report object
        myCrystalReportViewer.ReportSource = myreport
        myCrystalReportViewer.RefreshReport()
       
    End Sub

My only problem now is on the server I can't print or export using the crystal viewer... any thought?
Thanks
Cin
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Does the server have a default printer set?
If not then you need to install one on the server.

Did you install the export dlls on the server?

mlmcc
0
 

Author Comment

by:cin_champ
Comment Utility
NOT sure what are they?

When I run it local I can see the report and it prints/exports no problem. However on the server when I hit export or print the crystal report viewer is empty (the date is there when I run it locally...)and I get the message Failed to export using the options you specified. Please check your options and try again.

Works fine local not on server....
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
How did you install it on the server?
Did you install the runtimes on the server?

mlmcc
0
 

Author Comment

by:cin_champ
Comment Utility
What exactly do I need to install on the server?
Thanks
Cin
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Check this link

Crystal Reports Runtime Packages
http://resources.businessobjects.com/support/additional_downloads/runtime.asp

mlmcc
0
 

Author Comment

by:cin_champ
Comment Utility
Yes, all installed on server and still not working.

The local app. loads the report and keeps the report data on the main page, however on the server when I hit print or export the main page is blank and so is the subsequent pop up box with the message "Failed to export using the options you specified".  Any orther thoughts? Do I have to hard code something in javascript or does crystalreportviewer take care of all export functionality?


Thanks
Cin
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Are you exporting through the viewer or just through code?

mlmcc
0
 

Author Comment

by:cin_champ
Comment Utility
through the viewer, should I be doing it in code instead?
Cin
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Not necessarily.  Using code to do the export means you aren't displaying the report.

Do you handle the postback.  Clicking the print or export button causes a postback which means you have to have saved the parameters and other values in session values so they can be reused.

mlmcc
0
 

Author Comment

by:cin_champ
Comment Utility
Should I open another question for an example on this? and assign you the points for this? don't want to take advantage of all your help...
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
Comment Utility
Let it sit for a few days.  I don't program in .Net or web so I don't know the postback code.

There is a tutorial for VS2005 that might at least point you in the right direction.

http://www.businessobjects.com/global/pdf/dev_zone/VS2005_Walkthroughs.pdf

mlmcc
0
 

Assisted Solution

by:cin_champ
cin_champ earned 0 total points
Comment Utility
Thanks FINALLY GOT IT!!! It's all related to loosing the values on post back....

If Page.IsPostBack = False Then

         
            ' LEt get the global values
            If Session("memberID") Is Nothing Then
                If Right(Request.QueryString("memberID"), 4) = "" Then
                    Response.Redirect("pg_Login.cfm")
                Else
                    memberID = Right(Request.QueryString("memberID"), 4)
                    memberName = getMemberName(memberID)
                    Session("memberID") = memberID
                    Session("memberNAME") = Trim(memberName)

                End If
               

         
        ElseIf (stdate.SelectedDate <> #12:00:00 AM# And enddate.SelectedDate <> #12:00:00 AM#) Then
           
            Session("StarDt") = stdate.SelectedDate
            Session("EndDt") = enddate.SelectedDate
            ConfigureReport()
            myCrystalReportViewer.RefreshReport()
        End If

Thank You for all your guidance and patience!
Cin
0
 

Author Closing Comment

by:cin_champ
Comment Utility
Nothing like giudance to figure it out yourself just took a little longerthan I wanted...
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

762 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

10 Experts available now in Live!

Get 1:1 Help Now