load multi sql statement results into a dataset?

[yarbrough40]

I've only dealt with firing a single SQL statement at a SQLServer table and returning a single result... easy....

however....
is there a way to fire a dual sql command and load the results into respective tables within a dataset?.. I'm thinking something along these lines (this code of course does not work)

Dim ds As New DataSet
Dim dt1 As DataTable = ds.Tables(0)
Dim dt2 As DataTable = ds.Tables(1)

Dim con As New OleDbConnection(MyConnectionString)
        Dim cmd As New OleDbCommand
        Dim dr As OleDbDataReader
        Dim sql As String

''''''''''''NOTICE HOW THERE ARE TWO SELECT STATEMENTS HERE
        sql = "Select * from Holidays; Select * from UserTable"

con.Open()
        cmd.CommandText = sql
        cmd.Connection = con
        dr = cmd.ExecuteReader
While dr.read
   dt1.Load(dr)
   dt2.Load(dr)
End While

[CroCrew]

Hello yarbrough40,

I don’t know of anyway to fire off two individual SQL select commands at the same time into one dataset that can be individually accessed through the dataset.

But, I can show you a way to fire off two individual SQL select commands into one dataset.

        Dim dbConnection As MySqlConnection = New MySqlConnection()
        dbConnection.ConnectionString = ConnectionString()

        Dim mylocalDataSet As DataSet = New DataSet

        Dim myDataAdapterOne As New MySqlDataAdapter("SELECT Author FROM Books ORDER BY KeyID", dbConnection)
        myDataAdapterOne.Fill(mylocalDataSet, "myData1")

        Dim myDataAdapterTwo As New MySqlDataAdapter("SELECT Title FROM Books ORDER BY KeyID", dbConnection)
        myDataAdapterTwo.Fill(mylocalDataSet, "myData2")

        If dbConnection.State = Data.ConnectionState.Closed Then
            dbConnection.Open()
        End If

        TextBox1.Text = mylocalDataSet.Tables("mydata1").Rows(0).Item("Author").ToString
        TextBox2.Text = mylocalDataSet.Tables("mydata2").Rows(0).Item("Title").ToString

        If dbConnection.State = Data.ConnectionState.Open Then
            dbConnection.Close()
            dbConnection.Dispose()
        End If

Hope this helps.

Happy Coding,
CroCrew~

[yarbrough40]

Ahh, I see. Easier than I thought. Thanks!

[CroCrew]

Anytime.

Happy Coding,
CroCrew~