making datareaders

**Chris Dunaway** · May 2 '06, 01:48 PM

Re: making datareaders

dbahooker@hotma il.com wrote:
[color=blue]
> Dim DR As New System.Data.Sql Client.SqlDataR eader
> DR = Nothing[/color]

First thing, you declare a new instance of a DataReader but then in
the next line you throw it away! The New keyword is not needed here
and you can delete the DR = Nothing line.
[color=blue]
> SetDataReader(D R, "EXEC spRootFoldersTo Catalog")
>
> While DR.Read[/color]

Assuming your stored procedure is executing, then you need to check if
DR is Nothing before you try to use it. I believe that is the problem.
Your stored procedure is not returning anything. You should use SQL
Profiler to help determine why you are not getting any data back.

**aaron.kempf@gmail.com** · May 2 '06, 03:56 PM

Re: making datareaders

i am getting data back

i will delete the new keyword; it was choking during compilation saying
i needed to do something with it before i get values from it or
something along those lines..

can i do

Dim DR as System.Data.Sql Client.SqlDataR eader = GetDataReader(" EXEC
spRootFoldersTo Catalog")

??

**Chris Dunaway** · May 2 '06, 05:56 PM

Re: making datareaders

You'd have to write your GetDataReader function to return a DataReader,
but yes, that is probably the preferred method.

**aaron.kempf@gmail.com** · May 2 '06, 10:39 PM

Re: making datareaders

ok i am looking through the factory methods; i've been looking into
that for a while now

but i just swear; it is something much much simpler

I rewrote it so that i dont have to deal with the 'getDataReader'
procedure
but still i get the same error:
----------------------------------

There is already an open DataReader associated with this command which
must be closed first.
----------------------------------

This is my first ADO.net conversion and i'm just kinda banging my head
against a wall.
[color=blue]
>From what i understand; this is like a 2003 bug and it's fixed by[/color]
MARS-- multiple active result sets.

Right??

I know that the connection works; i've tested it with commands and it
works like a charm.

Dim cmd As System.Data.Sql Client.SqlComma nd = cnnX.CreateComm and()
cmd.CommandText = "EXEC spRootFoldersTo Catalog"
Dim DR As System.Data.Sql Client.SqlDataR eader = cmd.ExecuteRead er

While DR.Read
RootFolder = DR.GetValue(1). ToString
rootFolderID = CInt(DR.GetValu e(0).ToString)
Call RootFolder_Cata log(RootFolder, rootFolderID)
End While

**Chris Dunaway** · May 3 '06, 01:26 PM

Re: making datareaders

A DataReader requires a constant connection to the database and only
one DataReader can be open on a connection at once. If the Sql query
returns more than one result set, you can advance to the next one by
calling the DataReader.Next Result method.

**aaron.kempf@gmail.com** · May 3 '06, 04:26 PM

Re: making datareaders

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
MultipleActiveR esultSets 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 FillUserAddress esWithMARS()

Dim myEmployeeID As Integer = 0
Dim myAddressReader As SqlDataReader
Dim myWatch As Stopwatch = New Stopwatch()
Dim myFinalString As String = ""
Dim myConnectionCou nt As Integer = 0

Try

'' explicitly set the connection string to support Multiple
Active Result Sets.
Dim connectionStrin g As String =
ConfigurationMa nager.AppSettin gs("myMarsConne ctionString")

'' 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(c onnectionString )

'' Open the connection and incrase the count
myConnection.Op en()
myConnectionCou nt = myConnectionCou nt + 1

'' We create both SqlCommand objects using the same
connection
Dim myEmployeeComma nd As SqlCommand = New
SqlCommand(myEm ployeeQuery, myConnection)
Dim myAddressComman d As SqlCommand = New
SqlCommand(myAd dressQuery, myConnection)

'' Add the parameter to the address command, this will
allow us
'' to get all the addresses for each employee id
myAddressComman d.Parameters.Ad dWithValue("@Em ployeeID",
SqlDbType.Int)

'' We are going to go through all the Employee records
Using myEmployeeReade r As SqlDataReader =
myEmployeeComma nd.ExecuteReade r()

While myEmployeeReade r.Read()

'' We want to display the name of the employee
in the final text box
Dim myName As String =
myEmployeeReade r("FirstName"). ToString() & " " &
myEmployeeReade r("LastName").T oString()
myFinalString = myFinalString & myName & vbCrLf

'' Retrieve the EmployeeID for getting all the
addresses
myEmployeeID =
Convert.ToInt32 (myEmployeeRead er("EmployeeID" ))

myAddressComman d.Parameters("@ EmployeeID").Va lue = myEmployeeID

'' get the address information
myAddressReader =
myAddressComman d.ExecuteReader ()
Using myAddressReader
If myAddressReader .HasRows Then

'' retrieve all the addresses in the
DataReader object
While myAddressReader .Read()

Dim myAddress As String =
myAddressReader ("Address").ToS tring()
Dim myCity As String =
myAddressReader ("City").ToStri ng()
Dim myState As String =
myAddressReader ("State").ToStr ing()
Dim myZipCode As String =
myAddressReader ("ZipCode").ToS tring()
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.Cl ose()
End Using
'' Stop the StopWatch so that we can display the time
myWatch.Stop()
'' Display the time elapsed
elapsedTimeLabe l.Text =
myWatch.Elapsed Milliseconds.To String() & " ms"
'' Display how many connections were created
connectionNumbe rLabel.Text = myConnectionCou nt.ToString()
'' set some properties for the RichTextBox
displayedDataRi chTextBox.Scrol lBars =
RichTextBoxScro llBars.Vertical
displayedDataRi chTextBox.WordW rap = True
'' Assign the final data
displayedDataRi chTextBox.Text = myFinalString

Catch ex As Exception
MessageBox.Show ("There was an error retrieving data using
MARS", "Alert")
End Try

End Sub

**Chris Dunaway** · May 3 '06, 06:15 PM

Re: making datareaders

Here's an example of what we do.

We execute the sql query to get a datareader back then pass that
datareader into a method to fill and object (or it could be an array)
and return that object:

Private Function FillUserObect(r dr As SqlDataReader) As User
Dim u As New User()

u.Id = rdr.GetInt32("I d")
u.Name = rdr.GetString(" Name")

'More lines for the rest of the fields here

Return u
End Function

You can also use the data reader's GetValues method to fill an object
array with the values.

**aaron.kempf@gmail.com** · May 3 '06, 08:46 PM

Re: making datareaders

but the getValues only handles one record at a time right?

i'm so in over my head lol.

kneedeep in dotnet and loving it; im a VB6 / VBA convert.

-Aaron

**aaron.kempf@gmail.com** · May 4 '06, 06:43 PM

Re: making datareaders

and i still dont understand why MARS isn't working.

and what's an object array?

i just want a string array?

**Göran Andersson** · May 4 '06, 07:56 PM

Re: making datareaders

I don't see any code for closing any datareader. You have to close the
datareader when you are done with it, or the connection won't be usable
again.

aaron.kempf@gma il.com wrote:[color=blue]
> 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
> MultipleActiveR esultSets 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 FillUserAddress esWithMARS()
>
> Dim myEmployeeID As Integer = 0
> Dim myAddressReader As SqlDataReader
> Dim myWatch As Stopwatch = New Stopwatch()
> Dim myFinalString As String = ""
> Dim myConnectionCou nt As Integer = 0
>
> Try
>
> '' explicitly set the connection string to support Multiple
> Active Result Sets.
> Dim connectionStrin g As String =
> ConfigurationMa nager.AppSettin gs("myMarsConne ctionString")
>
>
> '' 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(c onnectionString )
>
> '' Open the connection and incrase the count
> myConnection.Op en()
> myConnectionCou nt = myConnectionCou nt + 1
>
> '' We create both SqlCommand objects using the same
> connection
> Dim myEmployeeComma nd As SqlCommand = New
> SqlCommand(myEm ployeeQuery, myConnection)
> Dim myAddressComman d As SqlCommand = New
> SqlCommand(myAd dressQuery, myConnection)
>
> '' Add the parameter to the address command, this will
> allow us
> '' to get all the addresses for each employee id
> myAddressComman d.Parameters.Ad dWithValue("@Em ployeeID",
> SqlDbType.Int)
>
> '' We are going to go through all the Employee records
> Using myEmployeeReade r As SqlDataReader =
> myEmployeeComma nd.ExecuteReade r()
>
> While myEmployeeReade r.Read()
>
> '' We want to display the name of the employee
> in the final text box
> Dim myName As String =
> myEmployeeReade r("FirstName"). ToString() & " " &
> myEmployeeReade r("LastName").T oString()
> myFinalString = myFinalString & myName & vbCrLf
>
> '' Retrieve the EmployeeID for getting all the
> addresses
> myEmployeeID =
> Convert.ToInt32 (myEmployeeRead er("EmployeeID" ))
>
>
> myAddressComman d.Parameters("@ EmployeeID").Va lue = myEmployeeID
>
>
> '' get the address information
> myAddressReader =
> myAddressComman d.ExecuteReader ()
> Using myAddressReader
> If myAddressReader .HasRows Then
>
> '' retrieve all the addresses in the
> DataReader object
> While myAddressReader .Read()
>
> Dim myAddress As String =
> myAddressReader ("Address").ToS tring()
> Dim myCity As String =
> myAddressReader ("City").ToStri ng()
> Dim myState As String =
> myAddressReader ("State").ToStr ing()
> Dim myZipCode As String =
> myAddressReader ("ZipCode").ToS tring()
> 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.Cl ose()
> End Using
> '' Stop the StopWatch so that we can display the time
> myWatch.Stop()
> '' Display the time elapsed
> elapsedTimeLabe l.Text =
> myWatch.Elapsed Milliseconds.To String() & " ms"
> '' Display how many connections were created
> connectionNumbe rLabel.Text = myConnectionCou nt.ToString()
> '' set some properties for the RichTextBox
> displayedDataRi chTextBox.Scrol lBars =
> RichTextBoxScro llBars.Vertical
> displayedDataRi chTextBox.WordW rap = True
> '' Assign the final data
> displayedDataRi chTextBox.Text = myFinalString
>
> Catch ex As Exception
> MessageBox.Show ("There was an error retrieving data using
> MARS", "Alert")
> End Try
>
> End Sub
>[/color]

**aaron.kempf@gmail.com** · May 4 '06, 08:55 PM

Re: making datareaders

UNLESS IM USING MARS.

RIGHT?

**aaron.kempf@gmail.com** · May 4 '06, 10:05 PM

Re: making datareaders

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.

**Göran Andersson** · May 4 '06, 10:55 PM

Re: making datareaders

Yes, if the database supports MARS. Judging by the error message it doesn't.

You should close the datareaders anyway.

aaron.kempf@gma il.com wrote:[color=blue]
> 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.
>[/color]

**aaron.kempf@gmail.com** · May 5 '06, 03:16 PM

Re: making datareaders

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

making datareaders

making datareaders

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment