VS 2008 - Crystal report error on Connection to IIS Server

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

cin_champAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
You cannot save the logon informationin the report.

You can pass the logon information through the application.

mlmcc
0
cin_champAuthor Commented:
Can you give me an example?
0
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

cin_champAuthor Commented:
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
cin_champAuthor Commented:
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
cin_champAuthor Commented:
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
mlmccCommented:
Are you trying to use the SP from Crystal or are you trying to pass a dataset to Crystal?

mlmcc
0
cin_champAuthor Commented:
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
mlmccCommented:
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
cin_champAuthor Commented:
Database permission all set
0
cin_champAuthor Commented:
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
mlmccCommented:
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
cin_champAuthor Commented:
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
mlmccCommented:
How did you install it on the server?
Did you install the runtimes on the server?

mlmcc
0
cin_champAuthor Commented:
What exactly do I need to install on the server?
Thanks
Cin
0
mlmccCommented:
Check this link

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

mlmcc
0
cin_champAuthor Commented:
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
mlmccCommented:
Are you exporting through the viewer or just through code?

mlmcc
0
cin_champAuthor Commented:
through the viewer, should I be doing it in code instead?
Cin
0
mlmccCommented:
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
cin_champAuthor Commented:
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
mlmccCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cin_champAuthor Commented:
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
cin_champAuthor Commented:
Nothing like giudance to figure it out yourself just took a little longerthan I wanted...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.