StoredProcedure, parameters not working in Crystal .Net loading

I use Visual Studio 2005, I create some basic stored procedure on my SQL server 2000. Then i create web project and dynamcli create ReportDocument and fill parameter @id with some value

crReportDocument.SetParameterValue(0, 6);
or
crReportDocument.SetParameterValue("@id", 6);

when i load report

crReport.ReportSource = crReportDocument fails with error

Failed to open a rowset. Details: ADO Error Code: 0x Source: Microsoft OLE DB Provider for SQL Server Description: Procedure 'test' expects parameter '@id', which was not supplied. SQL State: 42000 Native Error: Failed to open a rowset. Error in File C:\DOCUME~1\mkv\LOCALS~1\Temp\Report2 {0A5A6D74-BDAF-4062-8789-C804A1B07D07}.rpt: Failed to open a rowset.

If i use view or table, and create parameter on my own passing parameter works ok. So ther must be some problem with stored procedur parameters, mybe with character @, but this is character which must be suplied in sp, and parameter name populate in crystal i than also @id, .....have anybody some solutions for my probelm, i thnik that this must some bug.....

my sp look like

CREATE PROCEDURE [dbo].[test] @id int AS
select *
from test
where id=@id
GO

thanks

MAtej

Answer this question

StoredProcedure, parameters not working in Crystal .Net loading

Jason Li
I realised that visual studion 2005 come with Crystal report ver 10, but i have licenced version of crystal Xi developer. So how can now i use Crystalviewer version Xi, i try to add it to my component list, and when i paste it on my asp net form,everytime i get version 10 anyway that i rag version 11, i realize that problem with stored procedure paramters is in version 10, i try to run it in visual studio 2003 with crystal xi SDL .NET, and it works, is there any SDK .NET for visual studi 2005 for crystal XI, ....
XGamerX
Mark,

Have Business Objects given a timescale on when they will be releasing the update

The workround posted after your reply does not work for my installation.

My difficulty is that I am porting our application to run Windows 2003 (64), Visual Studio 2005 with a live date at the end of January 2006. Part of the functionality is derived from Crystal Reports invoking parameterised stored procedures.

Thanks
Paul
PoulErik
No you cannot add XI components to VS2005. Business Objects will release an update to CR XI that will provide compatibility with Visual Studio 2005.
Patrick Oliveros
I also want to know some release date of this problem. And also how can Bussines object affor that they are selling Crystal XI and you can not ca use it with new visual studio 2005 which come 1 years after the crystal XI was released, comone, i have customers whoare waiting for my answers...
Jeff Owens
The following workaround worked for me:
http://www.codecomments.com/Visual_Basic_Crystal_Reports/message690985.html

I just can't understant how can people "guess" that :).
yza suklan
I found that if i did NOT set user login credentials to the (SQL Server) DB that the parameters would get passed in ok, but it would prompt me to enter user,password etc.

When i provide the login credentials in code, using ConnectionInfo, with the exact same parameters, it fails telling me the procedure expects a parameter which was not supplied.

This is using VS2005 & the built in Crystal stuff.
Shawn Levendusky

Mark Dorey wrote:
No you cannot add XI components to VS2005. Business Objects will release an update to CR XI that will provide compatibility with Visual Studio 2005.
etiennemartin

I eventually got a fix from Business Objects. The code below generates a report to a PDF file and highlights the "key line". The sample is based on th PUBS database.

Stored Procedure

CREATE procedure test @state nvarchar(50) as select * from authors where state=@state

ASP.VB code to generate PDF file

Imports CrystalDecisions.Shared

Imports CrystalDecisions.CrystalReports.Engine

Partial Class ux_onlineNET_Reports_UxReports

Inherits System.Web.UI.Page

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

' This code is the translation code to product PDF crystal reports from Ux-Online. It uses

' .NET code to bypass permissions problems using the older Crystallise code.

' All parmaters are passed in via a redirect from the old Norwegian code to aviod having

' to translate the components

Dim rdCrystalDocument As New ReportDocument

Dim oExO As ExportOptions

Dim oExDo As New DiskFileDestinationOptions

Dim myTable As Table

Dim myLogin As TableLogOnInfo

Dim myParamValue As New ParameterDiscreteValue

Dim myParamValue1 As New ParameterDiscreteValue

Dim myParamValue2 As New ParameterDiscreteValue

Dim PDFname As String, RepNum As String, ReportName As String

Dim AuditSessionID As String

Const PDF_REPORT_PATH As String = "/"

'Load report

AuditSessionID = 0

ReportName = Server.MapPath(PDF_REPORT_PATH) & "\CrystalReport.rpt"

PDFname = "Report-" & AuditSessionID & ".pdf"

Dim param As String = "@state"

Dim value As String = "CA"

Dim dbname As String = "pubs"

rdCrystalDocument.Load(ReportName, OpenReportMethod.OpenReportByTempCopy)

myParamValue.Value = value

rdCrystalDocument.SetParameterValue(param, myParamValue)

' Set database and logon information

For Each myTable In rdCrystalDocument.Database.Tables

myLogin = myTable.LogOnInfo

With myLogin.ConnectionInfo

' The error occurs with this setting. Set the server name to be "." i.e. the local server and the

' file works.

'.ServerName = "."

' Replace the name volvo.ux-connect.com with the DNS name or your server name

' and uncomment the line you will then get the error

'Failed to open a rowset.

'Details: 42000:[Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'test' expects parameter '@state', which was not supplied.Failed to open a rowset.Error in File C:\WINDOWS\TEMP\CrystalReport {FA1D0B63-13C3-4959-91ED-DF9E122E66FE}.rpt:

'Failed to open a rowset.

.ServerName = "volvo.ux-connect.com"

.Password = ""

.UserID = "sa"

.DatabaseName = dbname

End With

myTable.ApplyLogOnInfo(myLogin)

' THIS IS THE IMPORTANT LINE THE NAME OF THE STORED PROCEDURE '

myTable.Location = "test;1"

Next

'Export

oExDo.DiskFileName = Server.MapPath(PDF_REPORT_PATH) & PDFname

oExO = rdCrystalDocument.ExportOptions

oExO.ExportDestinationType = ExportDestinationType.DiskFile

oExO.ExportFormatType = ExportFormatType.PortableDocFormat

oExO.DestinationOptions = oExDo

rdCrystalDocument.Export()

rdCrystalDocument.Close()

Response.Redirect(PDF_REPORT_PATH & PDFname)

End Sub

End Class
StoredProcedure, parameters not working in Crystal .Net loading

Answer this question