Solved

Return Multiple Tables Into A DataSet From A Stored Procedure...?

Posted on 2009-04-07
6
1,763 Views
Last Modified: 2012-05-06
I am trying to automate the creation of a Database and all it's parts for my application, through code. In one instance I need to examine information returned from the SP_HELPLOGINS stored procedure.  In Query Analyzer, I get two tables returned as it should be.  When I run the same code in VB and fill a DataSet using a DataAdapter, it only returns the 1st of the two tables.  I need access to the second table.  I could have sworn I had this working before in a previous application (and yes I am reusing the same class)...

When I had written this class initially years ago, we were using SQL 2000.  This new application I am creating uses SQL 2005.  Is there a different behavior between Servers? Is there something I need to add to my code to get my DataSet to populate BOTH tables into it from SQL 2005?

Thank you in advance for any help you can offer...
0
Comment
Question by:Ben Santiardo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 14

Expert Comment

by:GiftsonDJohn
ID: 24090512
Hi,

If your stored procedure returns 2 tables from sql server then definitely the DataSet will have both the tables in it after getting filled.

To access the second table you have to use

GridView1.DataSource=DataSet1.Tables[1];
GridView1.DataBind();

You can verify this by placing a breakpoint in the code and view the dataset in DataSet visualizer.
0
 
LVL 4

Expert Comment

by:y0usuf
ID: 24090519
you need to index the tables collection in dataset.
dataset.tables(0) for the first table
dataset.tables(1) for second table
0
 

Author Comment

by:Ben Santiardo
ID: 24090556
Fir let me thank you for answering my question.
Unfortunately my problem has nothing to do with Indexing (I wish it were that simple)...  The DataSet itself once filled, only contains one(1) table in it, not two(2).  
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 4

Expert Comment

by:y0usuf
ID: 24090623
I do this all the time. And when I fill my dataset I get two tables in it if the SQL query returns two tables. I don't think you need to do anything different for SQL server 2005. I am using SQL server 2005 as my database. Are you sure your query is running two tables. This is kinda strange to me.

    sqlConn.Open()
            cmd = New SqlCommand(SQLString, sqlConn)
            cmd.CommandTimeout = 0
            da.SelectCommand = cmd
            da.Fill(ds)

Open in new window

0
 
LVL 14

Expert Comment

by:GiftsonDJohn
ID: 24091005
Could you please tell me how you came to know that only one table gets filled in DataSet? Did you try debugging the code and view the dataset with dataset visualizer?
0
 

Accepted Solution

by:
Ben Santiardo earned 0 total points
ID: 24091082
Yes, through the debugger I checked through the visualizer, and also through manually sifting through the DataSet properties using the watch window.  This is why I say I know it's returning only one(1) table.  
I am also tracking down another avenue...  I am using a Derived DataSet & DataTable in my application in order to encapsulate and enhance the interactions between DataSet to DataTable to DataRow. The problem might be in the Derived Class itself...  I am thinking the way the Derived Class was created wasn't 100% correct and left some gaps in how data linked between DataSet -> DataTable -> DataRow...  Unfortunately, until I gain some more information on how to "properly" inherit a DataTable and all it's constituent parts, I cannot say for sure whether this is my error or not.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

623 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