Error when I try to access to a returned recordset

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Error when I try to access to a returned recordset

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2005-04-29 : 04:56:11

I am trying to refill a web form in my ASP.NET VB.NET application with the recordset returned by this SP inside Sql Server:


USE market
GO
ALTER PROCEDURE refill_form_p
@Offer_id bigint
As


SET NOCOUNT ON

        SELECT Certificate_Mana
        FROM CertMana_Offers
        WHERE Offer_num = @Offer_id 
         
SET NOCOUNT OFF

SET NOCOUNT ON

        SELECT CertM_T.Certificate_mana_id, CertM_T.Certificate_mana_name       
        FROM CertMana_Offers As CertM_O
        JOIN Certificates_mana_Type As CertM_T
        On CertM_T.Certificate_mana_id = CertM_O.Certificate_Mana  
        WHERE CertM_O.Offer_num = @Offer_id 
         
SET NOCOUNT OFF

SET NOCOUNT ON

        SELECT (Case When Other_Certificates IS Null THEN Null ELSE '8' END) 
        FROM Offers
        WHERE Offer_id = @Offer_id 
         
SET NOCOUNT OFF
GO

But I receive an exception in the application ‘System.IndexOutOfRangeException’ in the ‘Certificate_mana_name’ recordset, emphasized in bold in the SP. Perhaps is the way I reference this received element in the app.. ‘Certificate_mana_name’. Which is the name of that returned element? CertM_T.Certificate_mana_name or Certificate_mana_name? Or another?

Thank you,
Cesar

nr
SQLTeam MVY

12543 Posts

Posted - 2005-04-29 : 05:14:17

Certificate_mana_name isn't a recordset. It's a column in a recordset.
You have three recordsets retuned from the sp and this is in the second one.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cesark
Posting Yak Master

215 Posts

Posted - 2005-04-29 : 05:52:58

Ok, and how can I access to that column in the second recorset from the application? I mean only the name of the column. Which is the name of the column once in the application?

nr
SQLTeam MVY

12543 Posts

Posted - 2005-04-29 : 06:36:17

You open the second recordset and then reference the column by name or ordinal. The details depend on what you are using.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-04-29 : 07:15:31

YourDataSetName.Tables(1).Rows(0).Item("CertM_T.Certificate_mana_name")

Always go by name for a column. You never know when a nutjob DBA will change the SP on you

cesark
Posting Yak Master

215 Posts

Posted - 2005-04-29 : 09:07:12

I joined the first two select into one, and now works. Before I already reference the column by its name in the app, but it didn' t work, I don' t understand why..


USE market
GO
ALTER PROCEDURE refill_form_p
@Offer_id bigint
As

SET NOCOUNT ON

        SELECT Certificate_Mana, CertM_T.Certificate_mana_id, CertM_T.Certificate_mana_name       
        FROM CertMana_Offers As CertM_O
        JOIN Certificates_mana_Type As CertM_T
        On CertM_T.Certificate_mana_id = CertM_O.Certificate_Mana  
        WHERE CertM_O.Offer_num = @Offer_id 
         
SET NOCOUNT OFF

SET NOCOUNT ON

        SELECT (Case When Other_Certificates IS Null THEN Null ELSE '8' END) 
        FROM Offers
        WHERE Offer_id = @Offer_id 
         
SET NOCOUNT OFF
GO

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources