Data Reader Loop Issue 2

[AlanJordan]

Hello,

I do not understand why the following code places me into a endless loop. I've read a number of posts on the subject, and have tried different approaches.

Here are the declarations:

Partial Class _Default
    Inherits System.Web.UI.Page
    Dim strResultsHolder As String
    Dim myRemoteConnection As New SqlConnection("Data Source=p3swhsql-v07.shr.phx3.secureserver.net; Initial Catalog=MFMAdmin; User ID=MFMAdmin; Password='MFMKey0143';")
    Dim myRemoteSQLQuery As String = "SELECT * FROM aspnet_users"
    Dim myLocalSQLQuery As String = "SELECT FirstName from IWTK"
    'myRemoteSQLQuery += "Customers WHERE Country = 'US'"
    Dim MyLocalConnection As SqlConnection = New SqlConnection("server=MERLIN2\SQLEXPRESS;Integrated Security=SSPI;database=m4m")
    Dim myRemoteCommand As _
        New SqlCommand(myRemoteSQLQuery, myRemoteConnection)
    Dim MyRemoteDataReader As SqlDataReader
    Dim MyLocalDataReader As SqlDataReader

And here is the subroutine:

    Protected Sub cmdLocalTesting_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdLocalTesting.Click
        Dim boolHasRows As Boolean
        Dim myLocalCommand As New SqlCommand(myLocalSQLQuery, MyLocalConnection)
        MyLocalConnection.Open()
        MyLocalDataReader = myLocalCommand.ExecuteReader()

        If MyLocalDataReader.HasRows = False Then
            boolHasRows = False
            MsgBox("There does not appear to be data in a local table.")
            Exit Sub
        Else
            boolHasRows = True
            MsgBox("There appears to be at least one row.")

Okay. This all makes sense to me up to here. The counter i is just diagnostic.

Dim i As Integer
            MsgBox("Says 'True' if the LocalDataReader.HasRows is true " & (MyLocalDataReader.HasRows))
            Do
                i = i + 1
                'Me.txtFirstName.Text = MyLocalDataReader(0).ToString

The line commented out above doesn't work. I thought I'd fix it later and threw in something inane for the time being, but got stuck in a loop.

                Me.txtFirstName.Text = "Thomas"
                'MsgBox(" In do While loop" & i & " " & (Me.txtFirstName.Text & " " & Me.txtLastName.Text))
                MyLocalDataReader.Read()
            Loop While MyLocalDataReader.Read
            MyLocalDataReader.Close()
        End If
    End Sub

I never exit the loop. It's my understanding that the DataReader is supposed to exit when there are no more rows. I don't know of any methods, like .movenext, that I'm supposed to be using.

Note: I originally started this with a Do While . . . Loop construction, and changed it when I read other posts.

Any suggestions would be most appreciated.

 

[ca8msm]

Have a look at the simple example from here.


------------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions

 

[AlanJordan]

Yes. That worked. Do you think my main problem was that I was not using the right syntax, as shown below:

MyDataReader.Item("ProductName"))

Or, do you think it was because I declared the connection in the Partial Class, as opposed to in the subroutine?

Thanks,
Alan

 

[ca8msm]

At a guess I'd say it was probably more with how the Do loop was constructed if it didn't exit out of it.


------------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions