Stored procedure used for Crystal Reports overwrites previous records

Question

I have the following code to call a stored procedure to populate a Crystal Report. The problem is that the last record is the only one returned and is repeated throughout the report. It seems to be overwriting the previous records rather than returning the each record associated with the ID parameter. Am I missing something?

Dim idSQL As String = "SELECT DISTINCT ID FROM table"

drID = objdata.getOLEDBDR(MSCON1, idSQL)

            While drID.Read
                IDall = drID(0)

              'GET DATA******************************
                sqlstr = "SELECT columnname FROM tablename WHERE Id = '" & IDall & "'"

                Dim DAscp As New OleDbDataAdapter(sqlstr, MSCON1)
                dsQRpt.EnforceConstraints = False

                DAscp.Fill(dsQRpt, "tablename")


              'GET DATA FROM SP***********************
                Dim cmd As OleDbCommand = New OleDbCommand()
                cmd.Connection = MSCON
                cmd.CommandType = CommandType.StoredProcedure
                cmd.CommandText = "NAME_OF_SP"
                cmd.Parameters.Add("@month", OleDbType.VarChar, 10, ParameterDirection.Output).Value = QtrMonth
                cmd.Parameters.Add("@year", OleDbType.VarChar, 10, ParameterDirection.Output).Value = cboYear.SelectedValue
                cmd.Parameters.Add("@id", OleDbType.VarChar, 10, ParameterDirection.Output).Value = IDall

          Dim DAms As New OleDbDataAdapter()

            DAms.SelectCommand = cmd

            'DAms.Fill(dsQRpt)
            DAms.Fill(dsQRpt, "SP_NAME")


              'Populate Report*********************************************************************
                QPrpt.Load(Server.MapPath("rptQuarterly.rpt"))
                QPrpt.SetDataSource(dsQRpt)
                crQtrProgress.ReportSource = QPrpt
                QPrpt.SetParameterValue("vPTSID", PTSall)
                crQtrProgress.DataBind()

            End While

Here's the stored procedure:

USE [DB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_NAME]
(
@month varchar(20),
@year varchar (4),
@id varchar (15)
)
AS

DECLARE @tmp table
(
ID nvarchar(10),
CategoryId nvarchar(5),
CategoryName nvarchar(50),
MDate datetime
)


DECLARE @i varchar(10)
DECLARE @sql varchar(1000)


SET @i = 1
DECLARE @j varchar(10)
SET @j = 1


WHILE (@i <=28)
BEGIN
DECLARE @MIDDATE nvarchar(25)
DECLARE @MIDID nvarchar(15)
SET @MIDDATE = 'mh.MID_'+@i+'_DATE'
SET @MIDID = 'mh.MID_'+@i


IF (((@i= 9) OR (@i =12)) AND (@j = 1))
        BEGIN
            --SET @j = 2
            SET @MIDDATE = 'mh.MID_'+@i+'a_DATE'
            SET @MIDID = 'mh.MID_'+@i+'a'
        END  

 IF (((@i= 9) OR (@i = 12)) AND (@j = 2))
        BEGIN

            SET @MIDDATE = 'mh.MID_'+@i+'b_DATE'
            SET @MIDID = 'mh.MID_'+@i+'b'
       END 


SET @sql ='SELECT mh.ID, mc.CategoryId, mc.CategoryName, '+ @MIDDATE +'
FROM MHistory As mh, MCategories As mc WHERE mc.CategoryId = SUBSTRING('''+
@MIDID +''',8,10) AND mh.ID = ''' + @id + ''' AND mh.Hist_Yr = ' + @year + '
AND mh.Hist_Month = ''' + @month + ''' ORDER BY mh.ID'
INSERT INTO @tmp
EXEC (@sql)

IF (((@i= 9) OR (@i=12)) AND (@j = 1))
                  BEGIN
                  IF @i= 9 SET @i = 9
                  IF @i =12 SET @i = 12
                  SET @j = 2
                  END 
  ELSE
                  BEGIN
                    SET @i = @i + 1
                    SET @j = 1
                  END 
 END

----SET @i = @i + 1
SELECT 
ID AS ID,
CategoryId AS CategoryId,
CategoryName AS CategoryName,
MDate AS MDate

from @tmp

Answer 1

It seems the query for the report's data source is always returning a single record since your datasource query is filtering on Id which I believe is the primary key of tablename.

'GET DATA******************************
            sqlstr = "SELECT columnname FROM tablename WHERE Id = '" & IDall & "'"

            Dim DAscp As New OleDbDataAdapter(sqlstr, MSCON1)
            dsQRpt.EnforceConstraints = False

            DAscp.Fill(dsQRpt, "tablename")

Also, I would suggest that you verify if the stored procedure is getting called by the report with correct parameters. You could do this by populating a custom table just before the last SELECT in your stored procedure. In this custom table you could log the values of parameters and anything else you think is relevant to the situation.

You can use following queries to do this logging.

Create custom tables for logging

CREATE TABLE CustomLoggingTable (Parameters varchar(max), RunDate DateTime);
CREATE TABLE CustomResultsTable (ID int, CategoryId int, CategoryName varchar(500),
                                                                         MDate DateTime);

Add the query below as the last part in your procedure ( the last SELECT in this code is the original SELECT you have and you should not change it, but just place the other part before it)

Log your stored procedure

--log procedure parameters
INSERT INTO CustomLoggingTable ( Parameters, RunDate) 
select '@month = ' + isnull(@month,'')  + ', @year = ' + isnull(@year,'') 
                                       + ', @id = ' + isnull( @id,''), getdate() ;

--log result set being returned into a custom table
DELETE from CustomResultsTable;
INSERT into CustomResultsTable (ID, CategoryId, CategoryName, MDate)
SELECT 
ID AS ID,
CategoryId AS CategoryId,
CategoryName AS CategoryName,
MDate AS MDate
from @tmp

--this is your last statement in procedure for returning final result set
SELECT 
ID AS ID,
CategoryId AS CategoryId,
CategoryName AS CategoryName,
MDate AS MDate
from @tmp

After above setup, you can view exactly what happened when your stored procedure was called. The CustomResultsTable will be refreshed every time with the latest result set returned, while CustomLoggingTable will add a new record for each run of the procedure.