Why is my SqlCeResultSet only returning one row?

Question

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?

Answer 1

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.