> only one can be open on a connection at once.
>
> i swear; i've seen documentation in several places that SQL 2005
> against VB 2005 supports MARS -- multiple active result sets.
>
> i guess i'm just mistaken.
>
> So.  How can i get the results of a datareader into an array??
>
> This example is from the 101 Samples, VB 2005, Data from
> microsoft.com/visualbasic or whatever the url is.
> version 2005.
>
>
>     ''' <summary>
>     ''' This method fills employee and address information using
> Multiple Active Result Sets
>     ''' This is set in the connection string by setting the
> MultipleActiveResultSets to true
>     ''' by default it is set to true if ommitted.
>     ''' By setting Multiple Active Results Sets, multiple data readers
> can be opened
>     ''' by using the same connection without any blocking from
> occurring.
>     ''' </summary>
>     Private Sub FillUserAddressesWithMARS()
>
>         Dim myEmployeeID As Integer = 0
>         Dim myAddressReader As SqlDataReader
>         Dim myWatch As Stopwatch = New Stopwatch()
>         Dim myFinalString As String = ""
>         Dim myConnectionCount As Integer = 0
>
>         Try
>
>         '' explicitly set the connection string to support Multiple
> Active Result Sets.
>             Dim connectionString As String =
> ConfigurationManager.AppSettings("myMarsConnectionString")
>
>
>             '' Set the query strings
>             Dim myEmployeeQuery As String = "SELECT * FROM Employees
> ORDER BY LastName"
>             Dim myAddressQuery As String = "SELECT * FROM Addresses
> WHERE EmployeeID = @EmployeeID"
>
>             '' Use the new StopWatch class to time the retrieval and
> display of data
>             myWatch.Start()
>
>             '' Encapsulate the retrieval with this one connection
>             Using myConnection As SqlConnection = New
> SqlConnection(connectionString)
>
>                 '' Open the connection and incrase the count
>                 myConnection.Open()
>                 myConnectionCount = myConnectionCount + 1
>
>                 '' We create both SqlCommand objects using the same
> connection
>                 Dim myEmployeeCommand As SqlCommand = New
> SqlCommand(myEmployeeQuery, myConnection)
>                 Dim myAddressCommand As SqlCommand = New
> SqlCommand(myAddressQuery, myConnection)
>
>                 '' Add the parameter to the address command, this will
> allow us
>                 '' to get all the addresses for each employee id
>                 myAddressCommand.Parameters.AddWithValue("@EmployeeID",
> SqlDbType.Int)
>
>                 '' We are going to go through all the Employee records
>                 Using myEmployeeReader As SqlDataReader =
> myEmployeeCommand.ExecuteReader()
>
>                     While myEmployeeReader.Read()
>
>                         '' We want to display the name of the employee
> in the final text box
>                         Dim myName As String =
> myEmployeeReader("FirstName").ToString() & " " &
> myEmployeeReader("LastName").ToString()
>                         myFinalString = myFinalString & myName & vbCrLf
>
>                         '' Retrieve the EmployeeID for getting all the
> addresses
>                         myEmployeeID =
> Convert.ToInt32(myEmployeeReader("EmployeeID"))
>
>
> myAddressCommand.Parameters("@EmployeeID").Value = myEmployeeID
>
>
>                         '' get the address information
>                         myAddressReader =
> myAddressCommand.ExecuteReader()
>                         Using myAddressReader
>                             If myAddressReader.HasRows Then
>
>                                 '' retrieve all the addresses in the
> DataReader object
>                                 While myAddressReader.Read()
>
>                                     Dim myAddress As String =
> myAddressReader("Address").ToString()
>                                     Dim myCity As String =
> myAddressReader("City").ToString()
>                                     Dim myState As String =
> myAddressReader("State").ToString()
>                                     Dim myZipCode As String =
> myAddressReader("ZipCode").ToString()
>                                     Dim myAddressType As String =
> myAddressReader("AddressType").ToString()
>
>                                     myFinalString = myFinalString &
> myAddress + vbCrLf _
>                                      & myCity & ", " & myState & " " &
> myZipCode & vbCrLf _
>                                      & "Address Type: " & myAddressType
> & vbCrLf & vbCrLf
>                                 End While
>
>                             Else
>                                 myFinalString = myFinalString & "No
> Address " & vbCrLf & vbCrLf
>                             End If
>                         End Using
>                     End While
>                 End Using
>                 '' Close the connection
>                 myConnection.Close()
>             End Using
>             '' Stop the StopWatch so that we can display the time
>             myWatch.Stop()
>             '' Display the time elapsed
>             elapsedTimeLabel.Text =
> myWatch.ElapsedMilliseconds.ToString() & " ms"
>             '' Display how many connections were created
>             connectionNumberLabel.Text = myConnectionCount.ToString()
>             '' set some properties for the RichTextBox
>             displayedDataRichTextBox.ScrollBars =
> RichTextBoxScrollBars.Vertical
>             displayedDataRichTextBox.WordWrap = True
>             '' Assign the final data
>             displayedDataRichTextBox.Text = myFinalString
>
>         Catch ex As Exception
>             MessageBox.Show("There was an error retrieving data using
> MARS", "Alert")
>         End Try
>
>     End Sub
>

> i mean doesn't my example say this should work? i just dont understand
>
> i guess that the bottom line is that i figured out the root of my
> performance problem; a simple little cartesian.
>
> THANK GOD.
>

> I kinda feel like I'm stuck in the (bad) old days of DAO where you had
> to close all these objects for stability sake.
>
> If I Dim a SqlDataReader why should i need to close it when it's gone
> out of scope?
>
> -Aaron
>

> well i'm on SQL 2005 Standard on Windows 2000 professional.
> I do have a 4gb limit on db size; so maybe SQL thinks that i'm in
> personal edition or something.
>
> I'd love to get around the 4gb limit on my laptop; it drives me crazy!!
>
> Is there anywhere that says which OS support MARS?
>
> -Aaron
>