asked on

URGENT-- CRystal reports runtime

I developed a bunch of reports using the designer and setting the datasource as ADO.net and a stored proc. I call it using the follwing Used trusted conneection (integrated security) at runtime. Now after I build the vb.net application, how would I change the conection info at the production environment(?)

Any help is appreciated...

Private Sub showreports_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim rptloc As New locreport()
Dim pvlocID As New CrystalDecisions.Shared.ParameterValues()
Dim pdvlocIDrange As New _
CrystalDecisions.Shared.ParameterDiscreteValue()

pdvlocIDrange.Value = fltrloc
pvlocID.Add(pdvlocIDrange)
rptloc.DataDefinition.ParameterFields _
("@whereclause").ApplyCurrentValues(pvlocID)

crviewer.ReportSource = rptloc
End Sub

SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

geoenvi

ASKER

But the article gives a way with a UID and PW. How can you use integrated security?

Mani Pazhana

Ok, Here is what i did:
you can store the connection information in web.config file.
It is easy to change while deploying to production.
----------------------------------------------------------

Step1:

I created Stored procedure at backend SqlServer which handles all my buisness logic and returns final resultsets..

---------------------------------------------------------------------------------------------------------------
Step2:

Created a Typed Dataset like this:

<?xml version="1.0" encoding="utf-8" ?>
<xs:schema id="CTCPhoneListDataset"
targetNamespace="http://tempuri.org/CTCPhoneListDataset.xsd"
elementFormDefault="qualified"
attributeFormDefault="qualified"
xmlns="http://tempuri.org/CTCPhoneListDataset.xsd"
xmlns:mstns="http://tempuri.org/CTCPhoneListDataset.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="CTCPhoneListDataset" msdata:IsDataSet="true">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="CTCPhoneList">
<xs:complexType>
<xs:sequence>
<xs:element name="BureauName" type="xs:string" />
<xs:element name="MemberName" type="xs:string" />
<xs:element name="WorkPhone" type="xs:string" />
<xs:element name="GroupName" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>

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

Note:The element names in the typed dataset above should match with resultset the Stored Proc is returning.

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

Step3:

Created Crystal Report(.rpt) and bound the Typed Dataset as datasource in my report.

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

Step4: Created a aspx Web page with the following function code:

If ddlSelectExportFormatCOG.SelectedValue = "PDF" Then

Call ExportPhoneList(".pdf", CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, "pdf", strGroupNo, strDecisionMakers, strRecoveryExperts, strOperationalStaff, strExecutiveStaff, strEmergencyCertifiedPersonnel, strVendorSupport, strEPLO)

ElseIf ddlSelectExportFormatCOG.SelectedValue = "WORD" Then

Call ExportPhoneList(".doc", CrystalDecisions.Shared.ExportFormatType.WordForWindows, "msword", strGroupNo, strDecisionMakers, strRecoveryExperts, strOperationalStaff, strExecutiveStaff, strEmergencyCertifiedPersonnel, strVendorSupport, strEPLO)

ElseIf ddlSelectExportFormatCOG.SelectedValue = "EXCEL" Then

Call ExportPhoneList(".xls", CrystalDecisions.Shared.ExportFormatType.Excel, "vnd.ms-excel", strGroupNo, strDecisionMakers, strRecoveryExperts, strOperationalStaff, strExecutiveStaff, strEmergencyCertifiedPersonnel, strVendorSupport, strEPLO)

End If

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

Private Function ExportPhoneList(ByVal stFileExtension As String, ByVal expFormat As CrystalDecisions.Shared.ExportFormatType, ByVal stContentTyp As String, ByVal GroupNo As String, ByVal DecisionMakers As String, ByVal RecoveryExperts As String, ByVal OperationalStaff As String, ByVal ExecutiveStaff As String, ByVal EmergencyCertifiedPersonnel As String, ByVal VendorSupport As String, ByVal EPLO As String)

Dim MyDate As String = TimeStamp()

'Check for folder if Does not Exists, Create It in("Mydocuments")
Dim oFile As System.IO.File
Dim oFolder As System.IO.Directory
Dim oWrite As System.IO.StreamWriter

If Not oFolder.Exists(filepath & "\OACallListReportFiles") Then
oFolder.CreateDirectory(filepath & "\OACallListReportFiles")
End If

Dim tempFileName As String
tempFileName = filepath & "\OACallListReportFiles\" & "PhoneList" & MyDate & stFileExtension

Dim dfo As CrystalDecisions.Shared.DiskFileDestinationOptions = New CrystalDecisions.Shared.DiskFileDestinationOptions
dfo.DiskFileName = tempFileName

Dim crCTCPhoneListing As CTCPhoneListing
crCTCPhoneListing = New CTCPhoneListing

Dim eo As CrystalDecisions.Shared.ExportOptions = crCTCPhoneListing.ExportOptions
eo.DestinationOptions = dfo
eo.ExportDestinationType = CrystalDecisions.Shared.ExportDestinationType.DiskFile
eo.ExportFormatType = expFormat

'--------------------------------------------------------------

''Build a connection string
Dim strConnection As String = ConfigurationSettings.AppSettings("ConnectionString")
Dim objConnection As New SqlConnection(strConnection)

Dim sqlString As String
sqlString = "Execute GetCTCPhoneList "
sqlString = sqlString & "'" & Trim(GroupNo) & "',"
sqlString = sqlString & "'" & Trim(DecisionMakers) & "',"
sqlString = sqlString & "'" & Trim(RecoveryExperts) & "',"
sqlString = sqlString & "'" & Trim(OperationalStaff) & "',"
sqlString = sqlString & "'" & Trim(ExecutiveStaff) & "',"
sqlString = sqlString & "'" & Trim(EmergencyCertifiedPersonnel) & "',"
sqlString = sqlString & "'" & Trim(VendorSupport) & "',"
sqlString = sqlString & "'" & Trim(EPLO) & "'"

Dim myTable As CrystalDecisions.CrystalReports.Engine.Table
Dim myLogin As CrystalDecisions.Shared.TableLogOnInfo

For Each myTable In crCTCPhoneListing.Database.Tables
myLogin = myTable.LogOnInfo
myLogin.ConnectionInfo.Password = ConfigurationSettings.AppSettings("DevSQLPassword") '"oacalllists_test%admin"
myLogin.ConnectionInfo.UserID = ConfigurationSettings.AppSettings("DevSQLUserID") '"oacalllists_tst_adm"
myTable.ApplyLogOnInfo(myLogin)
Next

Dim da As SqlDataAdapter
da = New SqlDataAdapter(sqlString, objConnection)

Dim ds As New DataSet
da.Fill(ds, "CTCPhoneList")

crCTCPhoneListing.SetDataSource(ds)

'CleanUp
objConnection.Close()
objConnection = Nothing

crCTCPhoneListing.Export()
Response.ClearContent()
Response.ClearHeaders()
Response.ContentType = String.Format("application/{0}", stContentTyp)

Response.WriteFile(tempFileName)
Response.Flush()
Response.Close()

'System.IO.File.Delete(tempFileName)
End Function

----------------------------------------------------------------------------------------------
If you see my above code, i am getting connection information from web.config file.
no hard coding inside my web page.

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

Hope it helps. Good Luck.

geoenvi

ASKER

I am not using ASP.NET. No web config files here. I am using vb.net. I did try the methods outlined and I am able to login to my dev server fine but moving to any other server retiurns "Unable to open rowset". I know the connection is valid because other parts of the app that use an ADO.net OLEDB are fine.

Is there any way of knowing what the error might be?

ASKER CERTIFIED SOLUTION

Howard Cantrell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike McCracken

Glad i could help

mlmcc