Why is my SqlCeResultSet only returning one row?

I am working on a Mobile 6 Classic phone application for the first time and am having problems with the SqlCeResultSet. I am 开发者_运维问答trying to fill a datagrid with this:

    Private Sub LookUpRoutes()
    Dim dir As String = Path.GetDirectoryName(Reflection.Assembly _
        .GetExecutingAssembly().GetName().CodeBase)

    Dim Sql As String = "SELECT RouteID, Name, Description FROM Routes " & _
        "Where IsDeleted = 0"

    Using con As SqlCeConnection = New SqlCeConnection( _
        String.Format("Data Source = '{0}\database\RouteTracker.sdf'", dir))

        con.Open()

        Using cmd As SqlCeCommand = New SqlCeCommand(Sql, con)
            cmd.CommandType = CommandType.Text

            Dim resultSet As SqlCeResultSet = _
                cmd.ExecuteResultSet(ResultSetOptions.Scrollable)
            dgRoutes.DataSource = resultSet
        End Using
    End Using
End Sub

However I am only getting one filled in row back from that. The remaining rows show x's in the fields instead of the data (image below).

What am I doing wrong?

My best guess is that dgRoutes is actively using the resultSet which is actively using the open connection. Presumably, to make everything operate faster, it only queries results as they are scrolled into view (ResultSetOptions.Scrollable) - therefore the connection needs to remain open so that it can pull additional data on demand.

I am not sure why this works but I got rid of the Using con As SqlCeConnection = New SqlCeConnection and just used con.open and then resisted the urge to add a con.close to the code because doing so makes it not work again.

Can someone fill me in to why I can't close and dispose the connection without breaking the functionality? Is there another way?