Solved

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

Posted on 2009-04-07
6
1,736 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
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
jquery datatables, asp.net MVC 9 50
TimeZone, day light savings, Sql server, asp.net 6 37
ASP.NET Content Page 3 25
SQL Server Configuration Manager WMI Error 11 16
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.

765 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