Passing multiple parameter values into TableAdapter

[TRA]

Good morning.

ASP.Net front end, telerik reporting, MS SQL 2008 Database.

I'm trying to pass in multiple pkeys as parameter into a telerik report tableadapter.  First, I tried passing them as integers, then I converted the integer keys to a string.  One integer works fine, one string item works fine, but when I try to pass in more than one, the report displays a blank page.  I tried a system array, blank page.  I tried an arraylist, blank page.

I would prefer to pass in the integers (not having to convert them) but at this point, I'll try anything.

First, the code from the app, second, the code from the report:

 ' run through the XML document passed by the client and post all of the
        ' "checked" rows
        inputXML = New XmlDocument
        inputXML.LoadXml(Server.HtmlDecode(DETAILS_XML.Value))
        rowNodes = inputXML.SelectNodes("ROOT/Receipt")
        postCount = 0
        confirmedKeys = New StringBuilder("")
        'rKeyStr = CStr(confirmedKeys.ToString)
        For Each rowNode In rowNodes
            If rowNode("Print").InnerText = "Y" Then
                postCount += 1
                If confirmedKeys.Length > 0 Then
                    confirmedKeys.Append(",")
                    rKeyStr = rKeyStr & ","
                End If
                confirmedKeys.Append(rowNode("PKey").InnerText)
                rKeyStr = rKeyStr & rowNode("PKey").InnerText
            End If
            '
        Next
        '
        '
        sKey = Session(SESSION_SKEY)
        '
        '
        btnPost.Visible = True
        '
        '**********************************************************************************
        rvReceipt.Report = New SCOReports.Receipt(sKey, rKeyStr)
        rvReceipt.RefreshReport()
        rvReceipt.Visible = True
        '**********************************************************************************

'  REPORT CODE IS NEXT....

Public Sub New(ByVal sk, ByVal rKeys)
        InitializeComponent()

        'TODO: This line of code loads data into the 'DatReceipt.datReceiptTable' table. You can move, or remove it, as needed.
        Try
            Me.DatReceiptTableAdapter1.Fill(Me.DatReceipt.datReceiptTable, sk, rKeys)
        Catch ex As System.Exception
            'An error has occurred while filling the data set. Please check the exception for more information.
            System.Diagnostics.Debug.WriteLine(ex.Message)
        End Try


        'TODO: This line of code loads data into the 'DatRecDet1.DatRecDetTable' table. You can move, or remove it, as needed.
        Try
            Me.DatRecDetTableAdapter2.Fill(Me.DatRecDet1.DatRecDetTable)
        Catch ex As System.Exception
            'An error has occurred while filling the data set. Please check the exception for more information.
            System.Diagnostics.Debug.WriteLine(ex.Message)
        End Try
    End Sub
End Class

'  THE SQL FOR THE TABLE/TABLEADAPTER...
SELECT     RM.PKEY, RM.SKEY, RM.REC_NBR, RM.REC_DATE, RM.REC_CASH, RM.REC_CHECK, RM.REC_CREDIT, RM.AMOUNT, RM.RECEIVEDBY,
                      RM.CHKREF, S.LNAME + ', ' + S.FNAME + ' ' + ISNULL(S.MNAME, '') AS STUDNAME, ISNULL(P.FNAME, '') + ' ' + ISNULL(P.LNAME, '') AS PNAME,
                      SCH.NAME AS SCH_NAME, D.TAXID, RM.PKSTR
FROM         RECEIPTMASTER AS RM INNER JOIN
                      STUDENT AS S ON RM.STUDKEY = S.PKEY LEFT OUTER JOIN
                      PARENT AS P ON S.PRI_PGKEY = P.PKEY INNER JOIN
                      SCHOOLS AS SCH ON RM.SKEY = SCH.PKEY INNER JOIN
                      DISTRICT AS D ON SCH.DISTRICTKEY = D.PKEY
WHERE     (RM.STATUS = 'C') AND (RM.PEND_REC_NBR = 'PRINT') AND (RM.SKEY = @SKEY) AND (RM.PKSTR IN (@Keys))
ORDER BY RM.REC_NBR

Thanks in advance for taking a look.

LEBREW

[Tony]

Hi Lebrew, create a function in SQL server that will take a comma delimted varchar variable, in your case "@KEYS"  and returns the results in a table.   I tried something very similar and found this technique to work the best.  After googleing for a bit I found this SQL script that took a comma delimted string and returned the results in a datatable. The function is listed below

FUNCTION [dbo].[SplitValues_Integer]
(
    @List varchar(500)
)
RETURNS
@ParsedList table
(
    ID int   --Use Appropriate conversion
)
AS
BEGIN
    DECLARE @Key varchar(10), @Pos int

    SET @List = LTRIM(RTRIM(@List))+ ','
    SET @Pos = CHARINDEX(',', @List, 1)

    IF REPLACE(@List, ',', '') <> ''
    BEGIN
        WHILE @Pos > 0
        BEGIN
            SET @Key = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
            IF @Key <> ''
            BEGIN
                INSERT INTO @ParsedList (ID)
                VALUES (CAST(@Key AS int)) --Use Appropriate conversion
            END
            SET @List = RIGHT(@List, LEN(@List) - @Pos)
            SET @Pos = CHARINDEX(',', @List, 1)

        END
    END
    RETURN
END

Once you create this function, then the next thing you need to do is use it in the following line:
 RM.PKSTR IN (Select ID From SplitValues_Integer(@KEYS))

Your function needs to return the same datatype you are comparing.  In this exampe the function is returning one column of datatype integer.  If you need to compare other datatypes, re-use the function and change the datatype to the desired one where appropriate. 

This should do the trick

[TRA]

Hey Tony.

Please forgive me for the late reply.  This worked great and I'm very thankful to you for the post.  I changed a few things of course, but you got me going in the right direction.

Have a great day!

LEBREW

[Randa]

Using Q2 2011 
 
I am trying to use this function in a select statement or a stored procedure  of the report parameter
Both of them work well in execute query but when I run the report the drop down list is empty. 

SELECT        StatusID, Status FROM  tableStatus
WHERE        (StatusID IN (SELECT ID
                               FROM dbo.fnSplit_Values_Integer(@StatusID) AS fnSplit_Values_Integer_1))

I have a default value of '9999' for @StatusID  to display all values so I had the stored procedure :

@StatusID

varchar(max)  

 

if (@StatusID = '9999')  

 

BEGIN  

 

SELECT StatusID, Status FROM tableStatus 

END  

 

ELSE 

 

BEGIN  

 

SELECT StatusID, Status  

 

FROM tableStatus  

WHERE (StatusID IN (SELECT ID FROM dbo.fnSplit_Values_Integer(@StatusID)))  

 

END 
When I run the report it has blanks and I can not choose any values :
Here is the report parameter :

reportParameter3.AvailableValues.DataSource =

this.sqlDataSource4;  

reportParameter3.AvailableValues.DisplayMember =  "= Fields.Status";  

reportParameter3.AvailableValues.ValueMember =  "= Fields.StatusID";  

reportParameter3.MultiValue = true;  

reportParameter3.Name = "StatusID";  

reportParameter3.Text = "StatusID";  

reportParameter3.Value = "9999";  

reportParameter3.Visible = true; 

 

 

// sqlDataSource4  

 

// 

 

this.sqlDataSource4.Name = "sqlDataSource4";  

this.sqlDataSource4.Parameters.AddRange(new Telerik.Reporting.SqlDataSourceParameter[] {  

new Telerik.Reporting.SqlDataSourceParameter("@StatusID", System.Data.DbType.String, "=Parameters.StatusID.Value")});  

this.sqlDataSource4.SelectCommand = "c_SP_ReportAllStatus";  

this.sqlDataSource4.SelectCommandType = Telerik.Reporting.SqlDataSourceCommandType.StoredProcedure; 

 

Can someone help me figure out what am I missing?