Solved

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

Posted on 2009-04-07
6
1,724 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 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