I am getting information from a database and reading it into an xml file - I've tested the SQL and it works just fine on the database I am using - but it won't return anything once I've got it and am trying to use it. Here is my code:

Try
            Dim fieldCommand As New SqlCommand(getFieldNumbers, myConn)

            If (myConn.State = ConnectionState.Open) Then
                myConn.Close()
            End If

            myConn.Open()
            xmlr = fieldCommand.ExecuteXmlReader()
            xmlr.Read()
            Do While xmlr.ReadState <> Xml.ReadState.EndOfFile
                If GetRandomNumber = xmlr.ReadElementContentAsInt Then
                    GetRandomNumber = randomNumber(0, 10000)
                End If
                xmlr.MoveToNextAttribute()
            Loop
        Catch ex As Exception
            MessageBox.Show(ex.ToString())
        Finally
            If (myConn.State = ConnectionState.Open) Then
                myConn.Close()
            End If
        End Try

Its a simple SELECT statement that formats the results in XML, I can't remember exactly what it is but I tested it on the database without the program - and it works just fine - when I get back to work on Monday I will post the SQL.

Dim getFieldNumbers As String = "SELECT fieldInfoID FROM fieldInformation FOR XML AUTO, XMLDATA"

I think this line: xmlr.Read()

should be immediately after:

Do While xmlr.ReadState <> Xml.ReadState.EndOfFile

This should make it work and read the rows one at a time.

This article has been dead for over six months. Start a new discussion instead.