Accessing 2nd table in dataview

Question

I have a sqlDataSource that runs a stored procedure that returns a table of the days of the month that was passed in as a parameter, arranged as they would in a calendar. It also returns a second table which contains the event details associated with certain days of the month.

Basically, how would I go about accessing the values contained in the 2nd table that is returned by my query?

I have tried putting the result of the .select in a dataset, unfortunately I get a cast error. If I try to put it in a dataview, I only get the first table.

Any help would be appreciated!

This is what I have right now:

  Dim ds As New Data.DataSet
  Dim eventTable As New Data.DataTable
  ds = sqlCalendrier.Select(DataSourceSelectArguments.Empty)
  eventTable = ds.Tables(1)

I'm not convinced you can do what you want to do. Split it into two separate stored procedures: http://stackoverflow.com/questions/58940/access-to-result-sets-from-within-stored-procedures-transact-sql-sql-server — Smudge202, Apr 19 '11 at 14:02
That's what I feel also. I'll keep my hopes up that someone has a solution/idea for this, but I'll start planning out a 2nd option with the guy that manages the database/procedures. Thanks for your comment! — Vincent, Apr 19 '11 at 14:29

score 1 · Answer 1 · edited Jun 09 '11 at 08:42

Get in through the backdoor:

Get the dataview returned by the Select statement
Get the table associated with this dataview
Get the dataset associated with the table

Code example:

Dim dv As DataView = CType(sqlCalendrier.Select(DataSourceSelectArguments.Empty), DataView)
Dim ds As New DataSet() = dv.Table.DataSet
Dim dtDates As DataTable = ds.Tables(0)
Dim dtEvents As DataTable = ds.Tables(1)

I hope it works for you.

score 0 · Answer 2 · answered Apr 19 '11 at 13:46

0

By using the tables collection of the data set instance

    Dim ds As New DataSet()
    ds = <your data source>
    Dim dtDates As New DataTable()
    Dim dtEvents As New DataTable()

    dtDates = ds.Tables(0)
    dtEvents = ds.Tables(1)

answered Apr 19 '11 at 13:46

secretAgentB

1,279
4
19
36

That's what I have and it gives me a conversion error on the ds = line. I'll add the code I have in my question – Vincent Apr 19 '11 at 13:50

score 0 · Answer 3 · answered Apr 19 '11 at 16:13

Taken from this post:

 With cmd
   .CommandText = "ProdsAndCusts"
   .CommandType = CommandType.StoredProcedure

 End With


 cn.Open()

 myReader = cmd.ExecuteReader()

 While myReader.Read()
   ...

 End While


 myReader.NextResult()

 While myReader.Read()
   ...

 End While

Accessing 2nd table in dataview

3 Answers3