Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1794
  • Last Modified:

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

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
Ben Santiardo
Asked:
Ben Santiardo
  • 2
  • 2
  • 2
1 Solution
 
GiftsonDJohnCommented:
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
 
y0usufCommented:
you need to index the tables collection in dataset.
dataset.tables(0) for the first table
dataset.tables(1) for second table
0
 
Ben SantiardoAuthor Commented:
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
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.

 
y0usufCommented:
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
 
GiftsonDJohnCommented:
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
 
Ben SantiardoAuthor Commented:
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

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.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now