[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2004-11-10
6
Medium Priority
?
1,389 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:denam
  • 3
  • 3
6 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1000 total points
ID: 12551024
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12551050
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
 
LVL 1

Author Comment

by:denam
ID: 12556131
Excellent!  Thanks
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 1

Author Comment

by:denam
ID: 12556146
I'm not sure I see how no count fixed it?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12560269
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
 
LVL 1

Author Comment

by:denam
ID: 12560278
Thanks again
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question