R – Using stored procedure in crystal report 8.5

crystal-reportsparametersstored-proceduresvb6

I have made a new report using Crystal Reports 8.5 (report1) which uses a stored procedure as its data source. The stored procedure has 2 input parameters (@p1 and @p2) and when I enter some test data for @p1 and @p2 within crystal report IDE , every thing is all right. Then, I added the report1 in visual basic 6.0 IDE and added a new form (form1) and a crystal report viewer control on form1. Now please help me: I wanna to show the report1. What codes exactly should I write to show it?How send data user has entered to the stored procedure parameters via application?
I also get this error messsage: the server has not been opened yet"

What's wrong?

Best Answer

Specifying a report

Assumes that SampleReport has been added to your .Net project:

Dim parameter1 As CrystalDecisions.Shared.ParameterField
Dim parameter2 As CrystalDecisions.Shared.ParameterField

With SampleReport
    .SetDatabaseLogon("user", "password", "server", "database")
    'locate first parameter in report
    parameter1 = .ParameterFields.Find("@p1", "")
    'locate second parameter in report
    parameter2 = .ParameterFields.Find("@p2", "")
End With

'create a new discrete-parameter value
Dim stringValue As New CrystalDecisions.Shared.ParameterDiscreteValue()
stringValue.Value = "USA"

'assign it to the parameter's current values collection
parameter1.CurrentValues.Add(stringValue)

'create a new discrete-parameter value
Dim numberValue As New CrystalDecisions.Shared.ParameterDiscreteValue()
numberValue.Value = 100

'assign it to the parameter's current values collection
parameter2.CurrentValues.Add(numberValue)

With Me.CrystalReportViewer1
    .ReportSource = SampleReport
End With

Related Solutions

Sql – Insert results of a stored procedure into a temporary table

You can use OPENROWSET for this. Have a look. I've also included the sp_configure code to enable Ad Hoc Distributed Queries, in case it isn't already enabled.

CREATE PROC getBusinessLineHistory
AS
BEGIN
    SELECT * FROM sys.databases
END
GO

sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC getBusinessLineHistory')

SELECT * FROM #MyTempTable

Sql – Function vs. Stored Procedure in SQL Server

Functions are computed values and cannot perform permanent environmental changes to SQL Server (i.e., no INSERT or UPDATE statements allowed).

A function can be used inline in SQL statements if it returns a scalar value or can be joined upon if it returns a result set.

A point worth noting from comments, which summarize the answer. Thanks to @Sean K Anderson:

Functions follow the computer-science definition in that they MUST return a value and cannot alter the data they receive as parameters (the arguments). Functions are not allowed to change anything, must have at least one parameter, and they must return a value. Stored procs do not have to have a parameter, can change database objects, and do not have to return a value.

Best Answer

Related Solutions

Sql – Insert results of a stored procedure into a temporary table

Sql – Function vs. Stored Procedure in SQL Server

Related Topic