Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am trying to pull results from two different tables in one stored proc and want to select and load one or both into a datagrid (or two if necessary). In the stored proc, I define output for one dataset as @strCall_Person and the other as @strCall_Business. How do I reference these results in ADO.NET? My current code:

    Dim resultsDataSet As New DataTable

    Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter(cmd)
    myDataAdapter.Fill(resultsDataSet)

    Pulls the first dataset but not the second.

    TIA

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post
    It isn't clear to me what you're trying to do. Are the output parameters datasets or strings? What commandtext are you using to fill the dataset?
    Charlotte

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Since posting the question, I actually found and implemented a solution. I just forgot to update my post. Sorry about that!

    But, to be clear on this, the primary situation is use of a stored procedure that returns more than one data set. In my case, the SP just queries 2 different views using the same parameter. I find this performs faster than creating a "master view" combining the two different views, and the separate tables can be manipulated more easily if they are not combined.

    The next question is, 'how to reference the multiple result sets in ADO.NET?' Turns out this is completely easy. You just declare a DataSet and do a fill from the SqlDataAdaptor. Since DataSets can support multiple tables and relations, just specify which table using z-index order:

    DisplayResults.DataSource = resultsDataSet.Tables(0)
    resultsDataSet.Tables(0).Columns.Remove("FIRST_NAM E")
    resultsDataSet.Tables(0).Columns.Remove("MID_NAME" )
    resultsDataSet.Tables(0).Columns.Remove("LAST_NAME ")
    DisplayResults.DataBind()

    In the code snippet above, I have elected to remove some columns in the specific table before binding the dataset.table(0) to a datagrid. since your results are in separate tables, you can call

    DisplayResults0.DataSource = resultsDataSet.Tables(1)

    later on and manipulate the data prior to binding.

    Hope this clarifies things!

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thanks for posting your solution. A dataset can, indeed, contain more than one table, just as a query or view can. It just wasn't entirely clear what you were trying to do with the dataset, so your response clears it up nicely.
    Charlotte

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes - not only can datasets support multiple tables, you can also do joins, insert/updates/deletes on the data, so a dataset is basically an in-memory relational db. I don't know if this is new in ADO.NET, but it is a way to manipulate data outside of the physical db or stored procs...a little scary perhaps.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='steve_skelton13' post='763064' date='03-Mar-2009 06:48']Yes - not only can datasets support multiple tables, you can also do joins, insert/updates/deletes on the data, so a dataset is basically an in-memory relational db. I don't know if this is new in ADO.NET, but it is a way to manipulate data outside of the physical db or stored procs...a little scary perhaps.[/quote]
    In my work, we make use of this all the time, although it gets tricky when you want to actually update the dataset, as you might imagine. You can easily build these rich datasets in code without any stored procedures or views required. I don't honestly recall whether ADO supported something similar because I haven't used it in years.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •