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...
Ben SantiardoAsked:
Who is Participating?
 
Ben SantiardoConnect With a Mentor Author 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
 
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.