Welcome to weblogs.com.pk Sign in | Join | Help

Aziz ur Rahman

Random Thoughts

News

  • The WeatherPixie
    Listed on BlogShares


    Counter : simple hit counter
Passing Parameters to Stored Procedures

Reports that use stored procedures as their data source are no different than reports that use any other data source. When you open the report, it automatically calls the stored procedure, retrieves the data, and populates the report with this data. The difference between using a stored procedure and using a table is that stored procedures accept parameters as input.

When a report is designed to get its data from a stored procedure, Crystal Reports examines the stored procedure to see if it requires parameters. If so, the designer automatically creates a report parameter that corresponds to each parameter in the stored procedure. There is a one-to-one mapping of report parameters to the parameters in a stored procedure. When the report runs, the report engine takes the value of each of these parameters and automatically passes them to the stored procedure.

As you know, the user is always prompted to enter the parameters before the report can execute. Of course, you probably don't want to prompt the user for this information because your application has already done so via the user interface. To prevent this from happening, manually populate the parameter(s) via code with the information the user has already provided.

Caution! Crystal Reports can't connect to stored procedures that have output parameters defined. When attempting to do so, the report returns the error "Failed to open a rowset" and doesn?t display any data.

Listing shows how to use the viewer control to populate the paramter fields that will be passed to the stored procedure.

Private Sub SpWithViewer(ByVal UserId As String, ByVal Password As StringByVal SpParameter As String)    'Logon to the server
    Dim crReport As New CustomerSP()
    Dim crTable As CrystalDecisions.CrystalReports.Engine.Table
    Dim crLogonInfo As New CrystalDecisions.Shared.TableLogOnInfo()
    CrystalReportViewer1.LogOnInfo = New CrystalDecisions.Shared.TableLogOnInfos()
    crLogonInfo.TableName = "spCustomers;1"

    With crLogonInfo.ConnectionInfo
        .ServerName = "(local)"
        .DatabaseName = "Northwind"
        .UserID = UserId
        .Password = Password
    End With

    CrystalReportViewer1.LogOnInfo.Add(crLogonInfo)
    'Create the parameter
    Dim ParameterFields As CrystalDecisions.Shared.ParameterFields
    Dim ParameterField As CrystalDecisions.Shared.ParameterField
    Dim ParameterRangeValue As CrystalDecisions.Shared.ParameterRangeValue
    Dim spValue As CrystalDecisions.Shared.ParameterDiscreteValue

    ParameterFields = New CrystalDecisions.Shared.ParameterFields()
    ParameterField = New CrystalDecisions.Shared.ParameterField()
    ParameterField.ParameterFieldName = "@CustPattern"
    spValue = New CrystalDecisions.Shared.ParameterDiscreteValue()
    spValue.Value = SpParameter
    ParameterField.CurrentValues.Add(spValue)
    ParameterFields.Add(ParameterField)
    CrystalReportViewer1.ParameterFieldInfo = ParameterFields

    'Show the report
    CrystalReportViewer1.ReportSource = crReport

End Sub

The first half logs onto the data source with the appropriate server name, database name, and login credentials. Notice that the TableName property is the name of the stored procedure with ?;1? shown at the end of it. This is how Crystal Reports identifies the stored procedures and you have to remember to include it as part of the table name. The second half of the code creates a new parameter field, adds is to the parameter fields collection and assigns this collection to the report viewer.You can also pass parameter using the ReportDocument object

Tip: If you want to pass a NULL value to a stored procedure parameter, set the parameter's Value property to Nothing in VB.Net and null in C#.

SET NOCOUNT ON

Although this section assumes you already familiar with stored procedures, there is one statement that doesn't get much attention but is very important.

SET NOCOUNT ON

If you are working with simple stored procedures, then the majority of them consist of a SELECT statement followed by a list of tables, fields and a join method. Crystal Reports handles this type of stored procedure fine. Once you start getting into writing more complex stored procedures you will find that you often need to execute multiple SQL statements within one stored procedure. This can happen when you are using temporary tables, and updating data prior to executing the final SELECT statement.

SET NOCOUNT ON
INSERT INTO AuditLog
SELECT * FROM tblSales WHERE

In the above code, the SET NOCOUNT ON statement is commented out. Running this code will generate two output messages for each statement. They will be in the format of ?xx records affected?. This message is passed prior to the records being returned from the stored procedure. This conflicts with what the report is expecting. Thus, it doesn't use the data from the SELECT statement as its resultset. By uncommenting the first line of code, you tell the database server not to report how many records are affected. This eliminates Crystal Reports from incorrectly using these messages as part of the database. Ideally, this statement would be the first statement in every stored procedure.

HTH

 

Posted: Wednesday, July 21, 2004 9:33 AM by aziz
Filed under: ,

Comments

raja said:

HI Aziz
Thanks , above one is working well , How can i user multiple parameter inthis code
Thanking you
raja
# July 22, 2004 1:56 PM
Anonymous comments are disabled