Item cannot be found in the collection corresponding to the requested name or ordinal.

Hey Experts,

Here is the stored proc

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

-- =============================================
-- scalar function (FN) takes a comma seperated list and returns a
-- string varchar(500) of the corresponding responses.
-- ===================================================
ALTER  procedure LP_Sum_D2DResponsesFromCOMMAlist
(@SEQS_COMMA varchar(50)/*,
 @RespString varchar(500)*/)
AS

print '@D2DrespList'
print @SEQS_COMMA

DECLARE @SEQ int, @D2DrespString varchar(500), @comma varchar(1)

SET @SEQ = -1
SET  @D2DrespString = ''

WHILE (CHARINDEX(',', @SEQS_COMMA, 0)>0)
            BEGIN
                  SET @SEQ = (LEFT(@SEQS_COMMA, CHARINDEX(',', @SEQS_COMMA)-1))
                  SET  @D2DrespString = @D2DrespString + (SELECT RESPONSE_NAME FROM RT_RESPONSE WHERE RESPONSE_SEQ = @SEQ)+', '
                  SET @SEQS_COMMA = LTRIM(RTRIM(RIGHT(@SEQS_COMMA, (LEN(@SEQS_COMMA)-(CHARINDEX(',', @SEQS_COMMA))) )))
            END

SET  @D2DrespString = @D2DrespString + (SELECT RESPONSE_NAME FROM RT_RESPONSE WHERE RESPONSE_SEQ = @SEQS_COMMA)

SELECT @D2DrespString  AS D2dRespString

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO



running this in query analyzer

LP_Sum_D2DResponsesFromCOMMAlist @SEQS_COMMA = '4,7,11'
returns the correct values

d2drespstring
----------------
string1, string2, string3


The ASP Page Code:

dim rs2, cmd

'LP_Sum_D2DResponsesFromCOMMAlist
'@SEQS_COMMA varchar(50)
set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = MM_ICPDSN_STRING
cmd.CommandText = "dbo.LP_Sum_D2DResponsesFromCOMMAlist"
cmd.CommandType = 4
cmd.CommandTimeout = 0
cmd.Prepared = true
cmd.Parameters.Append cmd.CreateParameter("@SEQS_COMMA", adVarchar, adParamInput, 50, "7,4,11")
set rs2 = cmd.Execute

response.write rs2("D2DrespString")


returns an error
ADODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/test2.asp, line 21

line 21 is the response.write line

what's wrong?

tia
LVL 1
denamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
>>what's wrong?<<
Add SET NOCOUNT ON at the very top of your stored procedure, as in:

ALTER  procedure LP_Sum_D2DResponsesFromCOMMAlist
(@SEQS_COMMA varchar(50)/*,
 @RespString varchar(500)*/)
AS

SET NOCOUNT ON
print '@D2DrespList'
print @SEQS_COMMA

DECLARE @SEQ int, @D2DrespString varchar(500), @comma varchar(1)

SET @SEQ = -1
SET  @D2DrespString = ''

WHILE (CHARINDEX(',', @SEQS_COMMA, 0)>0)
          BEGIN
               SET @SEQ = (LEFT(@SEQS_COMMA, CHARINDEX(',', @SEQS_COMMA)-1))
               SET  @D2DrespString = @D2DrespString + (SELECT RESPONSE_NAME FROM RT_RESPONSE WHERE RESPONSE_SEQ = @SEQ)+', '
               SET @SEQS_COMMA = LTRIM(RTRIM(RIGHT(@SEQS_COMMA, (LEN(@SEQS_COMMA)-(CHARINDEX(',', @SEQS_COMMA))) )))
          END

SET  @D2DrespString = @D2DrespString + (SELECT RESPONSE_NAME FROM RT_RESPONSE WHERE RESPONSE_SEQ = @SEQS_COMMA)

SELECT @D2DrespString  AS D2dRespString

A better way would be to modify it so that  @D2DrespString is an output parameter and change your ASP code appropriately.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
As an aside there is a typo in your values:

LP_Sum_D2DResponsesFromCOMMAlist @SEQS_COMMA = '4,7,11'
...
cmd.Parameters.Append cmd.CreateParameter("@SEQS_COMMA", adVarchar, adParamInput, 50, "7,4,11")
0
denamAuthor Commented:
Excellent!  Thanks
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

denamAuthor Commented:
I'm not sure I see how no count fixed it?
0
Anthony PerkinsCommented:
From BOL:
<quote>
SET NOCOUNT ON eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. When using the utilities provided with Microsoft® SQL Server™ to execute queries, the results prevent "nn rows affected" from being displayed at the end Transact-SQL statements such as SELECT, INSERT, UPDATE, and DELETE.
</quote>

Your recordset in your initial code was reading one of these output messages, as opposed to the subsequent resultset.
0
denamAuthor Commented:
Thanks again
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.