Question VB Crystal Viewer requires login. Cannot figure it out. Have read posts on this...

[jason@home]

I have a VS 2008 app I am building. I REALLY need your help VB Crystal Reports Experts! Works great but asks for database credentials when Crystal Reports Viewer attempts to launch the report. The stucture is that the app passes parameters to a stored procedure on a MS SQL box which creates a temporary table. Then the CR inside of the CRviewer reports on the temporary table. All works fine except that the user is prompted for DB credentials (information they are not allowed to know), when the CRviewer launches. I have been struggling with this for a week! I have searched and searched the code here and everywhere else but I cannot find something that works or if I do find something, I am affraid to try it because I don't know where it should go? I think maybe it needs to be part of this but I don't know if this is where it belongs or what the code should be.

<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Partial Class RptViewer
    Inherits System.Windows.Forms.Form

    'Form overrides dispose to clean up the component list.
    <System.Diagnostics.DebuggerNonUserCode()> _
    Protected Overrides Sub Dispose(ByVal disposing As Boolean)
        Try
            If disposing AndAlso components IsNot Nothing Then
                components.Dispose()
            End If
        Finally
            MyBase.Dispose(disposing)
        End Try
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    <System.Diagnostics.DebuggerStepThrough()> _
    Private Sub InitializeComponent()
        Me.CrystalReportViewer1 = New CrystalDecisions.Windows.Forms.CrystalReportViewer
        Me.Exp_Auth1 = New AuthApp.Exp_Auth
        Me.SuspendLayout()
        '
        'CrystalReportViewer1
        '
        Me.CrystalReportViewer1.ActiveViewIndex = 0
        Me.CrystalReportViewer1.AutoSize = True
        Me.CrystalReportViewer1.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle
        Me.CrystalReportViewer1.DisplayGroupTree = False
        Me.CrystalReportViewer1.Dock = System.Windows.Forms.DockStyle.Fill
        Me.CrystalReportViewer1.Location = New System.Drawing.Point(0, 0)
        Me.CrystalReportViewer1.Name = "CrystalReportViewer1"
        Me.CrystalReportViewer1.ReportSource = Me.Exp_Auth1
        Me.CrystalReportViewer1.Size = New System.Drawing.Size(1034, 1054)
        Me.CrystalReportViewer1.TabIndex = 0
        Me.SetDatabaseLogon("pwhcprod01", "Pwhcprod01")
        '
        'RptViewer
        '
        Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
        Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
        Me.ClientSize = New System.Drawing.Size(1034, 1054)
        Me.Controls.Add(Me.CrystalReportViewer1)
        Me.Name = "RptViewer"
        Me.Text = "RptViewer"
        Me.ResumeLayout(False)
        Me.PerformLayout()

    End Sub
    Friend WithEvents CrystalReportViewer1 As CrystalDecisions.Windows.Forms.CrystalReportViewer
    Friend WithEvents Exp_Auth1 As AuthApp.Exp_Auth
End Class

ALL help is very much appreciated. I am a newbie but I have built a great app that fully meets some complex business requirements. All I need now is to get past this one last thing!!!

 

[IanRyder]

Hi,

I used to get credential issues all the time when I first got started with Crystal Reports. I am sure there are other ways to do this but I got round this by defining my own connection objects and then passing the resulting dataset to the Crystal Report at run time. See below a typical routine I call when Creating Crystal Reports. It takes various parameters but you should be able to work out how and where the parameters are used to construct the basis of the report.

Private Sub ViewReport(ByVal crReportName As ReportDocument, ByVal strQueryString As String, ByVal strDataSourceObjectName As String, Optional Params() As SqlParameter = Nothing)
  Dim myConnection As SqlConnection
  Dim myCommand As New SqlCommand
  Dim myDataSource As New DataSet
  Dim myDataAdapter As New SqlDataAdapter
 
  Try
    myConnection = New SqlConnection(<Add Your Database connection String here with the credentials that you need to use>)
    With myCommand
      .Connection = myConnection
      .CommandText = strQueryString
      .CommandType = CommandType.Text
      .Parameters.Clear()
      If Not IsNothing(Params) Then
        .Parameters.AddRange(Params)
      End If
    End With
    With myDataAdapter
      .SelectCommand = myCommand
      .Fill(myDataSource, strDataSourceObjectName)
     End With
    crReportName.SetDataSource(myDataSource)
    With CrystalReportViewer1
      .ReportSource = crReportName
      .Zoom(2)
    End With
  Catch ex As Exception
  MsgBox("Unexpected Error Occured in Report Creation!" & vbCrLf & vbCrLf & ex.Message, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Unexpected Error Creating Report!")
  End Try
End Sub

Hope that helps.

Cheers,

Ian

 

[jason@home]

No luck yet but more information for you here.

Thanks so much for your quick response Ian. No luck so far. Here is how I have adapted your code so that it does not give errors in my build:

    'New code from the web'
    Private Sub ViewReport(ByVal crReportName As Exp_Auth, ByVal strQueryString As String, ByVal strDataSourceObjectName As String, Optional ByVal Params() As SqlClient.SqlParameter = Nothing)
        Dim myConnection As SqlClient.SqlConnection
        Dim myCommand As New SqlClient.SqlCommand
        Dim myDataSource As New DataSet
        Dim myDataAdapter As New SqlClient.SqlDataAdapter
        Try
            myConnection = New SqlClient.SqlConnection("Dsn=Horizon tempdb;" + "Uid=pwhcprod01;" + "Pwd=Password;")
            With myCommand
                .Connection = myConnection
                .CommandText = strQueryString
                .CommandType = CommandType.Text
                .Parameters.Clear()
                If Not IsNothing(Params) Then
                    .Parameters.AddRange(Params)
                End If
            End With
            With myDataAdapter
                .SelectCommand = myCommand
                .Fill(myDataSource, strDataSourceObjectName)
            End With
            crReportName.SetDataSource(myDataSource)
            With CrystalReportViewer1
                .ReportSource = crReportName
                .Zoom(2)
            End With
        Catch ex As Exception
            MsgBox("Unexpected Error Occured in Report Creation!" & vbCrLf & vbCrLf & ex.Message, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Unexpected Error Creating Report!")
        End Try
    End Sub

But it still keeps on popping up the window that shows:
Server Name: (in this box is listed the DNS name)
Database: tempdb (the database whish contains the table which my Crystal Report pulls from)
Login ID: pwhcprod01
Password: (this is blank and must be entered every time, this being the problem)
Then has the checkbox for Use Integrated Security (which is not an option for my environment)

Here is the code from the form where I am building creating the table via adding parameters from the app to a stored procedure on the server, which creates the temporary table that the Crystal Report is accessing it's data from. Maybe it needs to be modified here intead???

    Private Sub PrepRptBtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PrepRpt.Click
        Me.Cursor = Cursors.WaitCursor
        Dim myCommandSQL As New SqlClient.SqlCommand
        Dim connSql As SqlClient.SqlConnection
        Dim transaction As SqlClient.SqlTransaction
        Dim startVal As Integer = 1
        Dim endVal As Integer = 100
        Dim ParamCounter As Integer
        Dim DaysParam As Integer
        DaysParam = DaysToReportNum.Value
        Dim I As Integer
        I = 0
        ParamCounter = +1
        Try
            connSql = New SqlClient.SqlConnection("Data Source=MHHSSQL01;Initial Catalog=PwHcProd01;User ID=PwHcProd01;Password=Password")
            connSql.Open()
            transaction = connSql.BeginTransaction()
            myCommandSQL.Connection = connSql
            myCommandSQL.Transaction = transaction
            myCommandSQL.CommandType = CommandType.StoredProcedure
            myCommandSQL.CommandText = "Merc_Aut_Svcs_Temp"
            For Each Item As Object In PyrNamStrListBox.SelectedItems
                ParamCounter = ParamCounter + 1
                myCommandSQL.Parameters.Add(Chr(64) & "PyrNam" & ParamCounter, SqlDbType.VarChar, 130).Value = DirectCast(Item, DataRowView)("NamStr").ToString()
                For ParamCount = startVal To endVal
                    If ParamCount = 100 Then
                        Exit For
                    End If
                    Application.DoEvents()
                Next
            Next
            myCommandSQL.Parameters.Add(Chr(64) & "Days", SqlDbType.Int).Value = DaysParam
            myCommandSQL.ExecuteNonQuery()
            transaction.Commit()
            connSql.Close()
            myCommandSQL.Dispose()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        Me.Cursor = Cursors.Default
        MessageBox.Show(DaysToReportNum.Value & " Days processed" & vbCrLf & "For " & PyrNamStrListBox.SelectedItems.Count & " payors" & vbCrLf & "Click " & Chr(34) & "Launch Report" & Chr(34))
        LaunchRpt.Enabled = True
    End Sub
    Private Sub LaunchRptBtn_Click() Handles LaunchRpt.Click
        Dim ViewerForm As New Form
        ViewerForm = RptViewer
        ViewerForm.Show()
        Dim myCommandSQL As New SqlClient.SqlCommand
        Dim connSql As SqlClient.SqlConnection
        If RptComboBox.Text = "Expiring Authorziations" Then
            connSql = New SqlClient.SqlConnection("Data Source=MHHSSQL01;Initial Catalog=tempdb;User ID=PwHcProd01;Password=Password")
            connSql.Close()
            myCommandSQL.Dispose()
        End If
    End Sub

Heeeeelp!

 

[IanRyder]

Hi,

I have had another quick look at this and what seems strange is the connection string that you have added to the code that I posted as an example:-

myConnection = New SqlClient.SqlConnection("Dsn=Horizon tempdb;" + "Uid=pwhcprod01;" + "Pwd=Password;")

This does not look like a valid connection string for a few reasons here, i.e "dsn?"

A valid connection string for non-integrated security should follow the form:-

Dim sqlConn As New SqlConnection("Data Source=IANVAIO\SQLEXPRESS;Initial Catalog=NORTHWIND;User ID=sa;Password=xxxxxxxx;")

You do however seem to use a valid connection string in your other code examples above so I get the feeling that your issue is still at the point of showing the report.

Hope that helps.

Cheers,

Ian

 

[jason@home]

Sorry that I had the connection string that way in the sample I passed. I was acutally messing with the connection string based upon code I aquired from connectionstrings.com my connections are the same as you suggest when I run it with the bad result. I worry that my mistake is in something more basic than something complex. Maybe I am doing or not doing something in the location if belongs. Like not even in the form it belongs in. I don't know if I was even attempting correctly because to begin I do not know where the credential code needs to go. Does it go into the code for the CR viewer page? Does it go in both? Do I use a data adapter? If so, am I doing it correctly. I have one version that uses a data adapter and it simply comes up blank. The other version where I do not is the one that does produce the report correctly but asks for the credentials that the user is not allowed to know. Does it go into the code for the main application that is launching the CR viewer? Does the fact that my CR is run off of a temporary table which is created by the main application and then reported upon by the CR change the situation? This works correctly. I have all the parameters passing to the SP and all the data is coming into the the temp table in the tempdb. But when the CR viewer is launched as another form from the main application; it asks for the database credentials at this time. Everything I have done to attempt to embed these credentials has either not changed the situation and all runs the same. Or it causes the report to come up blank even though the data is all in the table when I look at the table in MS SQL. I would love it if you could tell me where to put the code and if you see any basic flaws in what I am doing. Sorry for being such a newbie. I am learning quick! Thanks Ian.

 

[IanRyder]

Hi,

It sounds like you are going to have to break down your whole routine to pinpoint exactly which section of your code is causing the issue.

If you still think it's the report itself causing the issue then try this. Declare your report as normal and then set:-

crReportName.SetDatabaseLogon("YourUserName", "YourPassword")

I have not had the need to use this myself but it may help.

Cheers,

Ian

 

[jason@home]

I think I may be simply messing up when specifying the datasource location in the Crystal Report. I have added a System DSN and that is what I am linking to when I set me source location. Is there a better way to do this so that it keeps the credentials? In one of my versions I tried creating a dataset but when I link to that the report just comes up empty even though when I preview the data off the dataset in VS and see the data if I run the SP on the server to create the temp table. It seems like I should be issuing some sort of Fill command in the click event for the button that launches the report viewer but can't find code on this. Any ideas?

 

[IanRyder]

Hi,

I am not really sure where best to help you further? Firstly, you mention "I should be issuing some sort of fill command", If you have a look at my first post I demonstrate how I create my reports using a the fill command of a DataAdapter.

If all else fails, create a separate small application which uses the Northwind database (you can find it everywhere on the net) and then create a small report demonstrating the principals you are using and then post that as a zip file to the forum and I will see if I can see what is going on.

Hope that helps.

Cheers,

Ian

 

[jason@home]

I have tried everything on this. My latest attempt is a dataset in the form of an xsd. The data adapter does not need to be created, it is in my project. Though I don't know if I need to tie it to something else in some way. I create the table which contains the data using one button. That is all working well. Table gets created in the tempdb with the correct data and the parameters are all passed correctly. I am trying to fill the dataset before the viewer launches. The crystal report has it's ource location set to the dataset. But the report just comes up empty. Here is the code I am using to attempt to fill the dataset and launch the report viewer.

[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 
LaunchRptBtn_Click() [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] LaunchRpt.Click[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ViewerForm [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Form
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] DS [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] TempdbDataSet[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] DS_Adapter [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] TempdbDataSetTableAdapters.TempdbTableAdapter[/SIZE]
[SIZE=2]DS_Adapter.GetData()
DS_Adapter.Fill(DS.TempdbTable)
ViewerForm = RptViewer
ViewerForm.Show()
[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2]

[/SIZE]

 

[IanRyder]

Hi,

You are overcomplicating the issue. In your last example all your Adapter and Data components are being declared in the context of the current routine but you then open a new form, which holds your CrystalReportViewer, which cannot see your local component declarations. You also do not set the data source of the CrystalReport and you then do not set the ReportSource of the Viewer.

Have a look at this simple example. Create a new form and add a CrystalReportViewer. Then create a new Crystal Report using the Customers table from the Northwind Database. Then add the code below to the form.

Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
  Dim sqlConn As New SqlConnection("Data Source=IANVAIO\SQLEXPRESS;Initial Catalog=NORTHWIND;Integrated Security=True")
  Dim da As New SqlDataAdapter("Select * from Customers", sqlConn)
  Dim dt As New DataTable
  da.Fill(dt)
  CrystalReport11.SetDataSource(dt)
  With CrystalReportViewer1
    .ReportSource = CrystalReport11
  End With
End Sub

As you can see the Report DataSource and the ReportViewer ReportSource are both set AFTER the creation and loading of the Data objects.

I hope that helps you to finally get your head round this.

Cheers,

Ian